Оптимизировать диапазон запросов времени печати 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; 

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

CLUSTER

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

create CLUSTER ticket USING ticket_1_idx;

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

 CLUSTER ticket USING ticket_1_idx; 

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

 CLUSTER ticket; 

Возможно, только на неустойчивых разделах. Смотри ниже.

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

  • Коэффициент заполнения для последовательного индекса, который является PK

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 включает в себя границы. Ваш запрос выбирает все 19 декабря, плюс записи с 20 декабря, 00:00 часов. Это крайне маловероятное требование. Скорее всего, вы действительно хотите:

 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 .

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

Основы работы

Возможно, вам не хватает одной из основ. Все обычные рекомендации по работе:

Давайте будем гением компьютера.