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

[ptr128]

COUNT(*) — не панацея и не зло

• Он ленив: не хочет использовать индекс, даже если тот рядом.

А вот с этим не соглашусь.

EXPLAIN ANALYZE SELECT COUNT(*) FROM FactLoad;

Finalize Aggregate  (cost=525455.96..525455.97 rows=1 width=8) (actual time=2747.147..2895.738 rows=1 loops=1)
  ->  Gather  (cost=525455.13..525455.94 rows=8 width=8) (actual time=2747.028..2895.719 rows=9 loops=1)
        Workers Planned: 8
        Workers Launched: 8
        ->  Partial Aggregate  (cost=524455.13..524455.14 rows=1 width=8) (actual time=2742.027..2742.028 rows=1 loops=9)
              ->  Parallel Index Only Scan using factload_pk_idx on factload  (cost=0.56..509712.52 rows=5897045 width=0) (actual time=0.233..2540.437 rows=5244137 loops=9)
                    Heap Fetches: 2027564
Planning Time: 0.129 ms
Execution Time: 2895.778 ms

Всего-то есть первичный ключ у таблицы.

Для сравнения:

EXPLAIN ANALYZE SELECT COUNT(id) FROM FactLoad;

Finalize Aggregate  (cost=525455.96..525455.97 rows=1 width=8) (actual time=2772.525..2925.239 rows=1 loops=1)
  ->  Gather  (cost=525455.13..525455.94 rows=8 width=8) (actual time=2772.251..2925.232 rows=9 loops=1)
        Workers Planned: 8
        Workers Launched: 8
        ->  Partial Aggregate  (cost=524455.13..524455.14 rows=1 width=8) (actual time=2766.728..2766.729 rows=1 loops=9)
              ->  Parallel Index Only Scan using factload_pk_idx on factload  (cost=0.56..509712.52 rows=5897045 width=8) (actual time=0.145..2551.223 rows=5244137 loops=9)
                    Heap Fetches: 2027564
Planning Time: 0.130 ms
Execution Time: 2925.275 ms

[ptr128]

Теперь более полный анализ.

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

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);

Индексов нет, так что на данный момент обязательно будет Seq Scan.

Создаем индекс.

CREATE INDEX tmp_test_some_idx ON tmp_test(num);

Если сразу после создания индекса выполним

EXPLAIN ANALYZE SELECT COUNT(*) FROM tmp_test;

То увидим:

Finalize Aggregate  (cost=259735.12..259735.13 rows=1 width=8) (actual time=165.659..206.082 rows=1 loops=1)
  ->  Gather  (cost=259734.60..259735.11 rows=5 width=8) (actual time=165.563..206.074 rows=6 loops=1)
        Workers Planned: 5
        Workers Launched: 5
        ->  Partial Aggregate  (cost=258734.60..258734.61 rows=1 width=8) (actual time=161.432..161.432 rows=1 loops=6)
              ->  Parallel Seq Scan on tmp_test  (cost=0.00..246264.28 rows=4988128 width=0) (actual time=0.035..153.994 rows=166667 loops=6)
Planning Time: 0.120 ms
Execution Time: 206.130 ms

Однако, стоит нам подождать пока AUTOVACUUM доберется до нашей таблицы или же явно выполнить

VACUUM tmp_test;

То картина сразу изменится:

Finalize Aggregate  (cost=10966.90..10966.91 rows=1 width=8) (actual time=29.826..32.236 rows=1 loops=1)
  ->  Gather  (cost=10966.58..10966.89 rows=3 width=8) (actual time=29.744..32.230 rows=4 loops=1)
        Workers Planned: 3
        Workers Launched: 3
        ->  Partial Aggregate  (cost=9966.58..9966.59 rows=1 width=8) (actual time=26.062..26.062 rows=1 loops=4)
              ->  Parallel Index Only Scan using tmp_test_some_idx on tmp_test  (cost=0.42..9160.13 rows=322581 width=0) (actual time=0.127..17.083 rows=250000 loops=4)
                    Heap Fetches: 0
Planning Time: 0.097 ms
Execution Time: 32.268 ms

