Ещё одно статья от ребят из Cybertec. Пару раз пришлось прочитаь чтобы понять смысл (нет, это я не про сложность языка сейчас). И ещё несколько экспериментов провёл с рекурсивным запросом.
Ссылка на оригинал: Performance tuning: max and group by
Сейчас все говорят о временных рядах, анализе временных рядов и других способах настройки производительности. Анализ временных рядов в PostgreSQL может предоставить ценную информацию, помочь в принятии обоснованных решений и более глубоком понимании данных. Используя мощные возможности PostgreSQL, мы можем эффективно запрашивать все типы данных измерений для отслеживания тенденций, закономерностей и аномалий во времени. Но есть крошечное требование, которое люди не всегда могут понять.
Оглавление:
Посмотрим на следующие данные:
CREATE TABLE t_sample (
tstamp timestamptz,
sensor_id int,
measurement numeric
); CREATE TABLE
CREATE TABLE
INSERT INTO t_sample SELECT x, y, random() * 10000
FROM generate_series('2025-01-01',
'2025-01-06',
'1 second'::interval) AS x,
generate_series(1, 100) AS y
ORDER BY random();
INSERT 0 43200100
Общие вопросы о временных рядах
Вот один из наиболее типичных вопросов, который можно задать для указанных данных:
- Найдите наибольшее значение для каждого датчика
В этой статье мы обсудим, как это можно сделать наиболее эффективно.
Поиск наибольшего значения для каждой группы
Скорее всего, это самый распространенный вариант, который мы можем увидеть. Для каждого датчика в нашей серии мы хотим видеть наибольшее значение. Звучит просто? Ну, да… Давайте посмотрим на запрос:
test=# explain analyze SELECT sensor_id, max(measurement)
FROM t_sample
GROUP BY 1
ORDER BY 1;
QUERY PLAN
--------------------------------------------------------------------------
Finalize GroupAggregate (cost=546553.97..546579.31 rows=100 width=36)
(actual time=3620.266..3621.127 rows=100 loops=1)
Group Key: sensor_id
-> Gather Merge (cost=546553.97..546577.31 rows=200 width=36)
(actual time=3620.260..3621.079 rows=300 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=545553.95..545554.20 rows=100 width=36)
(actual time=3616.385..3616.388 rows=100 loops=3)
Sort Key: sensor_id
Sort Method: quicksort Memory: 28kB
Worker 0: Sort Method: quicksort Memory: 28kB
Worker 1: Sort Method: quicksort Memory: 28kB
-> Partial HashAggregate (cost=545549.63..545550.63
rows=100 width=36)
(actual time=3616.322..<strong>3616.328</strong> rows=100 loops=3)
Group Key: sensor_id
Batches: 1 Memory Usage: 32kB
Worker 0: Batches: 1 Memory Usage: 32kB
Worker 1: Batches: 1 Memory Usage: 32kB
-> Parallel Seq Scan on t_sample
(cost=0.00..455549.42 rows=18000042 width=15)
(actual time=0.545..1873.428 rows=14400033 loops=3)
Planning Time: 0.154 ms
Execution Time: 3621.199 ms
(18 rows)
А в запросе происходит то, что PostgreSQL прочитает ВСЕ данные и запустит агрегацию. Чтобы ускорить процесс, сервер строит параллельный запрос, использующий несколько ядер процессора одновременно. Однако основная проблема заключается в следующем: нам нужно прочитать все данные. Конечно, это приводит к увеличению времени выполнения, которое продолжает расти по мере увеличения объема данных.
Давайте попробуем создать индекс и посмотрим, изменится ли что-нибудь:
test=# CREATE INDEX ON t_sample (sensor_id, measurement);
CREATE INDEX
test=# \d t_sample
Table "public.t_sample"
Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
tstamp | timestamp with time zone | | |
sensor_id | integer | | |
measurement | numeric | | |
Indexes:
"t_sample_sensor_id_measurement_idx" btree (sensor_id, measurement)
Повторный запуск запроса не приведет к улучшению плана выполнения. Способ решения этой проблемы — смоделировать то, что обычно называют «skip scan» (пропуском сканирования). Что это значит? Для каждого значения датчика находим максимум. Система может довольно быстро найти максимальное значение для одного датчика.
test=# explain analyze SELECT max(measurement)
FROM t_sample
WHERE sensor_id = 10;
QUERY PLAN
-----------------------------------------------------------------------------
Result (cost=0.60..0.61 rows=1 width=32)
(actual time=0.111..0.112 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.56..0.60 rows=1 width=11)
(actual time=0.106..0.107 rows=1 loops=1)
-> Index Only Scan Backward
using t_sample_sensor_id_measurement_idx
on t_sample (cost=0.56..13321.39 rows=375841 width=11)
(actual time=0.105..0.105 rows=1 loops=1)
Index Cond: ((sensor_id = 10) AND (measurement IS NOT NULL))
Heap Fetches: 0
Planning Time: 0.269 ms
Execution Time: 0.143 ms
(8 rows)
Вау! мы можем найти одно значение за долю миллисекунды. К сожалению, нам придётся схитрить, чтобы сделать это для всех датчиков, а не только для одного. Существует два способа эмулировать skip scan:
- Использовать таблицу для хранения списка датчиков и используйте lateral join
- Использовать рекурсивный запрос
В следующем примере показано, как мы можем использовать lateral join для решения проблемы:
test=# explain analyze SELECT * FROM generate_series(1, 100) AS x,
LATERAL (SELECT max(measurement) FROM t_sample WHERE sensor_id = x);
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop (cost=0.60..63.05 rows=100 width=36)
(actual time=2.656..5.002 rows=100 loops=1)
-> Function Scan on generate_series x
(cost=0.00..1.00 rows=100 width=4)
(actual time=2.520..2.532 rows=100 loops=1)
-> Result (cost=0.60..0.61 rows=1 width=32)
(actual time=0.024..0.024 rows=1 loops=100)
InitPlan 1 (returns $1)
-> Limit (cost=0.56..0.60 rows=1 width=11)
(actual time=0.023..0.023 rows=1 loops=100)
-> Index Only Scan Backward
using t_sample_sensor_id_measurement_idx
on t_sample (cost=0.56..15312.59 rows=432001 width=11)
(actual time=0.023..0.023 rows=1 loops=100)
Index Cond: ((sensor_id = x.x)
AND (measurement IS NOT NULL))
Heap Fetches: 0
Planning Time: 0.481 ms
Execution Time: 5.124 ms
(10 rows)
Что такое LATERAL? Давайте отвлечёмся и посмотрим на SQL с философской точки зрения:
SELECT whatever FROM foo;
В процедурном коде это будет иметь вид:
foreach x in foo
loop
do_whatever
end loop;
Предложение FROM можно рассматривать как своего рода цикл. Итак: если мы хотим найти максимум для списка значений, нам нужен цикл вокруг этого цикла. Это именно то, что LATERAL и делает: для каждого значения, возвращаемого вызовом gener_series (= генерация списка датчиков), мы запускаем LATERAL-часть запроса. Проблема в том, что мы генерируем список датчиков, что делает запрос слишком «статичным», поэтому лучше получить список датчиков из второго отношения (нормализации).
Однако мы можем добиться того же, используя простую рекурсию:
test=# explain analyze
WITH RECURSIVE x AS (
SELECT min(sensor_id) AS s
FROM t_sample
UNION ALL
SELECT (SELECT min(sensor_id)
FROM t_sample
WHERE sensor_id > x.s)
FROM x
WHERE x.s IS NOT NULL
)
SELECT s AS sensor_id,
(SELECT max(measurement)
FROM t_sample
WHERE t_sample.sensor_id = x.s) AS measurement
FROM x
WHERE s IS NOT NULL;
QUERY PLAN
-----------------------------------------------------------------
CTE Scan on x (cost=64.66..127.73 rows=100 width=36)
(actual time=0.103..2.410 rows=100 loops=1)
Filter: (s IS NOT NULL)
Rows Removed by Filter: 1
CTE x
-> Recursive Union (cost=0.60..64.66 rows=101 width=4)
(actual time=0.060..0.838 rows=101 loops=1)
-> Result (cost=0.60..0.61 rows=1 width=4)
(actual time=0.058..0.059 rows=1 loops=1)
InitPlan 3 (returns $1)
-> Limit (cost=0.56..0.60 rows=1 width=4)
(actual time=0.051..0.052 rows=1 loops=1)
-> Index Only Scan
using t_sample_sensor_id_measurement_idx
on t_sample t_sample_1
(cost=0.56..1423086.31 rows=43200100 width=4)
(actual time=0.049..0.050 rows=1 loops=1)
Index Cond: (sensor_id IS NOT NULL)
Heap Fetches: 0
-> WorkTable Scan on x x_1
(cost=0.00..6.30 rows=10 width=4)
(actual time=0.007..0.007 rows=1 loops=101)
Filter: (s IS NOT NULL)
Rows Removed by Filter: 0
SubPlan 2
-> Result (cost=0.60..0.61 rows=1 width=4)
(actual time=0.007..0.007 rows=1 loops=100)
InitPlan 1 (returns $3)
-> Limit
(cost=0.56..0.60 rows=1 width=4)
(actual time=0.006..0.006 rows=1 loops=100)
-> Index Only Scan
using t_sample_sensor_id_measurement_idx
on t_sample
(cost=0.56..510365.23 rows=14400033 width=4)
(actual time=0.006..0.006 rows=1 loops=100)
Index Cond: ((sensor_id IS NOT NULL)
AND (sensor_id > x_1.s))
Heap Fetches: 0
SubPlan 6
-> Result (cost=0.60..0.61 rows=1 width=32)
(actual time=0.015..0.015 rows=1 loops=100)
InitPlan 5 (returns $6)
-> Limit (cost=0.56..0.60 rows=1 width=11)
(actual time=0.015..0.015 rows=1 loops=100)
-> Index Only Scan Backward
using t_sample_sensor_id_measurement_idx
on t_sample t_sample_2
(cost=0.56..15312.59 rows=432001 width=11)
(actual time=0.014..0.014 rows=1 loops=100)
Index Cond: ((sensor_id = x.s)
AND (measurement IS NOT NULL))
Heap Fetches: 0
Planning Time: 0.446 ms
Execution Time: 2.495 ms
(30 rows)
Основная идея: мы находим наименьший sensor_id — это что очень быстро. Затем мы ищем следующее наименьшее значение, больше, чем то, которое мы нашли ранее. Мы продолжаем делать это в рекурсии, пока быстро не составим уникальный список sensor_id. Рекурсия является самым быстрым способом найти уникальный список sensor_id без необходимости читать всю таблицу. По сути, это и есть имитация «skip scan», упомянутого ранее. Наконец, мы можем использовать этот список уникальных IDS датчиков («x»), чтобы найти максимальное значение для каждого из них, что, конечно же, представляет собой быстрый поиск по индексу.
Итог…
Если вы хотите узнать больше о временных рядах, возможно, вы захотите прочитать мою статью о простом сопоставление шаблонов и кодировании строк в PostgreSQL, которая также доступна на нашем веб-сайте.
Ханс-Юрген Шёниг (Hans-Jürgen Schönig)
Ханс-Юрген Шениг имеет опыт работы с PostgreSQL с 90-х годов. Он является генеральным директором и техническим руководителем компании CYBERTEC, которая является одним из лидеров рынка в этой области и с 2000 года обслуживает бесчисленное количество клиентов по всему миру.
Ещё раз ссылка на оригинал: Performance tuning: max and group by
Кстати, для интереса — можете сравнить эти два варианта с помощью вывода прочитанных страниц (BUFFERS).
Leave a Reply