Ускорение запроса 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(‘ТАБЛИЦА’) всегда будет одинаковым при любых количествах записей в таблице.

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

ссылка на оригинал статьи https://habrahabr.ru/post/276055/