Комментарии 7
Странно, что не рассмотрен вариант включения третьего поля в индекс. Не поленился, воспроизвёл. Результаты:
no index (a,v) (a,v),e (a,v,e)
JOIN 391/491 3,65/29 0,710/192 0,550/192
INTERSECT 328/712 0,387/196 0,534/197 0,367/197
COUNT 217/548 0,425/194 0,619/195 0,400/195
COUNT DISTINCT 217/522 0,724/194 0,989/195 0,704/195https://dbfiddle.uk/?rdbms=postgres_12&fiddle=581825050ffad372d4618183569b73c4
Как можно увидеть, включение третьего поля в индексное выражение улучшает производительность всех запросов приблизительно в полтора раза.
PS. Никогда раньше не обращал внимания, что PostgreSQL не умеет тривиального COUNT(DISTINCT a,b)...
На моей выборке индекс (a, v, e) дает 131/10 против 121/9 у INCLUDE-версии:
HashSetOp Intersect (actual time=0.130..0.131 rows=1 loops=1)
Buffers: shared hit=10
-> Append (actual time=0.023..0.101 rows=202 loops=1)
Buffers: shared hit=10
-> Subquery Scan on "*SELECT* 1" (actual time=0.022..0.053 rows=98 loops=1)
Buffers: shared hit=5
-> Index Only Scan using eav_idx3 on tst_eav (actual time=0.020..0.039 rows=98 loops=1)
Index Cond: ((a = 1) AND (v = 1))
Heap Fetches: 0
Buffers: shared hit=5
-> Subquery Scan on "*SELECT* 2" (actual time=0.010..0.034 rows=104 loops=1)
Buffers: shared hit=5
-> Index Only Scan using eav_idx3 on tst_eav tst_eav_1 (actual time=0.010..0.023 rows=104 loops=1)
Index Cond: ((a = 2) AND (v = 2))
Heap Fetches: 0
Buffers: shared hit=5А в вашем варианте INTERSECT + INCLUDE не дали Index Only Scan, а остались на Bitmap Heap Scan - потому и выигрыша не получилось.
Иными словами, всё зависит от получившихся данных (для генерации использовался именно запрос из статьи). Ведь именно на статистику опирается сервер при построении плана... и тогда получается, что вся эта оптимизация не более чем игра в рулетку - повезёт или нет, построится нормальный план или нет, а самому исследованию - грош цена.
Есть у постгресса хинты, позволяющие ему сказать, что он должен использовать именно Index Only Scan?
Есть у постгресса хинты, позволяющие ему сказать, что он должен использовать именно Index Only Scan?
https://postgrespro.ru/docs/postgresql/14/runtime-config-query#RUNTIME-CONFIG-QUERY-CONSTANTS
cpu_index_tuple_cost - можно укрутить "в ноль", например
получается, что вся эта оптимизация не более чем игра в рулетку - повезёт или нет
Не совсем так.
В варианте отсутствия e в индексе мы гарантированно не можем получить Index Only Scan - данных просто нет. Будет или Bitmap Heap Scan, или Index Scan (с дочитыванием heap), если очень повезет, и данных окажется относительно немного.
Если e будет ключевым полем, а не в INCLUDE - вероятность получить Index Only Scan становится ниже.
Но вполне очевидно, что если у нас значению ключа будет соответствовать 100K записей из миллиона, то Bitmap Heap Scan все равно окажется статистически много выгоднее. А если 900K - то даже Seq Scan станет наиболее оптимальным вариантом.
Поэтому варианта "гарантированно" получить IOS нету, да он и не нужен, поскольку не является оптимальным "вообще всегда". Но мы можем постараться увеличить его вероятность приведенными способами.
postgrespro.ru/docs/postgresql/14/runtime-config-query#RUNTIME-CONFIG-QUERY-CONSTANTS
cpu_index_tuple_cost — можно укрутить «в ноль», например
думаю, речь о том, как в ms sql работает конструкция with index в запросе. Что отключает планировщик и принуждает использовать для таблицы именно этот индекс.
Не самый распространенный модуль, но все же: https://postgrespro.ru/docs/enterprise/13/pg-hint-plan
Никогда раньше не обращал внимания, что PostgreSQL не умеет тривиального COUNT(DISTINCT a,b)
Почему не умеет? А вот так:
count(distinct (a, b))Или это не то, что вы имели в виду?
SQL HowTo: разные варианты работы с EAV