Рассмотрим на примере таблицы с ~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,
reset bool default false )
RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE
rows_count bigint;
tablename_seq text;
BEGIN
tablename_seq := tablename || '_count_seq';
-- сброс счетчика
IF reset IS TRUE THEN
EXECUTE 'SELECT setval($1,count(*)) FROM '||tablename
USING tablename_seq
INTO rows_count;
-- вывод текущего значения счетчика
ELSE
EXECUTE 'SELECT last_value FROM '||tablename_seq
INTO rows_count;
END IF;
RETURN rows_count;
END;
$$;
3. Функция rows_count_update_trigger() — триггерная функция для автоматического изменения счетчика.
CREATE OR REPLACE FUNCTION rows_count_update_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
DECLARE
tablename_seq text;
BEGIN
tablename_seq := TG_TABLE_NAME || '_count_seq';
-- увеличение счетчика при INSERT
IF TG_OP = 'INSERT' THEN
EXECUTE 'SELECT nextval($1) FOR UPDATE'
USING tablename_seq;
RETURN NEW;
-- уменьшение счетчика при DELETE
ELSEIF TG_OP = 'DELETE' THEN
EXECUTE 'SELECT setval($1,nextval($1)-2) FOR UPDATE'
USING tablename_seq;
RETURN OLD;
-- сброс счетчика при TRUNCATE
ELSEIF TG_OP = 'TRUNCATE' THEN
EXECUTE 'SELECT setval($1,0) FOR UPDATE'
USING tablename_seq;
RETURN OLD;
END IF;
END;
$$;
4. Подключение тригерной функции к таблице.
AFTER INSERT OR DELETE ON users
FOR EACH ROW EXECUTE PROCEDURE rows_count_update_trigger();
CREATE CONSTRAINT TRIGGER rows_count_update_trigger
AFTER INSERT OR DELETE ON users INITIALLY DEFERRED
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',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('ТАБЛИЦА') всегда будет одинаковым при любых количествах записей в таблице.
Спасибо за внимание.
Комментарии (41)
smagen
28.01.2016 12:32+2В статье явно не хватает анализа оверхеда возникающего при изменениях в таблице. В частности, важно сказать, что такое решение напрочь убивает конкурентность. Есть и другие решения, см. linuxdatabases.info/blog/?p=99
only-victor
28.01.2016 13:08Решение которое вы предложили по ссылке очень похоже на мое, только счетчик храниться не в sequence, а в таблице, что снижает скорость работы при изменении счетчика. На сколько мне известно, postgresql при обновлении записи в таблице, записывает данные в новый файл, а старый помечает как удаленный, а при работе с sequence postgresql точно знает что знаение только одно, и не делает лишних записей.
smagen
28.01.2016 13:34Был не прав. Основной недостаток вашего решения – некорректная работа с транзакциями. См. пример.
# SELECT rows_count('users'); rows_count ------------ 0 (1 row) # SELECT count(*) FROM users; count ------- 0 (1 row) -- Пока всё верно # BEGIN; BEGIN # INSERT INTO users (name) VALUES ('user1'); INSERT 0 1 *# SELECT rows_count('users'); rows_count ------------ 1 (1 row) # SELECT rows_count('users'); rows_count ------------ 1 (1 row) # SELECT count(*) FROM users; count ------- 0 (1 row) -- В параллельной транзакции данные не сходятся *# ROLLBACK; ROLLBACK # SELECT rows_count('users'); rows_count ------------ 1 (1 row) # SELECT count(*) FROM users; count ------- 0 (1 row) -- После rollback'а данные не сходятся
По ссылке посмотрите внимательно. Первое решение – просто хранить COUNT в таблице. При этом конкурентности не будет, но всё будет корректно работать. Второе решение – хранить COUNT + дельты.only-victor
28.01.2016 13:39Да, спасибо что об этом сказали, я обязательно додумаю как это решить.
Об этом уже первым сказал neolink.
only-victor
28.01.2016 13:52Мне не нравиться решение хранить в таблице счетчик, потому что затратно его обновлять, тем боле операциями DELETE а потом INSERT. А нельзя ни как доработаь решение на sequence?
pavel_pimenov
28.01.2016 13:16А тестировали насколько ваше изобретение просадит производительность высоконагруженной OLTP базы?
only-victor
28.01.2016 13:19Тестировал на своем сервере с этим решением и без него. При тестировании было два потока, один постоянно добавлял записи, другой постоянно удалял. Нагрузка на ЦП оставалась такой же, особых изменений в нагруженности не было.
pavel_pimenov
28.01.2016 13:31+1А как вы определили что запрос select count(*) from t в продуктивной базе является критичным?
может правильнее найти того дятла, кто грузит сервер бестолковыми запросами и устранить его
only-victor
28.01.2016 13:33-2Статья о том как ускорить этот запрос, а не о том чтобы не использовать его.
pavel_pimenov
28.01.2016 13:54+3Если появилась идея ускорить такой запрос… значит, возникла необходимость частого его использования?
или вы увидели, что он где-то мешает нормальной работе?
Расскажите про свой юзкейс.
У меня есть одна версия:
Кто-то строит графики по базе, в который видно как растет размер таблиц по времени…
но обвешивать из-за этого все таблицы базы таким кодом 100% не эффективно.
Ivan22
28.01.2016 13:43+3все умные субд уже давно придумали место куда они складывают такие счетчика количества записей в таблицах.
В PG это системная таблица pg_class.
Попробуйте запрос:
SELECT nspname AS schemaname,relname,c.reltuples FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND relkind='r' ORDER BY reltuples DESC limit 100
Конечно, есть ньюанс!
В документации видим:
www.postgresql.org/docs/9.1/static/planner-stats.html
For efficiency reasons, reltuples and relpages are not updated on-the-fly, and so they usually contain somewhat out-of-date values. They are updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.
Но в целом, такая статистика — в 90% случаев прекрасно подойдет, для большинства целей.only-victor
28.01.2016 13:47Это решение вообще не решение, потому что reltuples обновляется только после ANALYZE таблицы, а не после INSERT, DELETE
Нужна не статистика, а точное количество записей в таблице.pavel_pimenov
28.01.2016 14:06+1А кому и зачем нужно точное значение и как часто он требуется?
ведь после выполнения запроса оно в общем случае сразу становится не точным…
only-victor
28.01.2016 14:19К примеру для pagination (1,2,3… max) записей нужно знать точное количество записей.
Borz
28.01.2016 15:09+4в таких случаях обычно рядом с «SELECT COUNT(*)» ещё и WHERE пробегает. Как в вашем решении это учитывается?
only-victor
28.01.2016 15:14-4в таких случаях обычно рядом с «SELECT COUNT(*)» ещё и WHERE пробегает.
Как раз нооборот не нужен там WHERE.
Как в вашем решении это учитывается?
Это решение только для COUNT(*), как указано в заголовке.only-victor
28.01.2016 15:19Как раз нооборот не нужен там WHERE.
был не прав, обячно хотя бы active=true, но для админ панели без where
Ivan22
28.01.2016 14:33+1Нужна не статистика, а точное количество записей в таблице.
если дейтсвительно точное. Я бы триггерам не доверял.
p.s. Ничего личного, просто многолетний опыт. «не доверяй ТЗ, начальству и триггерам»only-victor
28.01.2016 14:48Счетчик можно периодически сбрасывать раз в день, но выигрышь в скорости значительный без особых затрат.
Почему бы не воспользоваться, тем более для таблиц с миллионами записей, без решения этой проблемы SELECT COUNT(*) может занимать до 1 секунды, потому что проверяет реальное существование каждой записи в таблице.
only-victor
28.01.2016 15:55+1Если не доверять триггерам, то чем тогда вообще можно доверять в БД!?
Ivan22
29.01.2016 16:01+1решениям которые проверены временем.
Логика на тригеррах мною проверена. Не работает.
Но это имхо, чего уж там
only-victor
28.01.2016 14:45Это решение подходит только для тех таблиц, которые не учавствуют в транзакциях на INSERT, DELETE
К сожалению PostgreSQL не поддерживает транзакции для SEQUENCE.
А в остальном, оно быстрее работает и без лишних таблиц.
only-victor
28.01.2016 14:57Так же можно счетчик сбрасывать через cron периодически, для точной коррекции, если вдруг транзакции были отменены.
romy4
28.01.2016 15:14В целом, если пошерстить интернеты, то есть несколько наводок на решение с транзакциями. 1. Триггер для констрейн ключей. 2. сама реализация
only-victor
28.01.2016 15:34Точно!
Попробовал переписать триггер вот так:
CREATE CONSTRAINT TRIGGER count_rows_update_trigger AFTER INSERT OR DELETE ON users INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE count_rows_update_trigger();
и все заработало! СПАСИБО!!!
*=> begin; BEGIN *=> select count_rows('users'); count_rows ------------ 345403 (1 строка) *=> delete from users where id=310000; DELETE 1 *=> select count_rows('users'); count_rows ------------ 345403 (1 строка) *=> commit; COMMIT *=> select count_rows('users'); count_rows ------------ 345402 (1 строка)
Получается что вопрос с транзакиями решился.smagen
28.01.2016 16:34Обновление sequence'а в конце, конечно, лучше. Но проблемы всё равно остаются:
# Транзакции в режимах repeatable read и serializable будут видеть неправильные значения.
# Если транзакция, которая вставляет или удаляет значения сделает «SET CONSTRAINTS ALL IMMEDIATE;», то последовательность будет обновлена сразу.
# Если постгрес упадёт в процессе коммита транзакции, то в последовательности может остаться неправильное значение.
# Deferred constraint'ы проверяются, если я не ошибаюсь, в негарантированном порядке. Если посте вашего constraint'а будет проверен, например, какой-нибудь FK, который свалится, то в последовательности останется неправильное значение.only-victor
28.01.2016 17:04Возможно есть какие то уникальные случаи в которых в sequence окажеться не верное значение, но это редкость и в этом случае можно учесть факт наличия такой работы с count(*). И в конце концов, можно пеиодически сбрасывать sequence например раз в день.
Просто выигрыш во времени исполнения этого запроса каласальный, особенно для таблиц с миллионами записей.
only-victor
28.01.2016 15:47Еще раз большое спасибо romy4 за то, что подсказал как решить вопрос с транзакциями для sequence.
Значит предлагаемое мной решение работает корректно даже с транзакциями.
only-victor
29.01.2016 21:57Протестировал это решение, счетчик работает корректно.
Условия тестирования:
— 2 потока на постоянное добавление/удаление в случайном порядке
— количество добавляемых/удаляемых записей 10 000
— при добавлении: всегда начинается транзакция с LOCK TABLE IN EXCLUSIVE MODE (разрешено только чтение из таблицы)
— при удалении: транзакция начинается/нет в случайном порядке без блокировок
— при добавлении или удалении: если транзакция началась, то commit/rollback в случайном порядке
Запускал 10 раз тест, результат счетчик имеет такое же значение как актуальное количество записей в таблице.
neolink
А зачем вам count без условий?
Ну и стоит упомянуть, что при rollback счетчик сбивается, да и в целом он будет показывать значения чуть опережая события
only-victor
Про rollback согласен, тут нужно додумать.
По поводу опережения не понял, почему? Триггеры запускаются после совершения операции:
AFTER INSERT OR DELETE
AFTER TRUNCATE
neolink
последовательности не имеют изоляции и по сути ещё не закомиченная новая строчка уже учтена в счетчике, хотя она всё ещё не видна другим транзакциям
only-victor
Вопрос с транзакциями решен ниже в комментах.