Оптимизация диапазона запросов меток времени Postgres

У меня есть следующая таблица и определенные индексы:

CREATE TABLE ticket
(
  wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass),
  eid bigint,
  created timestamp with time zone NOT NULL DEFAULT now(),
  status integer NOT NULL DEFAULT 0,
  argsxml text,
  moduleid character varying(255),
  source_id bigint,
  file_type_id bigint,
  file_name character varying(255),
  status_reason character varying(255),
  ...
)

Я создал индекс для метки времени created следующим образом:

CREATE INDEX ticket_1_idx
  ON ticket
  USING btree
  (created );

и вот мой запрос

select * from ticket 
where created between '2012-12-19 00:00:00' and  '2012-12-20 00:00:00'

Это работало нормально, пока количество записей не начало расти (около 5 миллионов), и теперь для возврата требуется вечность.

Объясните анализ показывает это:

"Index Scan using ticket_1_idx on ticket  (cost=0.00..10202.64 rows=52543 width=1297) (actual time=0.109..125.704 rows=53340 loops=1)"
"  Index Cond: ((created >= '2012-12-19 00:00:00+00'::timestamp with time zone) AND (created <= '2012-12-20 00:00:00+00'::timestamp with time zone))"
"Total runtime: 175.853 ms"

До сих пор я пытался установить

random_page_cost = 1.75 
effective_cache_size = 3

Также создано

create CLUSTER ticket USING ticket_1_idx;

Ничего не работает. Что я делаю неправильно? Почему он выбирает последовательное сканирование? Предполагается, что индексы делают запрос быстрым. Что-то можно сделать для оптимизации?


postgresql query-optimization indexing database-partitioning postgresql-performance
person user1754724    schedule 21.12.2012    source источник
comment
Он не выполняет последовательное сканирование. Он выполняет сканирование индекса.   -  person Mike Sherrill 'Cat Recall'    schedule 22.12.2012
comment
Кроме того, для запуска требуется всего 175 мс. Если это займет вечность, вероятно, у OP есть огромный набор данных, который занимает много времени для передачи по сети, а не времени для выполнения запроса. \   -  person Scott Marlowe    schedule 22.12.2012
comment
Кстати: effective_cache_size=3 может быть слишком мало. (но, вероятно, не повредит в этом случае)   -  person wildplasser    schedule 22.12.2012
comment
Почему вы считаете 175 мс возрастом?   -  person Bohemian♦    schedule 23.12.2012
comment
Не делайте select *, так как это увеличит размер набора результатов, который будет передан клиенту.   -  person Clodoaldo Neto    schedule 27.12.2012

Ответы (1)


arrow_upward
27
arrow_downward

CLUSTER

Если вы собираетесь использовать CLUSTER, отображаемый синтаксис недействителен.

create CLUSTER ticket USING ticket_1_idx;

Запустить один раз:

CLUSTER ticket USING ticket_1_idx;

Это может очень помочь с большими наборами результатов. Не так много для одной возвращаемой строки.
Postgres запоминает, какой индекс использовать для последующих вызовов. Если ваша таблица не предназначена только для чтения, эффект со временем ухудшается, и вам необходимо повторно запускать ее через определенные промежутки времени:

CLUSTER ticket;

Возможно только на энергозависимых разделах. Увидеть ниже.

Однако если у вас много обновлений, CLUSTER (или VACUUM FULL) может плохо сказаться на производительности. Правильное количество раздувания позволяет UPDATE размещать новые версии строк на той же странице данных и позволяет избежать слишком частого физического расширения базового файла в ОС. Вы можете использовать тщательно настроенный FILLFACTOR, чтобы получить лучшее из обоих миров:

pg_repack

CLUSTER устанавливает монопольную блокировку таблицы, что может быть проблемой в многопользовательской среде. Цитирование руководства:

Когда таблица кластеризуется, на нее устанавливается блокировка ACCESS EXCLUSIVE. Это предотвращает выполнение любых других операций с базой данных (как чтения, так и записи) в таблице до завершения CLUSTER.

Жирный акцент мой. Рассмотрим альтернативный вариант pg_repack:

В отличие от CLUSTER и VACUUM FULL он работает онлайн, не удерживая эксклюзивную блокировку обрабатываемых таблиц во время обработки. pg_repack эффективен для загрузки, его производительность сравнима с использованием CLUSTER напрямую.

и:

pg_repack должен получить монопольную блокировку в конце реорганизации.

Версия 1.3.1 работает с:

PostgreSQL 8.3, 8.4, 9.0, 9.1, 9.2, 9.3, 9.4

Версия 1.4.2 работает с:

PostgreSQL 9.1, 9.2, 9.3, 9.4, 9.5, 9.6, 10

Запрос

Запрос достаточно прост, чтобы не вызывать проблем с производительностью как таковых.

Однако несколько слов о правильности: BETWEEN< /a> конструкция включает границы. Ваш запрос выбирает все записи от 19 декабря, плюс записи с 00:00 часов 20 декабря. Это крайне маловероятно требование. Скорее всего, вы действительно хотите:

SELECT *
FROM   ticket 
WHERE  created >= '2012-12-19 0:0'
AND    created <  '2012-12-20 0:0';

Представление

Во-первых, вы спросите:

Почему он выбирает последовательное сканирование?

Ваш вывод EXPLAIN четко показывает сканирование индекса, а не последовательное сканирование таблицы. Должно быть какое-то недоразумение.

Если вы сильно нуждаетесь в повышении производительности, вы можете улучшить ситуацию. Но о необходимой справочной информации не может быть и речи. Возможные варианты включают в себя:

  • Вы можете запрашивать только обязательные столбцы вместо *, чтобы снизить стоимость передачи (и, возможно, другие преимущества производительности).

  • Вы можете посмотреть на разделение и поместить практические отрезки времени в отдельные таблицы. Добавьте индексы к разделам по мере необходимости.

  • Если разбиение на разделы невозможно, другим связанным, но менее навязчивым методом будет добавление одного или нескольких частичные индексы.
    Например, если вы в основном запрашиваете текущий месяц, вы можете создать следующий частичный индекс:

    CREATE INDEX ticket_created_idx ON ticket(created)
WHERE created >= '2012-12-01 00:00:00'::timestamp;

    CREATE новый индекс прямо перед началом нового месяца. Вы можете легко автоматизировать задачу с помощью задания cron. Опционально DROP частичные индексы за старые месяцы позже.

  • Сохраните общий индекс дополнительно для CLUSTER (который не может работать с частичными индексами). Если старые записи никогда не меняются, секционирование таблицы очень поможет в решении этой задачи, так как вам нужно будет повторно кластеризовать только новые секции. Опять же, если записи вообще никогда не меняются, вам, вероятно, не нужен CLUSTER.

Если вы объедините последние два шага, производительность должна быть потрясающей.

Основы производительности

Возможно, вы упускаете одну из основ. Применяются все обычные рекомендации по производительности:

person Erwin Brandstetter    schedule 23.12.2012