Как заставить PostgreSQL использовать мой индекс?

CREATE TABLE product (
  product_id     SERIAL,
  factory_key    VARCHAR(60),
  relevant       BOOLEAN
)
Indexes:
"product_factory_key_key" btree (factory_key);
"product_factory_key_relevant_key" btree (factory_key, relevant) WHERE relevant = false;
"product_relevant_key" btree (relevant);

Факты:

  1. У нас около 100 миллионов записей в таблице product
  2. Есть небольшое количество заводов. Например, на 1 заводе может быть 5 миллионов изделий.
  3. Есть миллионы заводских ключей
  4. Только небольшое количество строк НЕ относится к каждой фабрике. Например, есть фабрика с 5 миллионами продуктов, имеет около 100 продуктов, которые НЕ актуальны.
  5. Однако существует миллион строк НЕ релевантных строк. Так как, самый распространенный случай - это один заводской ключ, 5 рядов продуктов и может быть 2 ряда НЕ релевантных.

Это проблемный запрос:

SELECT * FROM product WHERE factory_key='some_product_key' AND relevant=false LIMIT 10;

Объясните анализ:

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..23.06 rows=10 width=188) (actual time=2709.654..32252.961 rows=10 loops=1)
   ->  Seq Scan on product  (cost=0.00..7366785.34 rows=3194759 width=188) (actual time=2709.634..32252.904 rows=10 loops=1)
         Filter: ((NOT relevant) AND ((product_key)::text = 'some_product_key'::text))
         Rows Removed by Filter: 449486
 Total runtime: 32253.150 ms
(5 rows)

Проблема:

Это проблематично, потому что:

  1. Я полагаю, что планировщик решил использовать последовательное сканирование, потому что существует так много строк, которые соответствуют этой фабрике. (около 3,2 миллиона строк соответствуют этой фабрике или примерно 3%)

  2. Однако, потому что только ЧРЕЗВЫЧАЙНО небольшое количество строк НЕ имеет значения. И я ищу НЕ актуально. Последовательное сканирование оказывается чрезвычайно дорогим.

Я уже создал составной индекс product_factory_key_relevant_key, однако он не использует преимущества индекса.

ИЗМЕНИТЬ:

Я пытаюсь заставить postgres использовать составной ключ: product_factory_key_relevant_key

SET enable_seqscan=off

Хотя сейчас он использует сканирование индекса. На самом деле это все еще медленнее, чем seqscan. (поэтому я думаю, что планировщик был прав, выполняя последовательное сканирование)

                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..34.03 rows=10 width=188) (actual time=8.088..469974.692 rows=10 loops=1)
   ->  Index Scan using product_factory_key_relevant_key on product  (cost=0.57..10689307.49 rows=3194776 width=188) (actual time=8.083..469974.655 rows=10 loops=1)
         Index Cond: (relevant = false)
         Filter: ((NOT relevant) AND ((product_key)::text = 'some_product_key'::text))
         Rows Removed by Filter: 2205295
 Total runtime: 469974.820 ms
(6 rows)

