Всем 👋 Подсмотрел на просторах следующий трюк. Запрос вида create index table_idx

on table(col1, col2);
select count(distinct col2) from table where col1 = ?
можно ускорить, переписав на
select count(*) from (select distinct col2 from table where col1 = ?) t
Действительно работает, но холодные запуски подсчёта всё равно занимают больше 1с при количестве записей в 20-40к.
Повторные запуски занимают 150-300мс, первый вариант обычно в 2 раза дольше работает. Почему так?
Понятное дело, что скорее всего PG кэширует данные, но непонятно всё-таки почему разница такая большая.

Извините, но, по-моему, это очередная серия "кто-то не умеет в benchmarks". ;( > Действительно работает Не работает, насколько я вижу. > но холодные запуски подсчёта всё равно занимают больше 1с > Повторные запуски занимают 150-300мс, Потому что выполняется кеширование данных, почти наверняка. > первый вариант обычно в 2 раза дольше работает. Почему так? Потому что Вы что-то не так измеряете, скорее всего. > но непонятно всё-таки почему разница такая большая. Потому что чтение с диска и из RAM отличаются по производительности как минимум на пару порядков. Что тут неожиданного?

Я просто вызываю запрос несколько раз с одним и тем же значением col1. Первый вызов в обоих случаях > 1c. Повторные вызовы count(distinct col2) в среднем два раза дольше count(*) по тому времени, что я вижу в IDE. Почему count работает долго, если количество записей можно достать из индекса? Вроде где-то читал, что PG нужно проверить видимость всех записей в индексе для сессии, это так?

> по тому времени, что я вижу в IDE. Можно выбросить эту IDE (ну, как вариант). ;) А так — попробуйте это в psql / покажите планы. > Первый вызов в обоих случаях > 1c. И уже это странно, потому что планы должны быть схожие (я даже сходу не вижу, как можно так накосить в настройке PostgreSQL, чтобы это было не так), и выполненный первым запрос должен кешировать данные для второго. > Почему count работает долго, если количество записей можно достать из индекса? Нельзя, потому что там этой информации нет (и в других СУБД сейчас — тоже нет, кстати... а на нужном этому запросу уровне детализации — никогда и не было). > Вроде где-то читал, что PG нужно проверить видимость всех записей в индексе для сессии, это так? В MVCC нужно проверять видимость записей для сессии независимо ни от чего, но для проверки видимости есть оптимизации (как при чтении индексов, так и таблиц).

Вот планы count(distinct) Aggregate (cost=87219.99..87220.00 rows=1 width=8) (actual time=345.630..345.632 rows=1 loops=1) -> Index Only Scan using <index> on <table> (cost=0.56..86958.17 rows=104726 width=14) (actual time=0.031..36.254 rows=99662 loops=1) Index Cond: (<col1> = '08'::text) Heap Fetches: 99662 Planning Time: 0.103 ms Execution Time: 345.666 ms count(*): Aggregate (cost=89494.72..89494.73 rows=1 width=8) (actual time=75.463..75.464 rows=1 loops=1) -> HashAggregate (cost=87219.99..88230.98 rows=101099 width=14) (actual time=67.109..73.856 rows=40064 loops=1) Group Key: <table>.<col2> -> Index Only Scan using <index> on <table> (cost=0.56..86958.17 rows=104726 width=14) (actual time=0.132..43.176 rows=99662 loops=1) Index Cond: (<col1> = '08'::text) Heap Fetches: 99662 Planning Time: 0.323 ms Execution Time: 76.602 ms

(Был занят, извините.) Я тут немного поэкспериментировал — в принципе, с [относительно] длинными text что-то подобное у меня получилось воспроизвести (я почему-то думал только про типы вроде bigint/timestamptz и т.п., когда отвечал), но разница намного меньше, чем в этом примере (но я измерял, а не пробовал пару запросов). Так что у меня есть ещё вопросы: . А какая это версия PostgreSQL? . Можете показать как минимум EXPLAIN (ANALYZE, BUFFERS) (а лучше — больше, зависит от версии).

Да, у меня text как-раз везде. Скину EXPLAIN (ANALYZE, BUFFERS) попозже

Можно что-то еще добавить к ANALYZE и BUFFERS на 12ой версии?

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) SELECT ...

