Почему производительность запросов PostgresQL снижается с течением времени, но восстанавливается при восстановлении индекса

В соответствии с этой страница в руководстве indexes don't need to be maintained. Однако мы работаем с таблицей PostgresQL, которая имеет непрерывную скорость updates, deletes и inserts, которая со временем (несколько дней) видит существенную деградацию запросов. Если мы удалим и воссоздаем индекс, производительность запроса будет восстановлена.

Мы используем настройки из окна.
Таблица в нашем тесте в настоящее время начинает пустую и растет до полумиллиона строк. Он имеет довольно большую строку (много текстовых полей).

Мы searching based of an index, not the primary key (я подтвердил, что индекс используется, по крайней мере, в нормальных условиях)

Таблица используется как постоянное хранилище для одного процесса. Использование PostgresQL в Windows с клиентом Java.

Я готов отказаться от insert and update performance, чтобы поддерживать производительность запросов.

Мы рассматриваем обратную архитектуру приложения, чтобы данные распространялись по различным динамическим таблицам таким образом, чтобы мы могли периодически отбрасывать и перестраивать индексы, не влияя на приложение. Однако, как всегда, есть время, чтобы заставить это работать, и я подозреваю, что у нас отсутствует что-то основное в нашей конфигурации или использовании.

Мы рассмотрели forcing vacuuming и rebuild to run at certain times, но я подозреваю locking period for such an action would cause our query to block. Это может быть вариант, но есть некоторые последствия в реальном времени (окна 3-5 секунд), которые требуют других изменений в нашем коде.

Дополнительная информация: Таблица и индекс

CREATE TABLE icl_contacts
(
  id bigint NOT NULL,
  campaignfqname character varying(255) NOT NULL,
  currentstate character(16) NOT NULL,
  xmlscheduledtime character(23) NOT NULL,
...
25 or so other fields.  Most of them fixed or varying character fiel  
...
  CONSTRAINT icl_contacts_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE icl_contacts OWNER TO postgres;

CREATE INDEX icl_contacts_idx
  ON icl_contacts
  USING btree
  (xmlscheduledtime, currentstate, campaignfqname);

Анализ:

Limit  (cost=0.00..3792.10 rows=750 width=32) (actual time=48.922..59.601 rows=750 loops=1)
  ->  Index Scan using icl_contacts_idx on icl_contacts  (cost=0.00..934580.47 rows=184841 width=32) (actual time=48.909..55.961 rows=750 loops=1)
        Index Cond: ((xmlscheduledtime < '2010-05-20T13:00:00.000'::bpchar) AND (currentstate = 'SCHEDULED'::bpchar) AND ((campaignfqname)::text = '.main.ee45692a-6113-43cb-9257-7b6bf65f0c3e'::text))

И, да, я знаю, что есть множество вещей we could do to normalize and improve the design of this table. Некоторые из этих вариантов могут быть доступны нам.

Мое внимание в этом вопросе о понимании how PostgresQL is managing the index and query over time (understand why, not just fix). Если бы это было сделано или значительно реорганизовано, было бы много изменений.

Ответы

Ответ 1

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

Примечания: VACUUM FULL: это приведет к восстановлению статистики таблиц и восстановлению нагрузки дискового пространства. Он блокирует всю таблицу.

VACUUM: это приведет к восстановлению статистики таблиц и восстановлению некоторого дискового пространства. Он может запускаться параллельно с производственной системой, но генерирует много IO, которые могут влиять на производительность.

ANALYZE: это приведет к восстановлению статистики планировщика запросов. Это вызвано VACUUM, но может запускаться самостоятельно.

Подробнее подробные заметки, найденные здесь

Ответ 2

Является ли значение 2010-05-20T13: 00: 00.000 ', которое xmlscheduledtime сравнивается с частью SQL или поставляется как параметр?

При планировании запуска запроса, говоря, что поле должно быть меньше заданного параметра с еще неизвестным значением, не дает PostgreSQL многого для продолжения. Он не знает, будет ли это соответствовать почти всем строкам, или почти любой из строк.

Чтение о о том, как планировщик использует статистику, очень помогает при попытке выяснить, почему ваша база данных использует планы.

Вы можете улучшить производительность, изменив порядок полей в этом сложном индексе или создав новый индекс, с упорядоченными полях (campaignfqname, currentstate, xmlscheduledtime), после чего индекс приведет вас прямо к названию fq кампании и текущее состояние, которое вас интересует, и сканирование индекса по диапазону xmlscheduledtime будет состоять из следующих строк.

Ответ 3

Что касается производительности, использование строк для хранения информации о времени и состоянии является довольно узким местом. Прежде всего, индексы по текстам крайне неэффективны, для сравнения в два раза в тот же день требуется не менее 11 сопоставлений (в используемом вами формате), однако с использованием типа времени его можно свести к простому сравнению. Это также влияет на размер индекса, и большой индекс трудно найти, и db не сохранит его в памяти. Эти же соображения применяются к столбцу состояния. Если он представляет собой небольшой набор состояний, вы должны использовать целочисленные числа, сопоставленные с состояниями, это уменьшит узлы индекса - и размер индекса соответственно. Кроме того, этот индекс будет бесполезен даже при использовании этих встроенных типов, если вы не укажете фактическое время в своем запросе.

Ответ 4

Это пахнет, как индекс. Я приведу вас на эту страницу

http://www.postgresql.org/docs/8.3/static/routine-reindex.html

который говорит внизу:

Кроме того, для индексов B-дерева a недавно построенный индекс несколько быстрее доступа, чем тот, который имеет неоднократно обновлялось, поскольку логически смежные страницы обычно также физически смежные в новом встроенный индекс. (Это соображение в настоящее время не применяются к не-B-дереву индексы.) Возможно, было бы полезно периодически пересматривать, чтобы улучшить скорость доступа.

Что, похоже, противоречит странице, на которую вы ссылаетесь, говоря, что индексы "не требуют обслуживания или настройки".

Вы пытались "создать индекс одновременно"?

Ответ 5

Это случай учебника. Вы должны настроить autovacuum, чтобы быть намного более агрессивным.