Почему PostgreSQL не использует индекс?

Question

[altair86]

Берём запрос:

SELECT *
   FROM t
     LEFT JOIN tu ON t.tid= tu.tid
WHERE t.OrderStatusID IN (1,5)

Получаем вот такой результат:

"Hash Right Join  (cost=4549.10..9309.76 rows=2262 width=460) (actual time=44.610..44.729 rows=165 loops=1)"
"  Hash Cond: (tu.tid = t.tid)"
"  ->  Seq Scan on tu  (cost=0.00..4359.65 rows=152765 width=47) (actual time=0.008..21.188 rows=152887 loops=1)"
"  ->  Hash  (cost=4520.83..4520.83 rows=2262 width=413) (actual time=1.232..1.232 rows=165 loops=1)"
"        Buckets: 4096  Batches: 1  Memory Usage: 65kB"
"        ->  Index Scan using t_orderstatusid on t  (cost=0.56..4520.83 rows=2262 width=413) (actual time=0.035..1.142 rows=165 loops=1)"
"              Index Cond: (orderstatusid = ANY ('{1,5}'::integer[]))"
"Planning time: 0.722 ms"
"Execution time: 44.834 ms"

Видим, что таблица tu проходит Seq Scan по кол-ву строк 152 887. При чём индекс имеется на поле tid.
Берём фильтр t.OrderStatusID IN (1,5) и забираем оттуда ID, которые мы ищем. Те же самые искомые 165 записей:

И получаем, что на этот раз на tu индекс работает! При чём за 1 мс в отличии от предыдущего запроса в 48 мс. Как так?

SELECT *
   FROM t
     LEFT JOIN tu ON t.tid= tu.tid
WHERE t.tid IN (34377272,34376992,34377506,34377537,34377335,34377330,34377397,34377023,34376898,34377431,34376981,34377208,34377599,34377368,34377384,34377521,34377538,34377490,34377063,34373826,34377533,34376547,34377037,34377622,34377247,34377571,34377392,34376742,34377598,34377568,34377344,34377617,34377366,34377509,34377573,34377469,34377614,34377618,34376881,34377608,34377017,34377595,34377548,34377611,34377457,34377374,34376918,34377619,34377363,34377402,34377383,34377422,34377350,34377503,34377594,34377302,34377518,34377612,34377527,34377373,34377255,34377500,34377544,34377505,34377248,34377583,34377375,34376735,34377492,34377466,34376782,34377604,34376196,34377439,34377364,34377620,34377561,34377334,34377615,34376558,34376960,34376671,34376987,34377096,34377449,34377601,34377470,34375651,34377460,34377473,34377086,34377053,34377523,34377600,34375538,34377338,34377456,34375690,34376215,34377545,34377553,34377530,34377625,34376287,34375912,34376511,34377411,34377565,34376383,34377520,34377297,34376739,34377307,34377589,34377443,34377591,34377596,34376399,34377415,34377555,34377626,34377508,34377362,34376891,34377026,34377139,34377562,34377560,34377485,34377581,34377462,34377607,34377325,34377361,34377576,34377420,34376860,34377585,34377524,34377501,34377531,34377163,34377517,34377624,34376396,34377582,34377286,34377603,34376915,34375891,34377605,34377623,34375056,34377312,34377621,34376481,34376083,34376673,34377434,34376852,34377478,34377453,34377349,34377602,34377510,34377464,34377494,34377586,34376715,34377569,34376780,34377551,34376514,34377606,34377483)