Aggregate (cost=478293.66..478293.67 rows=1 width=8) (actual time=3445.763..3445.765 rows=1 loops=1) Output: count(DISTINCT <col2>) Buffers: shared hit=11103 -> Bitmap Heap Scan on <schema>.<table> (cost=10937.67..476142.66 rows=860400 width=14) (actual time=54.687..130.177 rows=863534 loops=1) " Output: <col1>, <col2>, <col3>, <col4>, <col5>, <col6>, <col7>" Recheck Cond: (<table>.<col3> = '10'::text) Heap Blocks: exact=6845 Buffers: shared hit=11103 -> Bitmap Index Scan on <col3>_search_idx (cost=0.00..10722.57 rows=860400 width=0) (actual time=53.940..53.941 rows=863534 loops=1) Index Cond: (<table>.<col3> = '10'::text) Buffers: shared hit=4258 "Settings: effective_cache_size = '512MB', effective_io_concurrency = '200', max_parallel_workers_per_gather = '4', parallel_setup_cost = '1e+06', random_page_cost = '1', work_mem = '128MB'" Planning Time: 8.745 ms Execution Time: 3445.829 ms Aggregate (cost=492950.97..492950.98 rows=1 width=8) (actual time=403.331..403.334 rows=1 loops=1) Output: count(*) Buffers: shared hit=11103 -> HashAggregate (cost=478293.66..484808.02 rows=651436 width=14) (actual time=359.857..395.240 rows=174962 loops=1) Output: <table>.<col2> Group Key: <table>.<col2> Buffers: shared hit=11103 -> Bitmap Heap Scan on <schema>.<table> (cost=10937.67..476142.66 rows=860400 width=14) (actual time=54.543..145.863 rows=863534 loops=1) " Output: <table>.<col1>, <table>.<col2>, <table>.<col3>, <table>.<col4>, <table>.<col5>, <table>.<col6>, <table>.<col7>" Recheck Cond: (<table>.<col3> = '10'::text) Heap Blocks: exact=6845 Buffers: shared hit=11103 -> Bitmap Index Scan on <col3>_search_idx (cost=0.00..10722.57 rows=860400 width=0) (actual time=53.803..53.804 rows=863534 loops=1) Index Cond: (<table>.<col3> = '10'::text) Buffers: shared hit=4258 "Settings: effective_cache_size = '512MB', effective_io_concurrency = '200', max_parallel_workers_per_gather = '4', parallel_setup_cost = '1e+06', random_page_cost = '1', work_mem = '128MB'" Planning Time: 0.225 ms Execution Time: 410.856 ms При set enable_bitmapscan=false: Aggregate (cost=705703.45..705703.46 rows=1 width=8) (actual time=2610.040..2610.043 rows=1 loops=1) Output: count(DISTINCT <col2>) Buffers: shared hit=855509 -> Index Only Scan using <col3>_search_idx on <schema>.<table> (cost=0.56..703552.45 rows=860400 width=14) (actual time=0.033..333.383 rows=863534 loops=1) " Output: <col3>, <col1>, <col2>" Index Cond: (<table>.<col3> = '10'::text) Heap Fetches: 863534 Buffers: shared hit=855509 "Settings: effective_cache_size = '512MB', effective_io_concurrency = '200', enable_bitmapscan = 'off', max_parallel_workers_per_gather = '4', parallel_setup_cost = '1e+06', random_page_cost = '1', search_path = '<schema>, public', work_mem = '128MB'" Planning Time: 0.103 ms Execution Time: 2610.082 ms Aggregate (cost=717958.06..717958.07 rows=1 width=8) (actual time=421.481..421.483 rows=1 loops=1) Output: count(*) Buffers: shared hit=855509 -> Unique (cost=0.56..707854.45 rows=808289 width=18) (actual time=0.044..412.811 rows=174962 loops=1) " Output: <table>.<col1>, <table>.<col2>" Buffers: shared hit=855509 -> Index Only Scan using <col3>_search_idx on <schema>.<table> (cost=0.56..703552.45 rows=860400 width=18) (actual time=0.042..332.752 rows=863534 loops=1) " Output: <table>.<col1>, <table>.<col2>" Index Cond: (<table>.<col3> = '10'::text) Heap Fetches: 863534 Buffers: shared hit=855509 "Settings: effective_cache_size = '512MB', effective_io_concurrency = '200', enable_bitmapscan = 'off', max_parallel_workers_per_gather = '4', parallel_setup_cost = '1e+06', random_page_cost = '1', search_path = '<schema>, public', work_mem = '128MB'" Planning Time: 0.179 ms Execution Time: 421.544 ms

Да, любопытно. А что будет при (примерно, поправьте таблицы / поля)? EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) SELECT DISTINCT <col2> FROM ( SELECT <col2> FROM <table> WHERE <col3> = '10' OFFSET 0 ) AS nq;

HashAggregate (cost=486897.66..495501.66 rows=860400 width=14) (actual time=456.459..490.698 rows=174962 loops=1) Output: <table>.<col2> Group Key: <table>.<col2> Buffers: shared hit=11103 -> Bitmap Heap Scan on <schema>.<table> (cost=10937.67..476142.66 rows=860400 width=14) (actual time=79.501..202.039 rows=863534 loops=1) Output: <table>.<col2> Recheck Cond: (<table>.<col3> = '10'::text) Heap Blocks: exact=6845 Buffers: shared hit=11103 -> Bitmap Index Scan on <col3>_search_idx (cost=0.00..10722.57 rows=860400 width=0) (actual time=78.524..78.524 rows=863534 loops=1) Index Cond: (<table>.<col3> = '10'::text) Buffers: shared hit=4258 "Settings: effective_cache_size = '512MB', effective_io_concurrency = '200', max_parallel_workers_per_gather = '4', parallel_setup_cost = '1e+06', random_page_cost = '1', search_path = '<schema>, public', work_mem = '128MB'" Planning Time: 0.219 ms Execution Time: 502.694 ms

Эх, я надеялся, что будет выбран sort (т.е. это не то). А если так? SELECT <col2> FROM ( SELECT <col2> FROM <table> WHERE <col3> = '10' OFFSET 0 ) AS nq ORDER BY <col2>;