Обратите внимание на строку Heap Fetches. Это как раз и есть количество обращений к куче. Сразу после VACUUM обращений не потребовалось вообще. А вот если мы добавим строки в таблицу и сразу же попробуем посчитать количество строк в ней

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, 10) G(i);
EXPLAIN ANALYZE SELECT COUNT(*) FROM tmp_test;

То увидим:

Finalize Aggregate  (cost=10967.56..10967.57 rows=1 width=8) (actual time=29.298..31.960 rows=1 loops=1)
  ->  Gather  (cost=10967.24..10967.55 rows=3 width=8) (actual time=29.180..31.954 rows=4 loops=1)
        Workers Planned: 3
        Workers Launched: 3
        ->  Partial Aggregate  (cost=9967.24..9967.25 rows=1 width=8) (actual time=25.614..25.614 rows=1 loops=4)
              ->  Parallel Index Only Scan using tmp_test_some_idx on tmp_test  (cost=0.42..9160.73 rows=322604 width=0) (actual time=0.089..16.643 rows=250002 loops=4)
                    Heap Fetches: 110
Planning Time: 0.109 ms
Execution Time: 31.991 ms

Теперь несколько страниц потребовали доступа к куче.

Как видим, для того, чтобы COUNT(*) воспользовался индексом, достаточно иметь даже не уникальный индекс по NULL полю. Главное, чтобы для таблицы уже была построена карта видимости.

[slonopotamus]

Ну то есть ключевое утверждение в статье оказалось ложным. Забавно.

[ptr128]

Не ошибается, только тот, кто ничего не делает. Зато эта ошибка сподвигла меня на детальный анализ, что явно принесло пользу.

[slonopotamus]

Ну теперь было бы неплохо @badcasedaily1 или убрать вводящую в заблуждение статью в черновики или переписать с учётом правильной информации.

[ptr128]

Вот с этим я согласен и сам удивляюсь, почему он так до сих пор не сделал

[namee]

Статья не полная без сравнения с count(1)

[ptr128]

Для PostgreSQL - это одно и то же

[iBljad]

по сравнению с каким из вариантов?

[ptr128]

COUNT(*)

[Akina]

В PostgreSQL это аппаратно отработано очень оптимально.

Поясните, пожалуйста, эту фразу максимально подробно, потому как при первом взгляде она похожа на бред сумасшедшего. Я вообще не понимаю, о каких аппаратных ресурсах, реализующих хоть что-то подобное, может идти речь... особенно с учётом того, что установка Postgres (как и любого другого программного обеспечения) в принципе не изменяет аппаратной конфигурации системы.

[AlexKMK]

https://doxygen.postgresql.org/simd_8h_source.html

У процессоров intel есть спец. Команды которые умеют работать с массивами данных.

[GerrAlt]

Если речь об этом то это не у Postgres что-то аппаратно отработано, а Postgres может использовать платформозависимые команды для ускорения определенных операций.

Отдельно хочется сказать про то что "оптимально" это вроде как превосходная степень, поэтому "очень оптимально" не бывает.

[des1roer]

а зачем оно здесь, если это бред нейросети? select count(*) использует индекс (pg 17.2)

Finalize Aggregate  (cost=53197.49..53197.50 rows=1 width=8) (actual time=260.894..265.277 rows=1 loops=1)
  ->  Gather  (cost=53197.27..53197.48 rows=2 width=8) (actual time=260.886..265.271 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=52197.27..52197.28 rows=1 width=8) (actual time=254.429..254.430 rows=1 loops=3)
              ->  Parallel Index Only Scan using idx_atatus_transaction_created_at on atatus_transaction  (cost=0.43..49552.24 rows=1058014 width=0) (actual time=0.044..198.121 rows=845591 loops=3)
                    Heap Fetches: 552870
Planning Time: 0.120 ms
Execution Time: 265.321 ms

[ptr128]

бред нейросети

А кстати, это объясняет, почему автор до сих пор не исправил ошибку в статье, хотя я еще вчера ему на неё указал.

[Aleksey_dark]

Буквально вчера использовал postgresql и заметил, что любого рода селекты, в том числе и count тормозят, если отключена индексация строк