COUNT(*) в PostgreSQL: MVCC, visibility map и стоимость выполнения!
В PostgreSQL точный COUNT(*) требует определить количество строк, видимых текущему MVCC snapshot. Глобального счётчика, который можно было бы использовать для транзакционно корректного результата, у таблицы нет:
SELECT COUNT(*)
FROM orders;
На большой таблице планировщик часто выбирает Seq Scan, поскольку для точного результата всё равно требуется обработать множество видимых строк:
Aggregate
-> Seq Scan on orders
Наличие PRIMARY KEY или другого подходящего индекса не гарантирует его использование. Если модель стоимости считает путь через индекс дешевле, PostgreSQL может выполнить запрос через Index Only Scan:
Aggregate
-> Index Only Scan using orders_pkey on orders
Однако Index Only Scan не означает получение готового количества записей из индекса. Информация, необходимая для определения MVCC-видимости строк, находится в heap, а не в самом индексе.
PostgreSQL оптимизирует эту проверку с помощью visibility map. Если страница отмечена как all-visible, PostgreSQL может считать находящиеся на ней строки видимыми без дополнительного обращения к heap:
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders;
После изменения страницы её флаг all-visible сбрасывается и впоследствии может быть снова установлен VACUUM.
Поэтому на активно изменяемых таблицах даже Index Only Scan может требовать дополнительных обращений к heap:
Index Only Scan using orders_pkey on orders
Heap Fetches: 18427
Таким образом, производительность COUNT(*) зависит не только от размера таблицы и наличия индекса, но и от состояния visibility map, характера нагрузки, работы autovacuum, выбранного плана выполнения и состояния кэша PostgreSQL.
Если транзакционно точное количество строк не требуется, можно использовать статистическую оценку из pg_class:
SELECT reltuples::bigint
FROM pg_class
WHERE oid = 'orders'::regclass;
reltuples обновляется, в частности, при VACUUM и ANALYZE и остаётся приблизительной оценкой. Если статистика для таблицы ещё не собиралась, значение может быть -1.
🔥 Для больших таблиц это принципиально разные варианты: полный MVCC-корректный подсчёт или быстрое получение приблизительной статистической оценки.
➡️ SQL Ready | #практика
В PostgreSQL точный COUNT(*) требует определить количество строк, видимых текущему MVCC snapshot. Глобального счётчика, который можно было бы использовать для транзакционно корректного результата, у таблицы нет:
SELECT COUNT(*)
FROM orders;
На большой таблице планировщик часто выбирает Seq Scan, поскольку для точного результата всё равно требуется обработать множество видимых строк:
Aggregate
-> Seq Scan on orders
Наличие PRIMARY KEY или другого подходящего индекса не гарантирует его использование. Если модель стоимости считает путь через индекс дешевле, PostgreSQL может выполнить запрос через Index Only Scan:
Aggregate
-> Index Only Scan using orders_pkey on orders
Однако Index Only Scan не означает получение готового количества записей из индекса. Информация, необходимая для определения MVCC-видимости строк, находится в heap, а не в самом индексе.
PostgreSQL оптимизирует эту проверку с помощью visibility map. Если страница отмечена как all-visible, PostgreSQL может считать находящиеся на ней строки видимыми без дополнительного обращения к heap:
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders;
После изменения страницы её флаг all-visible сбрасывается и впоследствии может быть снова установлен VACUUM.
Поэтому на активно изменяемых таблицах даже Index Only Scan может требовать дополнительных обращений к heap:
Index Only Scan using orders_pkey on orders
Heap Fetches: 18427
Таким образом, производительность COUNT(*) зависит не только от размера таблицы и наличия индекса, но и от состояния visibility map, характера нагрузки, работы autovacuum, выбранного плана выполнения и состояния кэша PostgreSQL.
Если транзакционно точное количество строк не требуется, можно использовать статистическую оценку из pg_class:
SELECT reltuples::bigint
FROM pg_class
WHERE oid = 'orders'::regclass;
reltuples обновляется, в частности, при VACUUM и ANALYZE и остаётся приблизительной оценкой. Если статистика для таблицы ещё не собиралась, значение может быть -1.
🔥 Для больших таблиц это принципиально разные варианты: полный MVCC-корректный подсчёт или быстрое получение приблизительной статистической оценки.
➡️ SQL Ready | #практика