PostgreSQL не использует частичный индекс

У меня есть таблица в PostgreSQL 9.2, в которой есть text столбец. Назовем это text_col . Значения в этом столбце довольно уникальны (могут содержать не более 5-6 дубликатов). Таблица имеет ~ 5 миллионов строк. Около половины этих строк содержат null значение для text_col . Когда я выполняю следующий запрос, я ожидаю 1-5 строк. В большинстве случаев (> 80%) я ожидаю только 1 строку.

запрос

 explain analyze SELECT col1,col2.. colN FROM table WHERE text_col = 'my_value'; 

В btree существует индекс btree . Этот индекс никогда не используется планировщиком запросов, и я не уверен, почему. Это результат запроса.

планировщик

 Seq Scan on two (cost=0.000..459573.080 rows=93 width=339) (actual time=1392.864..3196.283 rows=2 loops=1) Filter: (victor = 'foxtrot'::text) Rows Removed by Filter: 4077384 

Я добавил еще один частичный индекс, чтобы попытаться отфильтровать те значения, которые не были нулевыми, но это не помогло (с или без text_pattern_ops . Мне не нужны text_pattern_ops если в моих запросах не text_pattern_ops условия LIKE , но они также соответствуют равенству).

 CREATE INDEX name_idx ON table USING btree (text_col COLLATE pg_catalog."default" text_pattern_ops) WHERE text_col IS NOT NULL; 

Отключение сканирования последовательности с использованием set enable_seqscan = off; делает планировщик по-прежнему выбирает seqscan над index_scan . В итоге…

  1. Количество строк, возвращаемых этим запросом, невелико.
  2. Учитывая, что ненулевые строки являются довольно уникальными, сканирование индекса по тексту должно быть быстрее.
  3. Вакуумирование и анализ таблицы не помогли оптимизатору выбрать индекс.

Мои вопросы

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

[Изменить – Дополнительная информация]

  1. Сканирование индекса происходит в моей локальной базе данных, где хранится около 10% данных, доступных в производстве.

Частичный индекс – это хорошая идея, чтобы исключить половину строк таблицы, которые вам явно не нужны. Simpler:

 CREATE INDEX name_idx ON table (text_col) WHERE text_col IS NOT NULL; 

Обязательно запустите ANALYZE table после создания индекса. (Autovacuum делает это автоматически через некоторое время, если вы не делаете это вручную, но если вы проверите сразу после создания, ваш тест не удастся.)

Затем, чтобы убедить планировщика запросов, что конкретный частичный индекс можно использовать, повторите условие WHERE в запросе – даже если оно кажется полностью избыточным:

 SELECT col1,col2, .. colN FROM table WHERE text_col = 'my_value' AND text_col IS NOT NULL ; -- repeat condition 

Вуаля.

По документации :

Однако имейте в виду, что предикат должен соответствовать условиям, используемым в запросах, которые должны получать выгоду от индекса. Если быть точным, частичный индекс может использоваться в запросе только в том случае, если система может распознать, что условие WHERE запроса математически подразумевает предикат индекса. PostgreSQL не имеет сложного метода проверки теоремы, который может распознавать математически эквивалентные выражения, написанные в разных формах. (Мало того, что такой общий теоретический прорыв чрезвычайно сложно создать, он, вероятно, будет слишком медленным, чтобы его можно было использовать.) Система может распознавать простые последствия неравенства, например «x <1» подразумевает «x <2»; в противном случае условие предиката должно точно соответствовать части условия запроса WHERE или индекс не будет признан пригодным для использования. Согласование происходит во время планирования запроса, а не во время выполнения. В результате параметризованные предложения запроса не работают с частичным индексом.

Что касается параметризованных запросов: снова добавьте (избыточный) предикат частичного индекса в качестве дополнительного, постоянного условия WHERE , и он работает нормально.


Важное обновление в Postgres 9.6 в значительной степени повышает вероятность сканирования только по индексу (что может сделать запросы дешевле, и планировщик запросов будет более легко выбирать такие планы запросов). Связанный:

  • PostgreSQL не использует индекс во время подсчета (*)

Частичный индекс используется только в том случае, если условия WHERE совпадают. Таким образом, индекс с WHERE text_col IS NOT NULL может использоваться только в том случае, если вы используете то же условие в SELECT . Несовпадение сортировки также может нанести вред.

Попробуйте следующее:

  1. Сделать простейший возможный индекс btree CREATE INDEX foo ON table (text_col)
  2. ANALYZE table
  3. запрос

Я понял это . При внимательном рассмотрении взгляда pg_stats который analyze помогает построить, я наткнулся на эту выдержку в документации .

корреляция

Статистическая корреляция между упорядочиванием физических строк и логическим порядком значений столбца. Это от -1 до +1. Когда значение около -1 или +1, сканирование индекса в столбце будет оценено как более дешевое, чем когда оно приближается к нулю, из-за уменьшения произвольного доступа к диску. (Этот столбец имеет значение null, если тип данных столбца не имеет оператора <.

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

 select * from pg_stats where tablename = 'table_name' and attname = 'text_col'; 

В этой таблице также есть несколько обновлений, выполненных по его строкам. Значение avg_width строк оценивается в 20 байтов. Если обновление имеет большое значение для текстового столбца, оно может превышать среднее значение и также приводит к более медленному обновлению. Мое предположение заключалось в том, что физическое и логическое упорядочение замедляются, двигаясь вместе с каждым обновлением. Чтобы исправить это, я выполнил следующие запросы.

 ALTER TABLE table_name SET (FILLFACTOR = 80); VACUUM FULL table_name; REINDEX TABLE table_name; ANALYZE table_name; 

Идея состоит в том, что я могу дать каждому блоку диска 20-процентный буфер и vacuum full таблицу, чтобы вернуть потерянное пространство и поддерживать физический и логический порядок. После этого запрос забирает индекс.

запрос

 explain analyze SELECT col1,col2... colN FROM table_name WHERE text_col is not null AND text_col = 'my_value'; 

Сканирование частичного индекса – 1,5 мс

 Index Scan using tango on two (cost=0.000..165.290 rows=40 width=339) (actual time=0.083..0.086 rows=1 loops=1) Index Cond: ((victor five NOT NULL) AND (victor = 'delta'::text)) 

Исключая условие NULL, подбирает другой индекс с помощью сканирования кучи битмапа.

Полный индекс – 0.08мс

 Bitmap Heap Scan on two (cost=5.380..392.150 rows=98 width=339) (actual time=0.038..0.039 rows=1 loops=1) Recheck Cond: (victor = 'delta'::text) -> Bitmap Index Scan on tango (cost=0.000..5.360 rows=98 width=0) (actual time=0.029..0.029 rows=1 loops=1) Index Cond: (victor = 'delta'::text) 

[РЕДАКТИРОВАТЬ]

Хотя изначально это выглядело так, как correlation играет важную роль в выборе индексационного сканирования. @Mike заметил, что значение correlation , близкое к 0 в его базе данных, все же привело к сканированию индекса. Помогло мне изменение коэффициента заполнения и вакуумирования, но я не уверен, почему.