Оптимизировать диапазон запросов Postgres timestamp

Question

Оптимизировать диапазон запросов Postgres timestamp

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

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;

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

18

postgresql indexing query-optimization database-partitioning postgresql-performance

Источник

user1754724 21 дек '12 в 22:43

1 ответ

Другие вопросы по тегам postgresql indexing query-optimization database-partitioning postgresql-performance

user939860 23 дек '12 в 01:28 2012-12-23 01:28 · Answer 1 · 2012-12-23 01:28

`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,

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

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

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