Делаем быстрее POSTGRESQL COUNT (*)

Часто жалуются, что count (*) в PostgreSQL очень медленный.

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

Почему count (*) такой медленный?

Большинство людей без проблем понимают, что следующий запрос будет выполняться медленно:

SELECT count(*)
FROM /* сложный запрос */;

В конце концов, это сложный запрос, и PostgreSQL должен вычислить результат, прежде чем узнает, сколько строк он будет содержать.

Но многие люди потрясены, когда узнают, что следующий запрос медленный:

SELECT count(*) FROM large_table;

Тем не менее, если вы подумаете еще раз, все вышесказанное остается в силе: PostgreSQL должен вычислить результирующий набор, прежде чем сможет его посчитать. Поскольку в таблице не хранится «магический счетчик строк» (как в MyISAM MySQL), единственный способ подсчитать строки — это просмотреть их.

Поэтому count (*) обычно выполняет последовательное сканирование таблицы, что может быть довольно дорого.

Является ли "*" в count (*) проблемой?

"*" в SELECT * FROM… распространяется на все столбцы. Следовательно, многие люди считают, что использование count (*) неэффективно, и вместо этого следует записать count (id) или count (1).

Но "*" в count (*) совсем другое, оно просто означает «строку» и вообще не раскрывается (фактически, это «агрегат с нулевым аргументом»). Запись count (1) или count (id) на самом деле медленнее, чем count (*), потому что должно проверяться, равен ли аргумент NULL или нет (count, как и большинство агрегатов, игнорирует аргументы NULL).

Так что вы ничего не добьетесь, избегая "*".

Использование index only scan

Заманчиво сканировать небольшой индекс, а не всю таблицу, чтобы подсчитать количество строк. Однако это не так просто в PostgreSQL из-за его многоверсионной стратегии управления параллелизмом. Каждая версия строки («кортеж» (“tuple”)) содержит информацию о том, какому моментальному снимку базы данных она видна. Но эта (избыточная) информация не хранится в индексах. Поэтому обычно недостаточно подсчитать записи в индексе, поскольку PostgreSQL должен обратиться к записи таблицы («куче кортежей» (“heap tuple”)), чтобы убедиться, что запись индекса видна.

Для смягчения этой проблемы, PostgreSQL внедрил карту видимости (visibility map), структуру данных, которая хранит информацию о том, все ли кортежи в блоке таблицы видны всем или нет.
Если большинство блоков таблицы являются полностью видимыми, то при сканировании индекса не требуется часто посещать кучу кортежей для определения видимости. Такое сканирование индекса называется «index only scan», и при этом часто быстрее сканировать индекс для подсчета строк.

Теперь именно VACUUM поддерживает карту видимости, поэтому убедитесь, что autovacuum выполняется достаточно часто, если хотите использовать небольшой индекс для ускорения count(*).

Использование сводной таблицы

Я писал выше, что PostgreSQL не хранит количество строк в таблице.

Ведение такого подсчета строк представляет собой большие накладные расходы, так как данное событие возникает при каждой модификации данных и не окупается. Это была бы плохая сделка. Более того, поскольку разные запросы могут видеть разные версии строк, счетчик также должен быть версионным.

Но ничего не мешает вам реализовать такой счетчик строк самостоятельно.
Предположим, вы хотите отслеживать количество строк в таблице mytable. Вы можете сделать это следующим образом:

START TRANSACTION;
 
CREATE TABLE mytable_count(c bigint);
 
CREATE FUNCTION mytable_count() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' THEN
      UPDATE mytable_count SET c = c + 1;
 
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      UPDATE mytable_count SET c = c - 1;
 
      RETURN OLD;
   ELSE
      UPDATE mytable_count SET c = 0;
 
      RETURN NULL;
   END IF;
END;$$;
 
CREATE CONSTRAINT TRIGGER mytable_count_mod
   AFTER INSERT OR DELETE ON mytable
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE mytable_count();
 
-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
   FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
 
-- initialize the counter table
INSERT INTO mytable_count
   SELECT count(*) FROM mytable;
 
COMMIT;

Мы делаем все в одной транзакции, чтобы никакие изменения данных по параллельным транзакциям не могли быть «потеряны» из-за кольцевого условия.
Это гарантируется тем, что команда CREATE TRIGGER блокирует таблицу в режиме SHARE ROW EXCLUSIVE, что предотвращает все параллельные изменения.
Минусом является то, что все параллельные модификации данных должны ждать, пока не будет выполнен SELECT count(*).

Это дает нам действительно быструю альтернативу count (*), но ценой замедления всех изменений данных в таблице. Использование deferred constraint trigger гарантирует, что блокировка строки в mytable_count будет максимально короткой для улучшения параллелизма.

Несмотря на то, что эта таблица счетчиков может получать много обновлений, никакой опасности «раздувания таблицы» нет, потому что все это будут «горячие» обновления (HOT updates).

Вам действительно нужен count(*)

Иногда лучшим решением является поиск альтернативы.

Часто аппроксимация достаточно хороша, и вам не нужно точное количество. В этом случае вы можете использовать оценку, которую PostgreSQL использует для планирования запросов:

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';

Это значение обновляется как autovacuum, так и autoanalyze, поэтому оно никогда не должно превышать 10%. Вы можете уменьшить autovacuum_analyze_scale_factor для этой таблицы, чтобы autoanalize выполнялся там чаще.

Оценка количества результатов запроса

До сих пор мы исследовали, как ускорить подсчет строк таблицы.

Но иногда требуется знать, сколько строк вернет оператор SELECT без фактического выполнения запроса.

Очевидно, что единственный способ получить точный ответ на этот вопрос — это выполнить запрос. Но если оценка достаточно хорошая, вы можете использовать оптимизатор PostgreSQL, чтобы получить ее.

Следующая простая функция использует динамический SQL и EXPLAIN для получения плана выполнения запроса, переданного в качестве аргумента, и возвращает оценку числа строк:

CREATE FUNCTION row_estimator(query text) RETURNS bigint
   LANGUAGE plpgsql AS
$$DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
 
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;

Не используйте эту функцию для обработки ненадежных инструкций SQL, так как она по своей природе уязвима для SQL injection.

Автор: puyol_dev2

Источник