badcasedaily1 21 апр в 12:34

Почему COUNT(*) быстрее, чем COUNT(col) — и когда это не так

Простой

5 мин

22K

Блог компании OTUSSQL*PostgreSQL*Программирование*Базы данных*

Обзор

Привет, Хабр!

Тема кажется на первый взгляд банальной: зачем COUNT(col), если есть COUNT(*)? Но за ней скрывается куча нюансов: MVCC‑проверки, visibility map, index‑only scan, параллелизм… И на разных версиях PostgreSQL COUNT(*) может вести себя по‑разному. Разберём всё досконально.

Семантика по стандарту SQL

COUNT(*) считает все строки результирующего набора, независимо от содержимого колонок.

COUNT(expression) считает только те строки, где expression IS NOT NULL.

SELECT COUNT(*) FROM users;       -- считает все строки
SELECT COUNT(user_id) FROM users; -- считает только не-NULL значения user_id

MVCC и visibility map

MVCC‑модель и поля xmin/xmax

PostgreSQL хранит все версии строк в куче. Каждая версия содержит:

xmin: XID транзакции, которая создала tuple;
xmax: XID транзакции, которая «удалила» или обновила его (логически).

При любом чтении движок проверяет, видна ли эта версия:

Активна ли транзакция с xmin?
Завершена ли транзакция с xmax?
Находится ли она в пределах «видимости» текущей транзакции?

Чтобы ответить «жива ли строка», PostgreSQL читает заголовок tuple и может обращаться к каталогу транзакций.

Visibility map

Проверка видимости для каждой строки — дорого. Чтобы избежать чтения heap при index‑only scan, есть специальная структура:

Visibility map: битовая карта, где каждый бит соответствует целой странице (8 kB) таблицы и показывает, что все строки на странице видимы ВСЕМ транзакциям.

Как работает:

После VACUUM страница помечается all‑visible, если на ней нет «грязных» версий.
Любые обновления/удаления/вставки по этой таблице сбрасывают бит у соответствующей страницы.
Дальнейший VACUUM может вернуть бит.

Если бит=1, индексный скан доверяет visibility map и не ходит в heap.

Классический сценарий: COUNT(*) → Seq Scan

Нет «привязки» к какой‑либо колонке, планировщик не может выбрать индекс. Даже при наличии индекса по id нет данных о xmin/xmax, придётся проверять видимость через heap → Seq Scan.

-- Сценарий A: базовый full table scan для COUNT(*)
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM t_events;

Seq Scan on t_events  (cost=0.00..14923.00 rows=1000000 width=0)
  (actual time=0.098..213.402 rows=1 loops=1)
  Buffers: shared hit=8000

Время: ~213 ms. Страниц прочитано: 8000 (все страницы таблицы).

Когда COUNT(col) летает: index‑only scan

Если есть индекс по колонке col и:

col NOT NULL;
Индекс покрывает именно эту колонку;
Все страницы помечены all‑visible в visibility map;

то PostgreSQL может выполнить Index‑only Scan:

CREATE INDEX idx_event_time ON t_events (event_time);
VACUUM ANALYZE t_events;

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(event_time) FROM t_events;

Index Only Scan using idx_event_time on t_events
  Index Cond: (event_time IS NOT NULL)
  Heap Fetches: 0
  Buffers: shared hit=300
  (actual time=0.050..4.572 rows=1 loops=1)

Время: ~4.5 ms. Страниц (индекс): 300. Heap Fetches: 0

Выигрыш: ≈50× по сравнению с full table scan.

Counter‑intuitive: COUNT(*) через индекс

Parallel Index‑only Scan (PostgreSQL 12+)

Начиная с PostgreSQL 12 движок умеет:

Планировать Parallel Index‑only Scan для COUNT(*),
Если есть любой индекс по NOT NULL полю (даже без INCLUDE),
Таблица давно не менялась (visibility map чиста),
Параллелизм включён (max_parallel_workers_per_gather > 0).

Пример из комментариев (таблица tmp_test):

CREATE TABLE tmp_test (
  num integer NULL,
  val varchar
);

-- Наполняем 1 000 000 строк:
INSERT INTO tmp_test (num, val)
SELECT CASE WHEN i%2=0 THEN NULL ELSE 12345 END,
       repeat(G.i::text, 256)
