Ускорение запроса SELECT COUNT(*) для больших таблиц в PostgreSQL

Как всем хорошо известно, запросы SELECT COUNT(*) из больших таблиц в PostgreSQL работают очень медленно. Предлагаю полное решение по ускорению этого запроса при помощи функций и триггеров.

Рассмотрим на примере таблицы с ~200 000 записей:

SELECT COUNT(*) FROM users;
count
— 205043

Теперь делаем анализ запроса с включенным enable_seqscan:

SET enable_seqscan TO on;
EXPLAIN ANALYZE SELECT COUNT(*) FROM users;

QUERY PLAN
— Aggregate (cost=15813.70..15813.71 rows=1 width=0) (actual time=82.907..82.908 rows=1 loops=1)
-> Seq Scan on users (cost=0.00..15296.16 rows=207016 width=0) (actual time=0.014..55.505 rows=205043 loops=1)
Planning time: 0.211 ms
Execution time: 82.967 ms

Время выполнения составило: 82.967 ms.

Теперь с выключенным enable_seqscan:

SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT COUNT(*) FROM users;

QUERY PLAN
— Aggregate (cost=20156.95..20156.96 rows=1 width=0) (actual time=117.553..117.554 rows=1 loops=1)
-> Bitmap Heap Scan on users (cost=4343.25..19639.41 rows=207016 width=0) (actual time=28.354..92.228 rows=205043 loops=1)
Heap Blocks: exact=13226
-> Bitmap Index Scan on users_pkey (cost=0.00..4291.50 rows=207016 width=0) (actual time=25.247..25.247 rows=229621 loops=1)
Planning time: 0.141 ms
Execution time: 117.724 ms

Время выполнения составило: 117.724 ms, хотя в данном случае postgresql использовал индекс users_pkey, но стало только хуже.

Как видим время выполнения этих запросов слишком велико.

Разработанное мною решение уменьшит время выполения запроса до меньше чем 1ms. Решение состоит в следующем:

1. для каждой таблицы заводиться счетчик количества записей с названием ТАБЛИЦА_count_seq.
2. пишем функцию rows_count(), которая будет управлять счетчиком.
3. пишем функцию rows_count_update_trigger(), которая будет запускаться триггерами таблиц для автоматического изменения счетчика при запросах INSERT, DELETE, TRUNCATE.
4. подключаем триггеры, которые будет:
— увеличивать счетчик при INSERT
— уменьшать при DELETE
— сбрасывать при TRUNCATE
5. вместо SELECT COUNT(*), будем использовать SELECT rows_count('ТАБЛИЦА')

Итак, начнем.

1. Создание счетчика, который будет хранить текущее количества записей в таблице.

CREATE SEQUENCE users_count_seq MINVALUE 0 START 0;

2. Функция rows_count() — управление счетчиком.

CREATE OR REPLACE FUNCTION rows_count(
	tablename text, 
	step int default 0, 
	reset bool default false )
RETURNS SETOF bigint
LANGUAGE plpgsql AS $$
DECLARE
	tablename_seq text;
BEGIN
	tablename_seq := tablename || '_count_seq';
	-- сброс счетчика
	IF reset IS TRUE THEN
		RETURN QUERY EXECUTE '
			WITH records AS (
				SELECT count(*) AS rows_count
				FROM '||tablename||')
			SELECT setval($1,rows_count+$2)
			FROM records'
		USING tablename_seq,step;
	ELSEIF step = 0 THEN
		-- вывод текущего значения счетчика
		RETURN QUERY EXECUTE '
			SELECT last_value 
			FROM '||tablename_seq;
	ELSE
		-- увеличение или уменьшение счетчика
		RETURN QUERY EXECUTE '
			WITH records AS (
				SELECT last_value AS rows_count
				FROM '||tablename_seq||')
			SELECT setval($1,rows_count+$2)
			FROM records'
		USING tablename_seq,step;
	END IF;
END;
$$;

3. Функция rows_count_update_trigger() — триггерная функция для автоматического изменения счетчика.

CREATE OR REPLACE FUNCTION rows_count_update_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
DECLARE
BEGIN
	IF TG_OP = 'INSERT' THEN
	   -- увеличение счетчика при INSERT
	   EXECUTE 'SELECT rows_count($1,+1)' USING TG_RELNAME;
	   RETURN NEW;
	ELSEIF TG_OP = 'DELETE' THEN
	   -- уменьшение счетчика при DELETE
	   EXECUTE 'SELECT rows_count($1,-1)' USING TG_RELNAME;
	   RETURN OLD;
	ELSEIF TG_OP = 'TRUNCATE' THEN
	   -- сброс счетчика при TRUNCATE
	   EXECUTE 'SELECT rows_count($1,0,true)' USING TG_RELNAME;
	   RETURN OLD;
	END IF;
END;
$$;

4. Подключение тригерной функции к таблице.

CREATE TRIGGER rows_count_update_trigger
	AFTER INSERT OR DELETE ON users
	FOR EACH ROW EXECUTE PROCEDURE rows_count_update_trigger();
CREATE TRIGGER rows_count_reset_trigger
	AFTER TRUNCATE ON users
	FOR EACH STATEMENT EXECUTE PROCEDURE rows_count_update_trigger();

5. Смотрим резельтаты используя SELECT rows_count('ТАБЛИЦА')

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

Делаем сброс счетчика:

SELECT rows_count('users',0,true);
rows_count
— 205043

Видим что счетчик сбросился и показывает актуальное количества записей 205043. Запрос SELECT rows_count('users') вернет такой же результат 205043.

Анализ запроса SELECT rows_count('users'):

EXPLAIN ANALYZE SELECT rows_count('users');

Вывод:

QUERY PLAN
— Result (cost=0.00..5.25 rows=1000 width=0) (actual time=0.242..0.244 rows=1 loops=1)
Planning time: 0.033 ms
Execution time: 0.260 ms

Время выполнения составило: 0.260 ms.

Еще одним плюсом явлется то, что время на выполнение SELECT rows_count('ТАБЛИЦА') всегда будет одинаковым при любых количествах записей в таблице.

Спасибо за внимание.

Автор: only-victor

Источник