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

Автор оригинала: Laurenz Albe


Часто жалуются, что 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 для этой таблицы, чтобы autoanalyze выполнялся там чаще.

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


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

Но иногда требуется знать, сколько строк вернет оператор 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.
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 35

    0

    Огромное спасибо за статью!


    Интересно, а насколько сложно сделать эстиматор из последнего примера надёжным?

      0
      Возможно наверно через права доступа (execute) самой функции
        +3
        Последний пример мне кажется слегка странным. Нет никакой проблемы добавить слово EXPLAIN к нужному запросу непосредственно в коде и там же получить нужный кусок плана.

        Кроме того все эти данные без костылей есть в системных таблицах, это уже 5 лет назад изложено в вики постгреса: wiki.postgresql.org/wiki/Count_estimate

        Вот то что вам нужно:
        SELECT reltuples::BIGINT AS estimate
        FROM pg_class
        WHERE oid = 'schema_name.table_name'::regclass;
          0

          Хм… И правда ларчик просто открывался. Спасибо!

        –3
        Емнип, в своё время в оракле лайфхак для разгона count-а был писать не select count(*), a select count(1). Что по нонешним временам — не знаю.
          –1
          Всё еще работает.
            0
            Ну, я бы сказал «странно», ибо уже могли бы допилить парсеры до понимания, что не надо весь dictionary тащить в клиента, если поля не указаны в каунте.

            Я-то, по старой привычке, пользуюсь, но не думал, что имеет смысл.
              0
              Ну так оракл же, они вообще неторопливые ребята.
              +3

              Неправда. Не было такого никогда и нет сейчас. Почитайте хотя бы Джонатана Льюиса: count(1) не лучше чем count(), более того оптимизатор заменяет count(1) на count()

              +1

              Нет, не было такого никогда. Count(*) уже сам по себе оптимален. Никакие count(1) улучшить его не могут, а вот ухудшить всякие вариации типа count(rowid) или count(-2) могут. Это были мифы а-ля Бурлесоновщина…

                0
                По памяти, оптимизация count(1) vs count(*) была в том, что во втором случае оракл лез в дата дикшнари, чтобы получить метаданные. В первом случае это не требуется. Повторюсь, мой опыт с этим — это 15 лет назад. Просто привык тогда писать count(1) так и пишу, не более.
                  +3
                  Да я знаю про этот миф. Я не знаю как и кто его породил, а главное — почему люди так наивно в это верят, ведь в оракле все легко проверяется, например трассировкой 10053.

                  Ссылки по теме:
                  jonathanlewis.wordpress.com/2008/10/31/count
                  jonathanlewis.wordpress.com/2020/02/25/count-again-2
                    0
                    Ok, спасибо.
                      0
                      О.

                      asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789#25335122556076
                        0
                        Аналогично поведению PostgreSQL. Для count(1) есть проверка на NULL, поэтому медленнее count(*)
                          0

                          Не аналогично. В оракле count(1) заменяется на звёздочку автоматически

                            0
                            Вот этого не знаю про замену. В примере по ссылке и другом примере count(*) чуть чуть быстрее count(1), хотя может быть это просто погрешность
                              +1

                              Дочитайте до конца — там шутка про скорость набора. Оптимизатор трансформирует запрос ещё до выполнения — прочитайте по моей ссылке у льюиса

                                –3
                                Проблема в том, что оракл — закрытое по и люди во всех этих обсуждениях строят догадки эмперически или по косвенным признакам типа сообщения:

                                10122, 00000, "Disable transformation of count(col) to count(*)"

                                Резюмируя — давайте пользоваться открытым по чтобы не строить догадки )
                                  +1

                                  Это не ошибка, а event, позволяющий отключить эту трансформацию. И оракл на порядок более документирован и имеет неимоверное количество средств же ага и траблшутинга. По возможностям оптимизатора, количеству трансформаций, гибкости и мощности движка, и бэкграунд сервисов он также всех опережает. Например, тот же full table scan или index fast full scan могут работать в параллели с direct path reads, в отличие от PostgreSql. У него, конечно же, есть свои огромные минусы(например, цена и выводящая из себя тех поддержка), но закрытость ПО — это несерьезный довод.

                                    0
                                    Я про то, что работа открытого по, в любом случае более предсказуема, так как есть возможность анализировать исходный код и точно сказать, как ведёт себя система и почему она себя так ведёт. Закрытое по — это по сути чёрный ящик, где максимум что есть — документация и эмпирические наблюдения
                                      +2

                                      Да что вы говорите… Более предсказуема? Кому? И давно вы анализировали код PostgreSql? Достаточен ли ваш уровень чтобы анализировать его код? А сколько вообще разработчиков, работающей с СУБД с достаточным уровнем c/c++? Как вы считаете, что быстрее и удобнее и правильнее: прочитать гайд, документацию или исходный код СУБД?


                                      Зы. Что-то своих статей не пишите с анализом кода pg, а только переводите чужие в которых нет ни одного куска кода...


                                      Зыы. Может уже остановитесь со своей маркетинговой чушью?

                                        –2
                                        Да, бывает нужно в исходном коде найти описание сообщения или ошибки и посмотреть, что ее вызвало и не нужно быть зубром в программировании на С, чтобы понимать исходный код

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

                                        п.с. я не понимаю, зачем вы стараетесь выгородить оракл, описывая его преимущества (маркетинговые), хотя речи об этом не было совсем. Может быть вы лично заинтересованы, не знаю
                                          +1
                                          Боюсь что при попытке сделать это реально, а не абстрактно-теоретически — окажется что в современном мире это не так уж и просто))))))))
                                            –1
                                            Буквально на днях это проделывал в исходниках postgres
                                              0
                                              Кстати не так уж и трудно. Я с ClickHouse только рак работаю, других вариантов просто нет.
                            0

                            :D это смотря какую IDE использовать :D во многих из них count(*) вставляется автокомплитом, а 1 придётся вручную вводить, так что звездочка рулит :D

                              0
                              Ну у меня это просто мышечная память, тут сложнее её заменить, с count(1) на *
                                0

                                Ну как я уже показывал по ссылке 1 заменяется на звёздочку в современных версиях оракл :) но в случае автоматических кодоанализаторов, да и просто, чтобы не давать такому мифу развиваться, я бы советовал использовать звёздочку

                    0
                    Статья вызвала эмоцию «Твою мать, почему я до сих пор этого не знал?». Правда я 1С-ник, но это не отменяет того, что для подсчёта количества строк нужно использовать в запросе конструкцию «ВЫБРАТЬ КОЛИЧЕСТВО(*) ИЗ Таблица», а не городить велосипеды, что я раньше делал. Причём это даже в справке написано.
                      0
                      Очень актуальна для постоянно распухающей БД Zabbix
                        0
                        Тут лучше удалять на регулярной основе старые записи, после чего чистить vacuumdb -z -f, сжимая файлы таблиц
                        0
                        До полноценной статьи не мешало бы добавить бенчмарки и графики. В текущем виде нет понимания, что в какой ситуации следует выбрать.

                        И в целом кодстайл так себе, процедуры счётчика лучше было разделить на две или три — позволит избежать интересных ошибок. Впрочем это перевод, так что претензии наверное не по адресу :-)
                          0
                          Вот здесь и бенчмарки и все остальное. И объяснение, что count(*) в Postgres на самом деле count(), а * появилась позже
                          0
                          Запись count (1) или count (id) на самом деле медленнее, чем count (*)

                          А если Id — это поле (или колонка) NOT NULL, по которому построен уникальный индекс?
                          Помнится, это по крайней мере в некоторых реляционных СУБД давало заметное ускорение.

                          Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                          Самое читаемое