"Nested Loop Left Join  (cost=0.98..2196.44 rows=175 width=460) (actual time=0.090..1.755 rows=175 loops=1)"
"  ->  Index Scan using t_tid on t (cost=0.56..851.88 rows=175 width=413) (actual time=0.060..1.135 rows=175 loops=1)"
"        Index Cond: (tid= ANY ('{34377272,34376992,34377506,34377537,34377335,34377330,34377397,34377023,34376898,34377431,34376981,34377208,34377599,34377368,34377384,34377521,34377538,34377490,34377063,34373826,34377533,34376547,34377037,34377622,34377247,34377571,34377392,34376742,34377598,34377568,34377344,34377617,34377366,34377509,34377573,34377469,34377614,34377618,34376881,34377608,34377017,34377595,34377548,34377611,34377457,34377374,34376918,34377619,34377363,34377402,34377383,34377422,34377350,34377503,34377594,34377302,34377518,34377612,34377527,34377373,34377255,34377500,34377544,34377505,34377248,34377583,34377375,34376735,34377492,34377466,34376782,34377604,34376196,34377439,34377364,34377620,34377561,34377334,34377615,34376558,34376960,34376671,34376987,34377096,34377449,34377601,34377470,34375651,34377460,34377473,34377086,34377053,34377523,34377600,34375538,34377338,34377456,34375690,34376215,34377545,34377553,34377530,34377625,34376287,34375912,34376511,34377411,34377565,34376383,34377520,34377297,34376739,34377307,34377589,34377443,34377591,34377596,34376399,34377415,34377555,34377626,34377508,34377362,34376891,34377026,34377139,34377562,34377560,34377485,34377581,34377462,34377607,34377325,34377361,34377576,34377420,34376860,34377585,34377524,34377501,34377531,34377163,34377517,34377624,34376396,34377582,34377286,34377603,34376915,34375891,34377605,34377623,34375056,34377312,34377621,34376481,34376083,34376673,34377434,34376852,34377478,34377453,34377349,34377602,34377510,34377464,34377494,34377586,34376715,34377569,34376780,34377551,34376514,34377606,34377483}'::integer[]))"
"  ->  Index Scan using unique_tu_tid on tu  (cost=0.42..7.68 rows=1 width=47) (actual time=0.002..0.002 rows=0 loops=175)"
"        Index Cond: (t.tid= tid)"
"Planning time: 0.652 ms"
"Execution time: 1.831 ms"

Answer 1

[Андрей Григорьев]

БД не успела накопить статистику что по фильтру OrderStatus остаётся мало записей, не может оценить профит от использования индекса, и на таком маленьком количестве записей предпочитает сделать full scan чтобы сразу поднять все данные в хеш по tid. Скорее всего analyze поможет.

Comments

[altair86]

Благодарю за совет. Попробовал так:
ANALYZE verbose tu
И так:
ANALYZE verbose tu (tid)

Результат один:

INFO:  analyzing "dbo.tu"
INFO:  "tu": scanned 2832 of 2832 pages, containing 153915 live rows and 8055 dead rows; 30000 rows in sample, 153915 estimated total rows

План запроса в результате анализа не поменялся, индекс не используется - идёт Seq Scan

[Андрей Григорьев]

altair86, почему tu то?) Ещё раз, ключевое место в этом запросе, та информация которая помогает вам понять что запрос можно оптимизировать выбрав по индексу несколько tid'ов из tu - это то, что выборка по t.OrderStatusID оставляет небольшое число различных tid. PostgreSQL получит эту информацию после analyze таблицы t :). Но вполне возможно что есть что-то ещё, что я упускаю, и analyze будет недостаточно.

[altair86]

Андрей Григорьев, Благодарю за комментарий. Запомню для дальнейших изысканий.

В итоге я разрешил проблему так: к 150 тыс записей в таблицу tu добавил ещё 100 тыс фиктивных записей,- не помогло,- а затем ещё 100 тыс и индекс начал работать.
Запрос начал быстрее работать, хотя и в некоторых сценариях не так, как я ожидал.

Answer 2

[globalmac]

Думаю Вам поможет данная статья - https://www.datadoghq.com/blog/100x-faster-postgre...

Вместо:

SELECT * FROM t LEFT JOIN tu ON t.tid= tu.tid WHERE t.tid IN (34377272,34376992,34377506,...)

Попробуйте:

SELECT * FROM t LEFT JOIN tu ON t.tid= tu.tid WHERE t.tid = ANY (VALUES (34377272,34376992,34377506,...))

Comments

[altair86]

Благодарю за ответ.
Проблема не в этом, проблема в первом запросе, где по какой-то причине не задействован индекс.

Answer 3

[Vitsliputsli]

Постройте составной индекс по обоим полям.

Comments

[altair86]

Благодарю за ответ.
Составной индекс невозможно построить, задействованы разные таблицы.

[Vitsliputsli]

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

[altair86]

Vitsliputsli, Здравствуйте. А как это делается: принудительно указывается индекс?

[Vitsliputsli]

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