FROM generate_series(1, 1000000) G(i);

-- Создаём индекс по nullable-полю num:
CREATE INDEX tmp_test_some_idx ON tmp_test(num);

-- Принудительный VACUUM для visibility map
VACUUM tmp_test;

-- Считаем строки
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM tmp_test;

Finalize Aggregate  (cost=10966.90..10966.91 rows=1 width=8)
  (actual time=29.826..32.236 rows=1 loops=1)
  ->  Gather
        Workers Launched: 3
        ->  Partial Aggregate
              ->  Parallel Index Only Scan using tmp_test_some_idx
                 Heap Fetches: 0
                 (actual time=0.127..17.083 rows=250000 loops=4)

Время: ~32 ms (вместо ~206 ms без параллелизма и index‑only).Heap Fetches: 0 — индекс покрывает всё, visibility map чист

Вывод: для COUNT(*) достаточно
индекса по NOT NULL-полю,
чистой visibility map,
параллелизма — и движок пойдёт по индексу.

Все четыре варианта

Запрос	План	Время	Buffers / Heap Fetches
`SELECT COUNT(*)`	Seq Scan	~213 ms	shared hit=8000
`SELECT COUNT(event_time)`	Index‑only Scan	~4.5 ms	shared hit=300 / 0 fetches
`SELECT COUNT(user_id)`	Seq Scan (без индекса)	~230 ms	shared hit=8000
`SELECT COUNT(user_id)` (с индексом)	Index‑only Scan	~~6 ms	shared hit≈300 / 0 fetches
`SELECT COUNT(1)`	То же, что `COUNT(*)`	~213 ms	shared hit=8000
`SELECT COUNT(*)` (PG 14+, параллельный)	Parallel Index‑only Scan	~~30 ms	shared hit≈300 / 0 fetches

COUNT(DISTINCT) — совсем другое дело

COUNT(DISTINCT col) никак не оптимизируется индексом: движку придётся:

Собрать все значения col (индексный или обычный скан);
Отсортировать и сгруппировать или построить хэш‑таблицу в памяти;
При большом объёме — spill в temp‑файлы, merge‑сортировка на диске.

EXPLAIN ANALYZE
SELECT COUNT(DISTINCT user_id) FROM t_events;

План обычно:

Aggregate
  ->  Index Only Scan using idx_user_id on t_events

Но в Aggregate лежит сам DISTINCT, и он до или после сбора данных держит хэш/сортировку — нагрузка на CPU/RAM/Disk.

Альтернатива:

approx_count_distinct(col) из модуля hyperloglog,
Другие подобные агрегаты (PostGIS‑Tiger‑Geocoder тоже даёт агрегаты),
Благодаря этому вы получаете приблизительный, но очень быстрый результат.

Итоговые рекомендации

Для OLTP‑сценариев выбирайте COUNT(col) по полю с NOT NULL вместе с соответствующим индексом и свежим VACUUM. Если всё‑таки нужен COUNT(*), убедитесь, что у вас есть индекс по любому NOT NULL полю и включён параллелизм, чтобы движок мог пойти в Index‑only Scan.

В OLAP‑нагрузках классический COUNT(*) через Seq Scan задействует все воркеры равномерно, но лучше фильтровать данные по дате date‐partitioning — так вы уменьшите объём сканируемой кучи. А для уникальных подсчётов вместо COUNT(DISTINCT) переходите на приближённые агрегаты типа HyperLogLog approx_count_distinct(), чтобы не вываливаться в temp‑файлы и не бить по памяти.

Не забывайте про автоматизацию: настройте autovacuum параметры vacuum_scale_factor, vacuum_freeze_table_age и регулярно мониторьте состояние visibility map в pg_visibility — это ключ к стабильной производительности ваших index‐only планов.

Если вам близки темы оптимизации SQL-запросов и хочется выжать из базы максимум — приглашаем на открытый урок 22 апреля, где разберём, как автоматизировать рутину с помощью хранимых процедур в PostgreSQL и MS SQL Server. На практике покажем, как оформлять бизнес-логику в процедуры, управлять параметрами, повышать читаемость и безопасность кода — всё, что экономит нервы и процессорное время.

Записаться на урок можно на странице курса «SQL для разработчиков и аналитиков».

Хабы: