Немного отвлечемся от простых SELECT и посмотрим на реальной бизнес-задаче построения различных "тепловых карт" и "шахматок", как знание возможностей SQL может облегчить жизнь и разработчику, и его базе.
Обычно это начинается с "хотелок" бизнеса вроде "а вот тут мы нарисуем почасовую активность с динамикой по часам и суткам"...
... или "нам нужен отчет по статусам задач в разрезе сотрудников с общими итогами", ...
... или даже "нам нужен список документов на выполнении с их общим количеством и детализацией по исполнителям и клиентам":
Суть всех этих задач примерно одна и та же: у нас есть некоторый исходный набор фактов в БД, а в интерфейсе хочется получить одновременно агрегаты в нескольких разрезах.
Давайте попробуем на примере первой задачи с тепловой картой на временном интервале разобрать несколько вариантов возможной реализации на стороне БД, чтобы найти:
количество фактов в каждой "клетке" день/час
количество фактов в каждом дне
количество фактов в каждом часе
количество фактов на всем интервале
Но сначала сформируем таблицу из миллиона случайным образом распределенных исходных "фактов" по аналогии с использовавшейся в предыдущей статье "SQL HowTo: TOP-N на субинтервалах":
CREATE TABLE timefact AS
SELECT
'2023-01-01'::date
+ '1 sec'::interval * (random() * 365 * 86400)::integer ts -- время факта
FROM
generate_series(1, 1e6);
-- без индекса - никуда
CREATE INDEX ON timefact(ts);Итак, попробуем посчитать искомые данные на интервале декабря.
Очевидно, сначала нам надо научиться получать данные для самой "матрицы" с координатами (день, час):
EXPLAIN (ANALYZE, BUFFERS)
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2
ORDER BY
1, 2;Посмотрим на план этого запроса:
Из 66мс почти треть заняла сортировка. В принципе, если мы можем позволить себе переупорядочивать данные на бизнес-логике, то от упорядочивания результата мы можем отказаться:
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2;Это сэкономит нам примерно четверть времени:
А вот дальше - интереснее... Декабрь-то у нас не завершен, а вполне себе идет "прямо сейчас", поэтому просто выполнить последовательно 4 независимых запроса с нужной агрегацией по исходным данным не выйдет - цифры разбегутся.
Значит, нам необходимо как-то "зафиксировать" данные - и в PostgreSQL мы можем сделать это разными способами.
Временная таблица
Первый способ заключается в формировании временной таблицы с уже предагрегированными данными:
CREATE TEMPORARY TABLE preagg AS
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2;Поскольку данные нам все-таки надо "записать", выполнение этого запроса будет примерно на 50% дольше. Зато дальше все просто и быстро - каждый запрос меньше 1мс:
-- перечитываем сформированные "клетки"
TABLE preagg;
-- по дням
SELECT
dt
, sum(count)
FROM
preagg
GROUP BY 1;
-- по часам
SELECT
hr
, sum(count)
FROM
preagg
GROUP BY 1;
-- "итого"
SELECT
sum(count)
FROM
preagg;Правда, при активном использовании временных таблиц может "пухнуть" системный каталог (таблицы pg_class, pg_attribute, ...), постепенно замедляя все запросы.
Несколько запросов в транзакции
В качестве альтернативы можно рассмотреть вариант транзакции в режиме REPEATABLE READ, где каждый из запросов будет "ходить" по исходным данным:
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- по "клеткам"
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2;
-- по дням
SELECT
ts::date dt
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1;
-- по часам
SELECT
extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1;
-- "итого"
SELECT
count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31';
COMMIT;Однако, мы перечитывали исходные данные и вычисляли ключи агрегации каждый раз, увеличив время выполнения запроса, примерно в те же 4 раза. Не говоря уж о том, что длительные транзакции (если этот отчет будет достаточно долгим) в PostgreSQL могут принести проблем.
CTE + UNION ALL
А почему бы нам не вычислить и вернуть сразу все данные за один запрос?..
Договоримся о формате ответа:
(dt IS NOT NULL, hr IS NOT NULL)- "клетка"(dt IS NOT NULL, hr IS NULL)- по дням(dt IS NULL, hr IS NOT NULL)- по часам(dt IS NULL, hr IS NULL)- "итого"
Вместо временной таблицы воспользуемся CTE, а результаты запросов "склеим" через UNION ALL:
WITH preagg AS (
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2
)
TABLE preagg
UNION ALL
SELECT
dt
, NULL hr
, sum(count) count
FROM
preagg
GROUP BY 1
UNION ALL
SELECT
NULL dt
, hr
, sum(count) count
FROM
preagg
GROUP BY 2
UNION ALL
SELECT
NULL dt
, NULL hr
, sum(count) count
FROM
preagg;В принципе, на этом можно бы и остановиться, поскольку в плане уже все достаточно неплохо:
GROUPING SETS
Но все-таки как-то "неаккуратненько" - слишком много повторяющегося кода нам пришлось написать. Но ведь этого можно и не делать, если воспользоваться функционалом наборов группирования.
Необходимый нам вариант группировок можно записать так:
GROUPING SETS (
(dt, hr)
, (dt )
, ( hr)
, ( )
)
-- или короче:
GROUPING SETS (
CUBE(dt, hr)
)После чего наш запрос сокращается всего-то до вот такого:
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
GROUPING SETS (
CUBE(1, 2)
);Такой план анализировать гораздо приятнее:
В большинстве случаев на этом и стоит остановиться, если значения ключей агрегации у вас не требуют дополнительных вычислений.
Однако, это не наш случай, поскольку мы вычисляли ключи для каждой исходной записи для каждого варианта агрегации независимо - от этого время выполнения нашего запроса существенно выросло!
CTE + GROUPING SETS
"Спрячем" вычисление ключей и предварительную агрегацию обратно "под CTE" и уберем повторные вычисления из GROUPING SETS:
WITH preagg AS (
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2
)
TABLE preagg
UNION ALL
SELECT
dt
, hr
, sum(count) count
FROM
preagg
GROUP BY
GROUPING SETS (
1
, 2
, ()
);Теперь наш запрос снова так же эффективен, как вариант с CTE + UNION ALL, но написать нам пришлось существенно меньше кода:
Всем хочу напомнить, что для эффективной профилировки ваших запросов к PostgreSQL в виде таких красивых картинок вы можете совершенно свободно воспользоваться нашим сервисом explain.tensor.ru или приобрести его для корпоративных нужд.
Комментарии (14)
SserjIrk
20.12.2023 18:50Нет. Вам не нужно ничего кроме ваше первого запроса с группировкой!
Оконные функции прекрасно работают с агрегаторами группировок. Не очень красивый синтаксис, поэтому я предпочитаю сами группировки выносить в CTE, но оптимизаторы достаточно умные чтобы развернуть CTE в один запрос. В ваш первый запрос с группировкой просто нужно добавить колонки, опять же в диалекте T-SQL:
...
, sum(count(*)) over () as [ОбщийИтог]
, sum(count(*)) over (partition by dt) as [ИтогДня]
, sum(count(*)) over (partition by dt, hr) as [ИтогЧаса]
, sum(count(*)) over (partition by hr) as [ИтогЧасов]
from ...
Kilor Автор
20.12.2023 18:50Наверное, я не до конца понимаю идею:
WITH preagg AS ( SELECT ts::date dt , extract(hour FROM ts) hr , count(*) FROM timefact WHERE ts BETWEEN '2023-12-01' AND '2023-12-31' GROUP BY 1, 2 ) SELECT dt , hr , count cnt_cell , sum(count) OVER (PARTITION BY dt) cnt_day , sum(count) OVER (PARTITION BY hr) cnt_hour , sum(count) OVER () cnt_total FROM preagg;Такой запрос плодит дубли значений счетчиков:
cnt_total имеет одинаковое значение вообще во всех строках А кроме этого имеет план с множественными пересортировками, о которых я писал выше:
SserjIrk
20.12.2023 18:50Основная идея в принципе работы оконных функций. Обратите внимание - самые тяжелые операции вашего оптимального плана CTE +
UNION ALLи варианта с оконными функциями технически одинаковые. Но возможно пока вы делали тесты ваш сервер решил кэшировать вашу таблицу. Потому на самом деле с вариантом холодного сервера время запросов практически будет одинаковое. Но главное в самих оконных функциях - они очень легко разносятся по разным процессорам. Не могу сказать как сделает PostgreSQL, но MSSQL однозначно раскидает каждое окно по разным процессорам. Ведь тут не нужно беспокоиться о блокировке или изменении данных.По поводу дублирования счетчиков. Конечно это не относится к статье, но считаю нужно упомянуть. Это не дублирование. Это очень дешевое выполнение той работы которую позже нужно было бы сделать слою представления. Фактически это ничего не стоит, так как окна не считаются для каждой строки, если у вас есть sum(..) over () - она посчитается один раз и будет возвращать кэшированный результат для каждой строки. Но позже построителю предствления нужно будет совершить один единственный прямой обход запроса и на каждом шаге у него будут все необходимые данные для расчета цвета, итога строки или итога колонки.
Kilor Автор
20.12.2023 18:50Это не дублирование. Это очень дешевое выполнение той работы которую позже нужно было бы сделать слою представления.
Я по-прежнему не понимаю цели передавать одно и то же значение, например, итого-счетчика в каждой строке, если его можно передать один раз, в одной записи.
SserjIrk
20.12.2023 18:50Потому что нет никакого смысла счетчик не передавать. Вы получаете некий resultSet в котором так же строго типизированные колонки и на счетчик отведено ровно столько байт сколько нужно для хранения типа. И даже если в колонке null представление этого null-а уложится в то-же самое поле, а остаток дополнится нулями. Но позже в слое представления не нужно делать лишнее действие в виде чтения первой строки и извлечения из него значения счетчитка в локальную переменную. Достаточно просто идти вперед и счетчик всегда под рукой. Но самый большой профит в том что даже не меняя запрос слой предствления может взять ваш результат и без проблем пересортировать его к примеру по время - дата. И опять спокойно пройти один раз имея все данные для отражения в перевернутом виде.
Kilor Автор
20.12.2023 18:50Достаточно просто идти вперед и счетчик всегда под рукой.
Это может быть полезно только для достаточно узкого класса задач вроде вычисления доли строки относительно итога.
Но позже в слое представления не нужно делать лишнее действие в виде чтения первой строки и извлечения из него значения счетчитка в локальную переменную.
Гораздо проще сделать fetchOne в переменную как раз. То есть подобные "преимущества" никак не оправдывают ни лишней работы СУБД, ни раздувания объема resultset, ни избыточных затрат на его разбор на стороне БЛ.
SserjIrk
20.12.2023 18:50Это может быть полезно только для достаточно узкого класса задач вроде вычисления доли строки относительно итога.
Долю очень просто вычислить прямо на сервере:
, sum(count(*)) over () / (100 * sum(count(*)) over (partition by dt)) as [ПроцентДняОтПериода]
, sum(count(*)) over () / (100 * sum(count(*)) over (partition by dt, dh)) as [ПроцентЧасаОтПериода]
, sum(count(*)) over (partition by dе) / (100 * sum(count(*)) over (partition by dt, dh)) as [ПроцентЧасаОтДня]
И это будет очень дешево. Потому что каждая из оконных функций кэширует результат от параметров. Т.е. сколько бы вы не написали расчетов sum(count(*)) over () - расчет будет один раз, а sum(count(*)) over (partition by dt) выполнится по одному разу для каждого дня.
То есть подобные "преимущества" никак не оправдывают ни лишней работы СУБД, ни раздувания объема resultset, ни избыточных затрат на его разбор на стороне БЛ.
Технически для задач из статьи вообще не нужна общая сумма, ее всегда можно получить в конце обхода результат суммируя к один из агрегатов.
Но и в добавлении колонки я не вижу страшного. С одной стороны мы получили новую колонку в 4 байта (тип int) => 720 строк * 4 = 2.8 Кб. С другой стороны исчезли строки группировки (50 штук). Обходов стало поменьше.
ptr128
20.12.2023 18:50Если честно, совершенно не понял, для чего рассматривались варианты без GROUPING SET. Ладно бы это было расширение, а то ведь стандарт SQL.
Kilor Автор
20.12.2023 18:50Причин три:
некоторые все еще живут на 9.4 или на форках от еще более ранних версий, где их нет
некоторые ORM "не разумеют"
GROUPING SETS, а задача-то никуда не деласьпоказать разницу для тех, кто "я так привык, мне и так удобно", и, например, вместо
HAVINGпри группировке используют подзапрос +WHERE
ptr128
20.12.2023 18:50Не думаю, что пытаться поддерживать версии, не поддерживаемые mainstream - хорошая идея. Ваш выбор, конечно. Просто лично я его не приветствую по вполне объективным причинам.
ORM - вообще отдельная история. Опять таки, если он не формирует оптимальный запрос и это критично, то проблема решается представлениями и функциями, а вовсе не временными таблицами, использование которых в PostgreSQL - не лучшая идея.
Последний аргумент понятен. Но так как Вы пишете туториал, то логичней было бы с самого начала описать самый эффективный вариант, а уже потом уже доказывать, что остальные варианты менее эффективны. Психологически, многие дойдя до первого рабочего варианта берут в работу его. Не всегда дочитав туториал до конца. Я даже на работе с таким регулярно сталкиваюсь.
Ну и последний штрих. Раз уж пишете туториал, то стоило бы рассмотреть все возможности GROUPING SETS, включая и [DISTINCT] ROLLUP.
Kilor Автор
20.12.2023 18:50Психологически, многие дойдя до первого рабочего варианта берут в работу его. Не всегда дочитав туториал до конца. Я даже на работе с таким регулярно сталкиваюсь.
Есть и обратная сторона, тоже сталкиваюсь: "Фу, как это для меня сложно, даже читать дальше не буду!"
Раз уж пишете туториал, то стоило бы рассмотреть все возможности GROUPING SETS, включая и [DISTINCT] ROLLUP.
Я не ставил себе тут цели разбирать все возможности SQL, только показать возможные решения конкретной прикладной задачи.
ptr128
20.12.2023 18:50Есть и обратная сторона, тоже сталкиваюсь: "Фу, как это для меня сложно, даже читать дальше не буду!"
Если бы Вы, например, уходили в дебри рекурсивных запросов для skip index scan или даже LATERAL с UNION ALL вместо OR - я бы это понял. Но тут все в рамках стандарта SQL, с которым интересующийся данным вопросом по любому должен ознакомиться.
Я не ставил себе тут цели разбирать все возможности SQL, только показать возможные решения конкретной прикладной задачи.
Ваша статья - Вам и решать. Я лишь высказал свое субъективное мнение. А прислушиваться к нему или нет - Ваше дело.
SserjIrk
Не рассмотрен наиболее интересный вариант с помощью оконных функций. К тому же с группировками вы теряете сами строки, в которых как правило есть какие то важные данные. Ну и оконные функции позволяют все сделать за один проход, так как сами окна будут считаться в памяти, без обращений к физическим таблицам. На t-sql примерно так будут выглядеть все примерно так:
with data as (
SELECT
convert(date, ts) dt
, DATEPART (hour, ts) hr
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
)
SELECT
data.dt
, data.hr
, count() over () as [ОбщиеИтоги] , count() over (partition by data.dt) as [ИтогиДня]
, count() over (partition by data.dt, data.hr) as [ИтогиЧасаДня] , count() over (partition by data.hr) as [ИтогиЧасаВсехДней]
FROM
data
Kilor Автор
Чтобы получить "агрегаты" - то есть уменьшить количество исходных строк, в PostgreSQL (не в курсе про T-SQL) пришлось бы использовать конструкт вида:
SELECT DISTINCT ON(dt) dt, count(*) OVER (PARTITION BY dt)И так для каждого способа группировки, что будет идентично CTE + UNION ALL за исключением множественных пересортировок в плане. Очевидно, что исходные строки при этом потеряются.
А без
DISTINCTдополнительные значения будут расчитаны для каждой исходной записи, много-много раз, и не совсем понятно, как это соотносится с задачей.Вот "склеить" выборку исходных данных (плюс пустые поля для ключей агрегации) и к ней итоги - это можно.