postgresql indexing postgresql-performance
person samol    schedule 19.04.2014    source источник
comment
Ну попробуйте; вам действительно следует упорядочивать столбцы в порядке убывания селективности, поэтому первое, что вам нужно сделать, это создать индекс. Вы, вероятно, захотите CREATE INDEX CONCURRENTLY, если не можете позволить себе заблокировать запись на несколько часов.   -  person Craig Ringer    schedule 19.04.2014
comment
@CraigRinger, но в этом нет никакого смысла. Я создал специальный составной индекс, который точно соответствует условию. Почему планировщик до сих пор не использует мой index. Надеюсь, ты понимаешь мое разочарование, ха-ха.   -  person samol    schedule 19.04.2014
comment
Индексы — это не волшебство. В частности, доступ к индексу требует большого количества случайных операций ввода-вывода, поэтому он может быть намного медленнее, чем последовательное сканирование, если затраты на случайный ввод-вывод высоки, а индекс не исключает большую часть строк. Таким образом, PostgreSQL может подумать, что использование индекса дороже (то есть медленнее), чем seqscan. Если это не так, это, вероятно, потому, что ваши параметры затрат не отражают реальность, хотя планировщик, безусловно, может ошибиться в оценке затрат. Это также может быть связано с неточной статистикой - покажите explain analyze.   -  person Craig Ringer    schedule 19.04.2014
comment
Кстати, план, который вы показали, всего лишь explain. Вам нужно explain analyze, чтобы увидеть проблемы со статистикой. explain.depesz.com/s/jNot . См. также stackoverflow.com/tags/postgresql-performance/info.   -  person Craig Ringer    schedule 19.04.2014
comment
@CraigRinger, пожалуйста, посмотрите мое последнее редактирование для анализа объяснения как для исходного запроса, так и для запроса с отключенным seqscan   -  person samol    schedule 19.04.2014

Ответы (2)


arrow_upward
14
arrow_downward

Переопределение параметров стоимости

Вы никогда не сможете заставить PostgreSQL использовать определенный индекс или полностью запретить ему выполнять seqscan.

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

Для тестирования попробуйте:

SET enable_seqscan = off;

если Pg может использовать сканирование индекса (или что-то еще), он будет.

Вы также можете рассмотреть:

SET random_page_cost = 1.1

т. е. сообщить PostgreSQL, что произвольный ввод-вывод лишь немного дороже, чем последовательный ввод-вывод. Обычно это верно в системах с твердотельными накопителями или в тех случаях, когда большая часть БД кэшируется в ОЗУ. В этом случае будет более вероятно выбрать индекс.

Конечно, если случайный ввод-вывод в вашей системе на самом деле дороже, то использование индекса, вероятно, будет медленнее.

Селективность, частичные индексы

То, что вы действительно должны сделать, это следовать совету, который вам уже дали. Создайте индекс в порядке избирательности — если relevant встречается реже, используйте его. Вы даже можете пойти дальше и создать частичный индекс:

CREATE INDEX idx_name_blah ON tbl_name_blah (factory_key) WHERE (NOT relevant);

Этот индекс содержит только значения для relevant = 'f'. Его можно использовать только для запросов, для которых планировщик знает, что релевантность будет ложной. С другой стороны, это будет гораздо меньший по размеру и более быстрый индекс.

Статистика

У вас также может быть неточная статистика, из-за которой PostgreSQL считает, что частота значений для вашей таблицы отличается от действительной. explain analyze поможет показать это.

Вы также можете просто ANALYZE my_table, если статистика просто устарела; если это так, увеличьте частоту, с которой работает автоочистка, потому что она не поспевает.

Если статистика актуальна, но планировщик все еще делает неверные оценки на основе статистики, увеличение цели статистики для таблицы (см. руководство) и повторный анализ могут помочь, если на самом деле это проблема неправильной оценки статистики.

Версии

Старые версии PostgreSQL, как правило, менее умны в оценке затрат, оптимизации запросов, статистике, методах выполнения запросов и почти во всем остальном.

Если у вас не последняя версия, обновите.

Например, сканирование только индекса 9.2 позволит вам создать частичный индекс.

(product_id, factory_key) WHERE (NOT relevant)

а затем выполните запрос:

SELECT product_id, factory_key FROM my_table WHERE NOT relevant;

это должно только читать индекс, без доступа к куче вообще.

person Craig Ringer    schedule 19.04.2014
comment
В чем разница между приведенным вами индексом примера и индексом, который у меня есть: "product_factory_key_relevant_key" btree (factory_key, relevant) WHERE relevant = false? Являются ли они фактически одним и тем же? - person samol; 19.04.2014
comment
Хорошо, я пропустил это. Да, они почти одинаковы; хотя термин relevant в вашем индексе совершенно бессмысленен, он просто увеличивает индекс, и вы должны избавиться от него. Вам действительно нужно показать explain analyze вывод вашего запроса, как с помощью seqscan, так и с enable_seqscan=off, чтобы увидеть, что планировщик считает следующим самым дешевым планом. - person Craig Ringer; 19.04.2014
comment
Я понимаю. К сожалению, у меня нет тестовой копии базы данных, так как размер базы составляет около 300 ГБ. У меня есть подмножество данных (около 10 ГБ), но планировщик ведет себя совершенно иначе в меньшей базе данных, чем в большой. - person samol; 19.04.2014
comment
Да, вам придется либо сделать это вживую, либо создать полноразмерную копию. - person Craig Ringer; 19.04.2014
comment
Поскольку у меня уже есть этот индекс "product_factory_key_key" btree (factory_key);, ваш частичный индекс все еще будет полезен для моего варианта использования? - person samol; 19.04.2014
comment
Да, будет, потому что он включает только строки, для которых relevant равно false. Частичные индексы очень полезны, когда у вас есть одно очень распространенное значение и другие гораздо менее распространенные значения; вы можете создать индекс, который не включает очень распространенные значения (для которых использование индекса в любом случае бесполезно), поэтому он включает только те значения, для которых индекс достаточно избирательен, чтобы быть полезным. Однако мне нужно увидеть фактическую статистику таблицы и explain analayze запроса, чтобы с большей уверенностью сказать, будет ли это полезно в этом случае. - person Craig Ringer; 19.04.2014
comment
пожалуйста, посмотрите на мое последнее редактирование. Я думаю, вы найдете результаты очень интересными. Я отключил seqscan и заставил его использовать составной индекс, и в результате он работал значительно медленнее. Я еще не уверен, почему это так. Я попробую частичный индекс сегодня вечером - person samol; 19.04.2014
comment
Ааа. Какая у вас версия PostgreSQL? - person Craig Ringer; 19.04.2014
comment
Еще одно требование, которое у меня есть, заключается в следующем: мне нужно уметь: SELECT * FROM product WHERE factory_key IN ('some_product_key1','some_product_key2') AND NOT relevant LIMIT 10; Подойдет ли для этой цели частичный индекс? - person samol; 19.04.2014
comment
Думаю так; из памяти Pg внутренне преобразует IN в список AND qual. Я рекомендую проверить, чтобы увидеть. - person Craig Ringer; 19.04.2014
comment
Интересно то, что при выполнении WHERE IN составной ключ дешевле, чем частичный индекс. (согласно объяснению) - person samol; 19.04.2014
comment
Ок, заработало. Теперь это значительно быстрее с частичным индексом. Ты, мой друг, для меня герой. - person samol; 19.04.2014

arrow_upward
0
arrow_downward

Имхо, проблема в вашем запросе - это $1. Я предполагаю, что это означает, что вы используете подготовленный оператор, потому что вы где-то читали, что это лучшая практика.

На самом деле это плохо, потому что PG не может знать заранее, является ли кардинальность используемых вами критериев высокой или низкой. Таким образом, ему необходимо выбрать план, который подойдет для большинства случаев. Если некоторые значения разбросаны по всему месту таким образом, что самая левая часть вашего индекса btree бесполезна, у него будут очень веские причины для последовательного сканирования.

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

Если нет, то что сказал Крейг... В частности, частичный индекс.

person Denis de Bernardy    schedule 19.04.2014
comment
В более новых версиях Pg параметризует планы и повторно проверяет, должен ли он создавать специализированный план или использовать общий план. Так что в наши дни это меньше проблем. - person Craig Ringer; 19.04.2014
comment
Сладкий. Я не знал об этом. :-) - person Denis de Bernardy; 19.04.2014
comment
Том добавил его в 9.2, см. git commit e2fa76d80ba571d4de8992de6386536867250474. - person Craig Ringer; 19.04.2014