Прискорення запиту 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 users ON
FOR EACH ROW EXECUTE PROCEDURE rows_count_update_trigger();

CREATE CONSTRAINT TRIGGER rows_count_update_trigger
AFTER INSERT OR DELETE users ON INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE rows_count_update_trigger();
CREATE TRIGGER rows_count_reset_trigger
AFTER TRUNCATE users ON
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('ТАБЛИЦЯ') завжди буде однаковим при будь-яких кількостях записів у таблиці.

Дякую за увагу.

Джерело: Хабрахабр
  • avatar
  • 0

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.