Как ускорить select count postgresql
Перейти к содержимому

Как ускорить select count postgresql

  • автор:

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

Часто жалуются, что 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.

  • postgresql performance
  • postgresql

Как заставить PostgreSQL считать быстрее

Все умеют считать, но не все умеют считать быстро. В этой статье мы подробно рассмотрим методы оптимизации count в PostgreSQL. Существуют приемы, которые могут позволить ускорить подсчет количества строк на порядки.

Если подходить к вопросу со всей серьезностью, необходимо выделить несколько вариантов count, у каждого из которых есть собственные методы. С чем нужно будет определиться:

  • требуется ли точное количество строк или оценочного значения будет достаточно;
  • следует ли учитывать дубликаты или интересуют только уникальные значения;
  • нужно ли посчитать все строки таблицы или необходимо выбрать только удовлетворяющие определенному условию.

Мы проанализируем решения для каждой конкретной ситуации, а также сравним их скорость и потребление ресурсов. Разобрав ситуацию с централизованной БД, мы воспользуемся Citus, чтобы продемонстрировать параллельное выполнение count в распределенной базе данных.

Содержание

  • Подготовка БД
  • Count с дубликатами
    • Точный подсчет
    • Оценка
      • Оценка по всей таблице
      • Оценка для выборки
      • Точный подсчет
        • Поведение по умолчанию при нехватке памяти
        • Специализированное агрегирование
        • HashAggregate
        • Index-Only Scan
        • HyperLogLog
        • Настройка кластера
        • Точный подсчет
          • С дубликатами
          • Distinct (без дубликатов)
          Подготовка БД

          Для тестов мы будем использовать базу данных под названием count, для которой инициализирован pgbench:

          [user@comp ~]$ pgbench -i count

          Создадим тестовую таблицу:

          -- создаем миллион случайных чисел и строк CREATE TABLE items AS SELECT (random()*1000000)::integer AS n, md5(random()::text) AS s FROM generate_series(1,1000000); -- информируем планировщик об изменении размера большой таблицы VACUUM ANALYZE;
          Count с дубликатами
          Точный подсчет

          Итак, начнем с начала: рассмотрим получение точного количества строк всей таблицы или ее части с дубликатами — старый добрый count(*) . Время выполнения этой команды даст нам базис для оценки скорости работы других методов подсчета количества строк.

          Pgbench — удобный инструмент для многократного выполнения запроса и сбора статистики о производительности.

          # Все примеры выполнялись на PostgreSQL 9.5.4 echo "SELECT count(*) FROM items;" | pgbench -d count -t 50 -P 1 -f - # average 84.915 ms # stddev 5.251 ms

          Замечание по поводу count(1) vs count(*) . Можно подумать, что count(1) быстрее, поскольку count(*) должен обработать значения всех колонок текущей строки. На самом деле верно обратное. В отличие от конструкции SELECT * , символ «звездочка» в count(*) ничего не означает. PostgreSQL обрабатывает выражение count(*) как особый случай count без аргументов. (Правильно было бы записывать это выражение в виде count() ). С другой стороны, count(1) принимает один аргумент, и PostgreSQL должна для каждой строки убедиться, что этот аргумент (1) и вправду не является NULL.

          Предыдущий тест с count(1) выдал следующие результаты:

          # average 98.896 ms # stddev 7.280 ms

          В любом случае и count(1) , и count(*) по определению медленные. Чтобы обеспечить непротиворечивость одновременно выполняющихся транзакций, PostgreSQL использует управление параллельным доступом с помощью мультиверсионности (MVCC, Multiversion Concurrency Control). Это значит, что каждая транзакция может видеть разные строки и даже разное количество строк таблицы. Поэтому не существует единственно правильного значения количества строк, которое СУБД могла бы поместить в кеш, и системе придется просканировать все строки, чтобы подсчитать, какие из них видны отдельной транзакции. Время выполнения точного (exact) count линейно растет вслед за увеличением размера таблицы.

          EXPLAIN SELECT count(*) FROM items; Aggregate (cost=20834.00..20834.01 rows=1 width=0) -> Seq Scan on items (cost=0.00..18334.00 rows=1000000 width=0)

          На scan приходится 88% стоимости запроса. Если удвоить размер таблицы, то и время выполнения запроса увеличится примерно в два раза при пропорциональном росте стоимости scan и aggregate.

          Количество строк Среднее время
          1 млн 85 мс
          2 млн 161 мс
          4 млн 343 мс

          Как это ускорить? Есть два варианта: решить, что нам достаточно оценочного значения, либо самостоятельно помещать в кеш количество строк. Во втором случае нам придется отдельно хранить значения для каждой таблицы и каждого выражения WHERE, для которых мы хотим быстро выполнять count.

          Давайте рассмотрим пример ручного кеширования значения count(*) для таблицы items целиком. Следующее основанное на триггерах решение является адаптацией метода, предложенного A. Elein Mustain. Механизм MVCC PostgreSQL будет поддерживать согласованность между items и таблицей, содержащей значения количества строк.

          BEGIN; CREATE TABLE row_counts ( relname text PRIMARY KEY, reltuples bigint ); -- проинициализируем таблицу текущим значением количества строк INSERT INTO row_counts (relname, reltuples) VALUES ('items', (SELECT count(*) from items)); CREATE OR REPLACE FUNCTION adjust_count() RETURNS TRIGGER AS $$ DECLARE BEGIN IF TG_OP = 'INSERT' THEN EXECUTE 'UPDATE row_counts set reltuples=reltuples +1 where relname = ''' || TG_RELNAME || ''''; RETURN NEW; ELSIF TG_OP = 'DELETE' THEN EXECUTE 'UPDATE row_counts set reltuples=reltuples -1 where relname = ''' || TG_RELNAME || ''''; RETURN OLD; END IF; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER items_count BEFORE INSERT OR DELETE ON items FOR EACH ROW EXECUTE PROCEDURE adjust_count(); COMMIT;

          Скорость чтения и обновления кешированных значений в этом случае не зависит от размера таблицы, и получение значения количества строк выполняется очень быстро. Однако эта техника увеличивает накладные расходы на операции вставки и удаления. Без триггера следующая команда выполняется 4,7 секунды, когда как вставка с триггером в пятьдесят раз медленнее:

          INSERT INTO items (n, s) SELECT (random()*1000000)::integer AS n, md5(random()::text) AS s FROM generate_series(1,1000000);
          Оценка
          Оценка по всей таблице

          Подход, при котором мы кешируем количество строк таблицы, замедляет операции вставки. Если вместо точного числа мы готовы довольствоваться оценочным значением, то есть возможность получить быстрые операции чтения без ухудшения времени работы вставки. Для этого мы можем использовать собираемые PostgreSQL служебные данные. Их источниками являются stats collector и autovacuum daemon.

          Варианты получения оценочных значений:

          -- Спрашиваем у "stats collector" SELECT n_live_tup FROM pg_stat_all_tables WHERE relname = 'items'; -- Обновлено VACUUM и ANALYZE SELECT reltuples FROM pg_class WHERE relname = 'items';

          Но есть и более надежный источник, данные в котором обновляются чаще. Andrew Gierth (RhodiumToad) советует:

          Помните: планировщик на самом деле не использует reltuples; он умножает отношение reltuples/relpages на текущее количество страниц.

          Логика здесь следующая: по мере увеличения объема данных в таблице среднее количество строк, умещающихся в физическую страницу, в общем случае будет меняться не так сильно, как их общее количество. Чтобы получить более точную оценку текущего количества строк, мы можем умножить среднее количество строк на актуальную информацию о текущем количестве занимаемых таблицей страниц.

          -- pg_relation_size и block_size содержат свежую информацию, -- поэтому их можно использовать вместе с оценкой -- среднего количества строк на страницу SELECT (reltuples/relpages) * ( pg_relation_size('items') / (current_setting('block_size')::integer) ) FROM pg_class where relname = 'items';
          Оценка для выборки

          В предыдущем разделе мы рассмотрели получение оценочного количества строк для целой таблицы, а можно ли сделать то же самое, но только для подходящих под условие WHERE строк? Michael Fuhr придумал интересный способ: запустить EXPLAIN для запроса и проанализировать полученный результат.

          CREATE FUNCTION count_estimate(query text) RETURNS integer AS $$ DECLARE rec record; rows integer; BEGIN FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)'); EXIT WHEN rows IS NOT NULL; END LOOP; RETURN rows; END; $$ LANGUAGE plpgsql VOLATILE STRICT;

          Эту функцию можно использовать следующим образом:

          SELECT count_estimate('SELECT 1 FROM items WHERE n < 1000');

          Точность этого метода зависит от планировщика, который использует различные способы для оценки избирательности выражения WHERE , и от того, откуда может быть получено количество возвращаемых запросом строк.

          Distinct сount (без дубликатов)
          Точный подсчет
          Поведение по умолчанию при нехватке памяти

          Count с дубликатами может выполняться медленно, но count distinct намного хуже. С ограниченной рабочей памятью и без индексов PostgreSQL не способна выполнять оптимизацию эффективно. В конфигурации по умолчанию СУБД накладывает жесткий лимит на каждый параллельный запрос ( work_mem ). На компьютере, который я использую для разработки, это значение по умолчанию было установлено в 4 мегабайта.

          Давайте оценим производительность работы с миллионом строк на заводских настройках work_mem .

          echo "SELECT count(DISTINCT n) FROM items;" | pgbench -d count -t 50 -P 1 -f - # average 742.855 ms # stddev 21.907 ms echo "SELECT count(DISTINCT s) FROM items;" | pgbench -d count -t 5 -P 1 -f - # average 31747.337 ms # stddev 267.183 ms

          Запуск EXPLAIN показывает, что большая часть времени выполнения запроса ушла на агрегирование. Также отметим, что подсчет количества строк по колонке типа text ведется намного медленнее, чем по целочисленной:

          -- план для колонки типа "integer", n Aggregate (cost=20834.00..20834.01 rows=1 width=4) (actual time=860.620..860.620 rows=1 loops=1) Output: count(DISTINCT n) Buffers: shared hit=3904 read=4430, temp read=1467 written=1467 -> Seq Scan on public.items (cost=0.00..18334.00 rows=1000000 width=4) (actual time=0.005..107.702 rows=1000000 loops=1) Output: n, s Buffers: shared hit=3904 read=4430 -- план для колонки типа "text", s Aggregate (cost=20834.00..20834.01 rows=1 width=33) (actual time=31172.340..31172.340 rows=1 loops=1) Output: count(DISTINCT s) Buffers: shared hit=3936 read=4398, temp read=5111 written=5111 -> Seq Scan on public.items (cost=0.00..18334.00 rows=1000000 width=33) (actual time=0.005..142.276 rows=1000000 loops=1) Output: n, s Buffers: shared hit=3936 read=4398

          Что же происходит внутри "aggregate"? Описание этой процедуры в выводе EXPLAIN непрозрачно. Разобраться в ситуации помогает анализ похожего запроса. Заменим count distinct на select distinct .

          EXPLAIN (ANALYZE, VERBOSE) SELECT DISTINCT n FROM items; Unique (cost=131666.34..136666.34 rows=498824 width=4) (actual time=766.775..1229.040 rows=631846 loops=1) Output: n -> Sort (cost=131666.34..134166.34 rows=1000000 width=4) (actual time=766.774..1075.712 rows=1000000 loops=1) Output: n Sort Key: items.n Sort Method: external merge Disk: 13632kB -> Seq Scan on public.items (cost=0.00..18334.00 rows=1000000 width=4) (actual time=0.006..178.153 rows=1000000 loops=1) Output: n

          В условиях недостаточного значения work_mem и отсутствия внешних структур данных (например, индексов) PostgreSQL выполняет merge-sort таблицы между памятью и диском, а затем пробегает по результату, удаляя дубликаты, то есть действуя во многом аналогично классической Unix-комбинации sort | uniq .

          Большую часть времени выполнения запроса занимает сортировка, особенно когда мы используем не целочисленную колонку n , а строковую s . Удаление дубликатов (unique filter) в обоих случаях выполняется примерно с одинаковой скоростью.

          Специализированное агрегирование

          Для подсчета количества уникальных значений Thomas Vondra создал специализированный метод агрегирования, работающий с типами ограниченной длины (не должна превышать 64 бита). Этот способ даже без увеличения рабочей памяти или создания индексов оказывается быстрее используемого по умолчанию метода, основанного на сортировке. Для установки выполните следующие шаги:

          1. Создайте копию проекта tvondra/count_distinct.
          2. Переключитесь на стабильную ветку: git checkout REL2_0_STABLE .
          3. Запустите make install .
          4. В вашей базе данных выполните: CREATE EXTENSION. count_distinct; .

          В этой статье Томас объясняет, как работает агрегирование. Коротко скажу лишь, что его метод создает в памяти отсортированный массив уникальных элементов, уплотняя его в процессе.

          echo "SELECT COUNT_DISTINCT(n) FROM items;" | pgbench -d count -t 50 -P 1 -f - # average 434.726 ms # stddev 19.955 ms

          Это работает быстрее стандартного count distinct, который на наших тестовых данных выполняется в среднем 742 мс. Следует учитывать, что написанные на C расширения, такие как count_distinct, не ограничены параметром work_mem, поэтому созданный в процессе массив может занять больше памяти в расчете на соединение, чем вы изначально планировали.

          HashAggregate

          Если все пересчитываемые столбцы умещаются в work_mem, для получения уникальных значений PostgreSQL применит хеш-таблицу:

          SET work_mem='1GB'; EXPLAIN SELECT DISTINCT n FROM items; HashAggregate (cost=20834.00..25822.24 rows=498824 width=4) Group Key: n -> Seq Scan on items (cost=0.00..18334.00 rows=1000000 width=4)

          Это самый быстрый из рассмотренных нами методов. Он выполняется в среднем 372 мс для n и 23 секунды для s . Запросы select distinct n и select count(distinct n) будут работать примерно одинаковое количество времени при условии, что агрегирование count distinct также применит HashAggregate.

          Будьте осторожны: установка высокого лимита рабочей памяти может привести к неприятным последствиям, так как work_mem относится к каждому параллельному запросу. Кроме того, мы можем придумать и кое-что получше.

          Index-Only Scan

          Эта возможность появилась в PostgreSQL 9.2. Если индекс содержит все необходимые для запроса данные, система может использовать только его, не трогая саму таблицу (“the heap”). Тип индекса должен поддерживать index-only scan (например, btree). Индексы GiST и SP-GiST поддерживают index-only scan лишь для некоторых классов операторов.

          Создадим по btree-индексу для колонок n и s :

          CREATE INDEX items_n_idx ON items USING btree (n); CREATE INDEX items_s_idx ON items USING btree (s);

          Для выбора уникальных значений из этих колонок теперь используется другая стратегия:

          EXPLAIN SELECT DISTINCT n FROM items; Unique (cost=0.42..28480.42 rows=491891 width=4) -> Index Only Scan using items_n_idx on items (cost=0.42..25980.42 rows=1000000 width=4)

          Но тут мы наталкиваемся на странную проблему: SELECT COUNT(DISTINCT n) FROM items не станет использовать индекс, несмотря на то что SELECT DISTINCT n делает это по умолчанию. Следуя советам в блогах («Трюк, который ускорит ваш postgres в 50x раз!»), можно дать подсказку планировщику, переписав count distinct в виде count по подзапросу:

          -- SELECT COUNT(DISTINCT n) FROM items; -- должен быть переписан в виде EXPLAIN SELECT COUNT(*) FROM (SELECT DISTINCT n FROM items) t; Aggregate (cost=34629.06..34629.07 rows=1 width=0) -> Unique (cost=0.42..28480.42 rows=491891 width=4) -> Index Only Scan using items_n_idx on items (cost=0.42..25980.42 rows=1000000 width=4)

          Симметричный (in-order) обход бинарного дерева выполняется быстро. Этот запрос занимает в среднем 177 мс (270 мс для колонки s ).

          Замечание. Если значение work_mem достаточно для размещения таблицы целиком, PostgreSQL даже при наличии индекса выберет HashAggregate. Получается парадокс: выделение системе большего количества памяти может привести к выбору худшего плана запроса. Есть возможность форсировать выбор index-only scan, установив SET enable_hashagg=false; , только не забудьте вернуть его обратно в true, чтобы не испортить планы других запросов.

          Оценка
          HyperLogLog

          Рассмотренные ранее методы зависят от индексов, хеш-таблиц, отсортированных массивов в памяти либо обращаются к статистическим таблицам централизованной базы данных. Когда данных становится по-настоящему много и/или они разделяются между несколькими узлами распределенной базы данных, эти методы перестают нас устраивать.

          В этом случае на помощь приходят вероятностные структуры данных, которые способны давать быстрые приблизительные оценки и хорошо распараллеливаются. Давайте испытаем одну из таких структур на count distinct. Рассмотрим механизм оценки количества элементов (cardinality estimator) под названием HyperLogLog (HLL). Для представления набора элементов он использует небольшое количество памяти. Операция объединения в этом механизме работает без потерь, что позволяет объединять произвольные значения HLL, не теряя точности оценки количества.

          HLL использует свойства «хороших» хеш-функций, в частности дистанцию между хешированными значениями. Функция, равномерно распределяющая значения, стремится разносить их на максимально возможное расстояние. По мере добавления новых хешей свободного места становится меньше, и элементы начинают прижиматься друг к другу. Анализируя наименьшие расстояния между хешированными значениями, алгоритм может оценить наиболее вероятное количество исходных элементов.

          Давайте измерим скорость. Сначала установим расширение для PostgreSQL.

          1. Создайте копию проекта postgresql-hll.
          2. Запустите make install .
          3. Создайте расширение hll в вашей базе данных: CREATE EXTENSION hll; .

          HLL выполняет быстрое агрегирование данных при последовательном сканировании таблиц (sequential scan):

          EXPLAIN SELECT #hll_add_agg(hll_hash_integer(n)) FROM items; Aggregate (cost=23334.00..23334.01 rows=1 width=4) -> Seq Scan on items (cost=0.00..18334.00 rows=1000000 width=4)

          Средняя скорость HLL при выполнении count distinct составила 239 мс по колонке n и 284 мс по s . Получилось немного медленнее, чем index-only scan на одном миллионе записей. Настоящая же сила HLL проявляется за счет ее ассоциативных и коммутативных операций объединения, которые проходят без потерь. Это значит, что они могут выполняться параллельно и быть объединены для вычисления итогового результата.

          Распараллеливание

          Приложения, собирающие аналитику в реальном времени, такие как, например, Google Analytics, активно используют count, и эта операция хорошо распараллеливается. В этом разделе мы измерим производительность нескольких методов подсчета количества строк на базе небольшого кластера Citus, развернутого в Citus Cloud.

          Идея в том, чтобы развернуть узлы распределенной базы данных на нескольких машинах. На узлах будет одинаковая схема, и каждый из них будет содержать часть общего набора данных (shard). Подсчет количества строк будет выполняться параллельно, т. е. одновременно на разных машинах.

          Настройка кластера

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

          В Citus Cloud я сделал кластер из восьми машин, выбрав для каждой из них самую слабую из возможных конфигураций. При желании воспроизвести этот пример самостоятельно зарегистрируйтесь вот здесь.

          После создания кластера я подключаюсь к координирующей ноде для выполнения SQL-запросов. Первым делом создадим таблицу.

          CREATE TABLE items ( n integer, s text );

          В данный момент таблица существует только в БД координатора. Нам нужно разбить таблицу и разместить ее части на рабочих узлах. Citus приписывает каждую строку к определенному сегменту (shard) путем обработки значений в выбранной нами колонке для распределения (distribution column). В примере ниже мы ставим ему задачу распределить будущие строки в таблице items, используя хеши значений в колонке n для определения принадлежности к тому или иному сегменту.

          SELECT master_create_distributed_table('items', 'n', 'hash'); SELECT master_create_worker_shards('items', 32, 1);

          С помощью координирующей ноды мы загрузим случайные данные в сегменты БД. (Citus также поддерживает MX, режим "без мастера" (masterless mode), который используется для быстрой загрузки данных, но сейчас он нас не интересует).

          После получения URL базы данных координатора кластера выполните следующий код на компьютере с быстрым сетевым соединением. (Все генерируемые данные будут передаваться по сети с этой машины, поэтому нужна хорошая скорость.)

          cat randgen.sql COPY ( SELECT (random()*100000000)::integer AS n, md5(random()::text) AS s FROM generate_series(1,100000000) ) TO STDOUT; EOF psql $CITUS_URL -q -f randgen.sql | \ psql $CITUS_URL -c "COPY items (n, s) FROM STDIN"

          В примере с централизованной базой данных мы использовали миллион строк. На этот раз давайте возьмем сто миллионов.

          Точный подсчет
          С дубликатами

          Обычный count (без дубликатов) проблем не доставляет. Координатор выполняет запрос на всех узлах, а затем суммирует результаты. Вывод EXPLAIN показывает план, выбранный на одном из рабочих узлов (“Distributed Query”), и план, выбранный на координаторе (“Master Query”).

          EXPLAIN VERBOSE SELECT count(*) FROM items; Distributed Query into pg_merge_job_0003 Executor: Real-Time Task Count: 32 Tasks Shown: One of 32 -> Task Node: host=*** port=5432 dbname=citus -> Aggregate (cost=65159.34..65159.35 rows=1 width=0) Output: count(*) -> Seq Scan on public.items_102009 items (cost=0.00..57340.27 rows=3127627 width=0) Output: n, s Master Query -> Aggregate (cost=0.00..0.02 rows=1 width=0) Output: (sum(intermediate_column_3_0))::bigint -> Seq Scan on pg_temp_2.pg_merge_job_0003 (cost=0.00..0.00 rows=0 width=0) Output: intermediate_column_3_0

          Для справки: на нашем кластере этот запрос выполняется 1,2 секунды. Distinct count представляет более серьезную проблему при работе с распределенной БД.

          Distinct (без дубликатов)

          Трудность в подсчете уникальных значений колонки в распределенной базе данных заключается в том, что дубликаты надо искать на разных узлах. Однако это проблема, если считать значения в колонке для распределения (distribution column). Строки с одинаковыми значениями в этой колонке попадут в один сегмент, что позволит избежать межсегментного дублирования.

          Citus знает, что для подсчета уникальных значений в колонке распределения (distribution column) нужно выполнить запрос count distinct на каждом узле и сложить результаты. Наш кластер выполняет эту задачу за 3,4 секунды.

          Найти количество уникальных значений в обычной колонке (non-distribution) оказывается более сложной задачей. Логически можно выделить две возможности:

          1. Скопировать все строки на координирующий узел и посчитать там.
          2. Перераспределить строки между рабочими узлами, не допуская попадания одинаковых значений в разные сегменты, и после этого выполнять подсчет уникальных значений так же, как и для колонки, по которой ведется распределение.

          Первый вариант ничем не лучше подсчета в централизованной БД. Фактически это в точности то же самое плюс значительные накладные расходы на передачу данных по сети.

          Второй вариант называется «перераспределение» (repartitioning). Идея заключается в том, чтобы, используя колонку для распределения, создать на рабочих узлах временные таблицы. Рабочие узлы посылают друг другу строки для записи во временные таблицы, выполняют запрос и удаляют эти таблицы. У каждой СУБД своя логика реализации перераспределения. Детали этой системы в Citus выходят за рамки данной статьи, поэтому мы не будем в них углубляться.

          Оценка без дубликатов

          Механизмы оценки мощности, такие как HLL, незаменимы в распределенных базах данных. Они позволяют выполнять подсчет уникальных значений даже по обычным колонкам (non-distribution), создавая при этом лишь небольшую дополнительную нагрузку на сеть. Тип данных HLL имеет небольшой размер в байтах и может быстро пересылаться от рабочих узлов координатору. Поскольку операция объединения в HLL выполняется без потерь, нет необходимости волноваться, что количество сегментов базы может повлиять на точность.

          В Citus нет необходимости явно вызывать функции postgresql-hll. Просто установите citus.count_distinct_error_rate в ненулевое значение, и Citus перепишет планы запросов для count distinct с использованием HLL. Например:

          SET citus.count_distinct_error_rate = 0.005; EXPLAIN VERBOSE SELECT count(DISTINCT n) FROM items; Distributed Query into pg_merge_job_0090 Executor: Real-Time Task Count: 32 Tasks Shown: One of 32 -> Task Node: host=*** port=5432 dbname=citus -> Aggregate (cost=72978.41..72978.42 rows=1 width=4) Output: hll_add_agg(hll_hash_integer(n, 0), 15) -> Seq Scan on public.items_102009 items (cost=0.00..57340.27 rows=3127627 width=4) Output: n, s Master Query -> Aggregate (cost=0.00..0.02 rows=1 width=0) Output: (hll_cardinality(hll_union_agg(intermediate_column_90_0)))::bigint -> Seq Scan on pg_temp_2.pg_merge_job_0090 (cost=0.00..0.00 rows=0 width=0) Output: intermediate_column_90_0

          Запрос отработал быстро: 3,2 секунды для колонки n и 3,8 секунды для строковой s . И это на 100 миллионах записей и обычных (non-distribution) колонках! HLL — отличный инструмент для горизонтального масштабирования процедуры подсчета уникальных значений в распределенной базе данных.

          Итоги

          Метод Время/1 млн строк Точные Фильтруемые Уникальные
          PG Stats 0,3 мс
          Парсинг EXPLAIN 0,3 мс +
          Ручное кеширование 2 мс (но медленная вставка) +
          count(*) 85 мс + +
          count(1) 99 мс + +
          Index Only Scan 177 мс + + +
          HLL 239 мс + +
          HashAgg 372 мс + + +
          Custom Agg 435 мс (колонка 64-bit) + + +
          Mergesort 742 мс + + +

          Немного проигрывая index-only scan в централизованной базе данных на таблице с миллионом строк, HyperLogLog (HLL) вырывается вперед на больших объемах данных (> 100 Гб). Этот метод также незаменим в распределенных БД, позволяя получить оценку количества уникальных элементов (distinct count) в реальном времени на огромных объемах данных.

          PostgreSQL COUNT Function

          Summary: in this tutorial, you will learn how to use the PostgreSQL COUNT() function to count the number of rows in a table.

          PostgreSQL COUNT() function overview

          The COUNT() function is an aggregate function that allows you to get the number of rows that match a specific condition of a query.

          The following statement illustrates various ways of using the COUNT() function.

          COUNT(*)

          The COUNT(*) function returns the number of rows returned by a SELECT statement, including NULL and duplicates.

          SELECT COUNT(*) FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

          When you apply the COUNT(*) function to the entire table, PostgreSQL has to scan the whole table sequentially.

          If you use the COUNT(*) function on a big table, the query will be slow. This is related to the PostgreSQL MVCC implementation. Because multiple transactions see different states of data at the same time, there is no direct way for COUNT(*) function to count across the whole table, therefore PostgreSQL must scan all rows.

          COUNT(column)

          Similar to the COUNT(*) function, the COUNT(column) function returns the number of rows returned by a SELECT clause. However, it does not consider NULL values in the column .

          SELECT COUNT(column) FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

          COUNT(DISTINCT column)

          In this form, the COUNT(DISTINCT column) returns the number of unique non-null values in the column.

          SELECT COUNT(DISTINCT column) FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

          We often use the COUNT() function with the GROUP BY clause to return the number of items for each group. For example, we can use the COUNT() with the GROUP BY clause to return the number of films in each film category.

          PostgreSQL COUNT() function examples

          Let’s use the payment table in the sample database for the demonstration.

          1) PostgreSQL COUNT(*) example

          The following statement uses the COUNT(*) function to return the number of transactions in the payment table:

          SELECT COUNT(*) FROM payment;Code language: SQL (Structured Query Language) (sql)

          Here is the output:

          2) PostgreSQL COUNT(DISTINCT column) example

          To get the distinct amounts which customers paid, you use the COUNT(DISTINCT amount) function as shown in the following example:

          SELECT COUNT (DISTINCT amount) FROM payment;Code language: SQL (Structured Query Language) (sql)

          PostgreSQL COUNT() with GROUP BY clause

          To get the number of payments by the customer, you use the GROUP BY clause to group the payments into groups based on customer id, and use the COUNT() function to count the payments for each group.

          The following query illustrates the idea:

          SELECT customer_id, COUNT (customer_id) FROM payment GROUP BY customer_id;Code language: SQL (Structured Query Language) (sql)

          Here is the partial output:

          postgresql count with group by

          PostgreSQL COUNT() with HAVING clause

          You can use the COUNT function in a HAVING clause to apply a specific condition to groups. For example, the following statement finds customers who have made more than 40 payments:

          SELECT customer_id, COUNT (customer_id) FROM payment GROUP BY customer_id HAVING COUNT (customer_id) > 40;Code language: SQL (Structured Query Language) (sql)

          postgresql count with having

          In this tutorial, you have learned how to use the PostgreSQL COUNT() function to return the number of rows in a table.

          SQL-Ex blog

          Как сделать запросы SELECT COUNT(*) очень быстрыми

          Добавил smois on Суббота, 11 апреля. 2020

          Когда вы выполняете SELECT COUNT(*), скорость результатов во многом зависит от структуры и настроек базы данных. Давайте проведем исследование на таблице Votes в базе данных Stack Overflow - 300-гигабайтной версии 2018-06, в которой таблица Votes содержит 150784380 строк и занимает пространство около 5,3Гб.

          • Как много страниц он читает (с установкой SET STATISTICS IO ON).
          • Как много времени процессора он использует (с установкой SET STATISTICS TIME ON).
          • Как быстро выполняется.

          Я выполняю эти тесты на SQL Server 2019 (15.0.2070.41) на 8-ядерной виртуальной машине с 64Гб RAM.

          1: Обычный COUNT (*) только с кластеризованным индексом построчного хранения и уровнем совместимости 2017 и ранее

          ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; 
          GO
          SELECT COUNT(*) FROM dbo.Votes;
          GO
          • Чтение страниц: 694389
          • CPU: 14,5 секунд времени процессора
          • Продолжительность: 2 секунды

          2: Уровень совместимости 2019 (пакетный режим на построчных индексах)

          ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; 
          GO
          SELECT COUNT(*) FROM dbo.Votes;
          GO
          • Чтение страниц: 694379
          • CPU: 5,2 секунд времени процессора
          • Продолжительность: 0,7 секунды

          Пакетный режим отлично подходит к множеству запросов, создающих отчеты, агрегируя большие объемы данных.

          3: Добавление некластеризованных построчных индексов, но при использовании режима 2017 и ранее

          Я собираюсь создать индекс на каждом из 5 столбцов таблицы dbo.Votes, а затем сравнить их размеры при помощи sp_BlitzIndex:

          CREATE INDEX IX_PostId ON dbo.Votes(PostId); 
          GO
          CREATE INDEX IX_UserId ON dbo.Votes(UserId);
          GO
          CREATE INDEX IX_BountyAmount ON dbo.Votes(BountyAmount);
          GO
          CREATE INDEX IX_VoteTypeId ON dbo.Votes(VoteTypeId);
          GO
          CREATE INDEX IX_CreationDate ON dbo.Votes(CreationDate);
          GO
          / Каковы размеры каждого индекса?
          Выключите фактические планы для выполнения этого:
          /
          sp_BlitzIndex @TableName = 'Votes';
          GO

          Проверьте число строк в каждом индексе сравнительно с его размером. Когда SQL Server необходимо подсчитать число строк в таблице, он достаточно умен, чтобы посмотреть на то, какой из объектов является наименьшим, а затем использовать его для подсчета. Индексы могут иметь различные размеры в зависимости от типов данных индексируемого содержимого, размера содержимого каждой строки, числа NULL-значений и т.п.

          Я вернусь к уровню совместимости 2017 (убирая операции пакетного режима), после чего выполню подсчет:

          ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; 
          GO
          SELECT COUNT(*) FROM dbo.Votes;
          GO

          SQL Server останавливает выбор на индексе BountyAmount, одном из меньших 2Гб:

          Мы экономим на чтении меньшего числа страниц, но по-прежнему читаем то же самое число строк - 150М, поэтому время процессора и продолжительность фактически не меняется:

          • Чтение страниц: 263322
          • CPU: 14,8 секунд времени процессора
          • Продолжительность: 2 секунды

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

          4: Пакетный режим 2019 с некластеризованными построчными индексами

          Итак, давайте теперь испытаем операцию пакетного режима с имеющимися индексами:

          ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; 
          GO
          SELECT COUNT(*) FROM dbo.Votes;
          GO
          • Чтение страниц: 694379
          • CPU: 4,3 секунд времени процессора
          • Продолжительность: 0,6 секунды

          5: Некластеризованный поколоночный индекс с пакетным режимом

          Я намеренно работаю здесь в режиме совместимости 2017, чтобы выяснить, где зарыта собака:

          CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_BountyAmount ON dbo.Votes(BountyAmount); 
          GO
          ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
          GO
          SELECT COUNT(*) FROM dbo.Votes;
          GO

          План выполнения содержит оператор сканирования нашего нового поколоночного индекса, и все операторы в плане относятся к пакетному режиму:

          Тут я должен поменять единицы измерения:

          • Чтение страниц: 73922
          • CPU: 15 миллисекунд
          • Продолжительность: 21 миллисекунда

          Перспективы: Я знаю некоторых разработчиков, кто пытается использовать системные таблицы для быстрого подсчета числа строк, но даже они не могут достичь таких результатов по скорости.

          Вот что необходимо сделать, чтобы запросы SELECT COUNT(*) выполнялись быстро

          1. Иметь SQL server 2017 или новее, и построить поколоночный индекс на таблице.
          2. Иметь любую версию, которая поддерживает пакетный режим на поколоночных индексах, и построить поколоночный индекс на таблице - хотя ваш опыт будет сильно различаться в зависимости от типа вашего запроса. Чтобы познакомиться со спецификой, почитайте о поколоночных индексах, особенно те статьи Niko, где в заголовке упоминается слово "пакетный" (batch).
          3. Иметь SQL SERVER 2019 или новее, и установить уровень совместимости 150 (2019), даже с построчными индексами. Вы сможете по-прежнему значительно сократить использование процессора, благодаря пакетному режиму на построчном хранении. Это действительно легко сделать - вероятно, вам потребуются минимальные изменения вашего приложения и схемы базы данных - хотя у вас и не будет удивительно быстрых ответов в миллисекундах, которые может обеспечить поколоночный индекс.

          Обратные ссылки

          Нет обратных ссылок

          Комментарии

          Показывать комментарии Как список | Древовидной структурой

          Автор не разрешил комментировать эту запись

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *