Как оптимизировать запрос SELECT COUNT?

Question

[Антон]

Всегда считал, что если указать в теле COUNT поле, то запрос будет выполняться быстрее. Решил это проверить и удивился:

SELECT count(*) FROM public.news;

1 row retrieved starting from 1 in 252ms (execution: 245ms, fetching: 7ms)
1 row retrieved starting from 1 in 231ms (execution: 227ms, fetching: 4ms)

SELECT count(id) FROM public.news;

1 row retrieved starting from 1 in 343ms (execution: 340ms, fetching: 3ms)
1 row retrieved starting from 1 in 300ms (execution: 296ms, fetching: 4ms)

У запроса, в котором указано поле, всегда время больше. Выполнял запрос несколько раз с небольшим (несколько секунд) интервалом.

И теперь у меня возник вопрос: как же все-таки оптимизировать запрос по подсчету количества строк, чтобы он выполнялся максимально быстро?

Answer 1

[Алексей Немиро]

SELECT COUNT(*) FROM - перебрать все строки.
SELECT COUNT(id) FROM - перебрать все строки, в которых указанное поле (в данном случае id) имеет значение отличное от NULL.

Без указания полей - наиболее оптимальный вариант для PostgreSQL:
SELECT COUNT(*) FROM table WHERE field = value
https://wiki.postgresql.org/wiki/Slow_Counting

Если все совсем плохо, то как вариант, можно сделать собственный счетчик.

Вот вырезка из PostgreSQL Wiki на русском языке:

Почему "SELECT count(*) FROM bigtable;" работает медленно?
Потому что не используется индекс. PostgreSQL выполняет проверку видимости каждой записи и таким образом производит последовательное сканирование всей таблицы. Если вы хотите, вы можете отслеживать количество строк в таблице с помощью триггеров, но это вызовет замедление при операциях записи в таблицу.
Вы можете получить некоторую оценку. Колонка reltuples в таблице pg_class содержит информацию из результата выполнения последнего оператора ANALYZE на эту таблицу. На большой таблице, точность этого значения составляет тысячные доли процента, что вполне достаточно для многих целей.
"Точный" результат count, часто не будет точным долгое время в любом случае; из-за конкурентности MVCC, count будет точным только на момент вызова запущенного запроса SELECT count(*) (или ограничиваться уровнями изоляции транзакций данной транзакции), и может потерять актуальность уже в момент завершения запроса. При постоянной работе транзакций, изменяющий таблицу, два вызова count(*), которые завершатся в одно и то же время могут показать разные значения, если изменяющая транзакция завершилась между их вызовами.
https://wiki.postgresql.org/wiki/Часто_Задаваемые_...

Comments

[Антон]

Создал тестовую таблицу, в которую поместил несколько миллионов записей. Проверил COUNT(*) и COUNT(id) с WHERE и без.

Без WHERE выходит, что лучше использовать COUNT(*). Так можно выиграть даже больше 1 секунды.
С WHERE лучше использовать COUNT(id). В этом случае я выиграл даже больше 2-х секунд.

Answer 2

[Сергей Горностаев]

Есть хак. Очень быстрый, но слегка неточный.

SELECT reltuples FROM pg_class WHERE oid = 'schema_name.table_name'::regclass;

Например, в таблице с 31560 строками он только что выдал мне цифру 31558, но выполнялся в 20 раз быстрее. Точность зависит от того, на сколько часто запускается для таблицы ANALYZE.

Comments

[Антон]

А что делать, когда нужно использовать WHERE?
Например, нужно получить количество новостей от конкретного пользователя.

[Сергей Горностаев]

Антон: стараться чтобы выборка по конкретному пользователю не была слишком большой. Или, если количество пользователей небольшое, а считать надо все данные, то можно построить частичные индексы и выбирать reltuples для них.