Комментарии 31
Спасибо за идею очередного эксперимента по проверке паттернов производительности в условиях параллельной нагрузки и high load .
1) Index Scan vs Seq Scan - гипотеза не подтверждается экспериментально в общем случае
2) Join vs Коррелированный подзапрос - гипотеза не подтверждается экспериментально
3) EXISTS vs IN - в процессе эксперимента
3) MAX vs ARRAY - в плане исследований
Лично мне ваш запрос не кажется понятным для человека. По мне гораздо понятнее оконные функции. Не знаю их точной реализации на PostgreSQL но на MSSQL они всегда быстрее. Потому что очень хорошо паралеллятся и оптимизатор достаточно умный чтобы не искать дальше второго документа:
with data as (
select
cli.id as client
, max(doc.dt) over (partition by cli.id) docDate
, row_number() over (partition by cli.id order by doc.dt) as docNum
from
cli join doc
on doc.cli = cli.id
where
cli.name LIKE '!%'
)
select
client, docDate
from
data
where
docNum = 2
Даже на PG16 это не работает, не говоря о более ранних версиях:
ERROR: window functions are not allowed in WHERE
LINE 11: row_number() OVER (PARTITION BY cli.id ORDER BY doc.dt DES...Ну и параллелиться WindowAgg пока не умеет.
Кроме того, подсчет номера записи в выборке все-таки требует иметь эту выборку (полный JOIN) "под ногами" - то есть грабли ровно те же.
Поправил, вынес оконки в CTE. На MSSQL все так же не считает строки дальше 2-ой.
"Не считает" или "не читает"?
Можно вот так, и даже будет использоваться Run Condition:
WITH pre AS (
SELECT
cli.id
, dt
, row_number() OVER (PARTITION BY cli.id ORDER BY doc.dt DESC) rn
FROM
cli
JOIN
doc
ON doc.cli = cli.id
WHERE
cli.name LIKE '!%'
)
SELECT
id
, max(dt) OVER (PARTITION BY id) dt
FROM
pre
WHERE
rn = 2;Только медленнее в 1.5 раза от исходного:
Это в какой же версии SQL Server допускает оконные функции во WHERE?
По мне гораздо понятнее оконные функции
В MS это вряд ли быстрее. Вот только автор сову на глобус тянет, у него фактически нет клиентов у которых менее 2 документов. Если будет наоборот, то его заумный запрос вероятно проиграет исходному варианту.
Хорошая оптимизация запроса.
Не очень согласен, что это логичнее для человека, особенно, который не сильно погружен и не думает, как планировщик запросов.
Подскажите, пожалуйста, по CTE. Пишется ли что-то на диск, при создании CTE? В старых версиях работало не очень быстро в условиях большой нагрузки, много похожих запросов с CTE нагружали дисковую подсистему и фактически выполнялись дольше, чем без CTE. И там в запросе с массивом не надо ли dts[0] вместо dts[1]? У нас же по идее в массив даты в обратном порядке должны собираться, так как сортировка desc?
CTE не пишется на диск (
temp buffers), пока влезает в work_memв PG массивы нумеруются с 1, а не с 0
Спасибо
Программа пишется не для компьютеров, а для программистов. Я хочу знать что будут думать о вас те кому придется поддерживать код с такими запросами
Программа пишется для решения определенных задач за конкретные деньги.
ФОТ программистов легко конвертируется в затраты на "железо" и обратно. Грубо, чем проще написать программу, тем менее эффективно (долго, дорого) она будет выполняться. Иногда ради эффективности можно многим пожертвовать.
Да нормальный запрос же, просто отформатирован нечитаемо.
Ну, правда, я всегда препочту CTE lateral join'у.
Кажется более привычным и читаемым
Кажется, мы дожили до момента, когда max и count официально объявлены вредными привычками 😄
Очень нравится посыл статьи: вместо «верить в магию агрегатов» — честно формулировать алгоритм, что именно мы хотим прочитать из диска.
И оказывается выигрыш в миллисекундах на тесте легко превращается в часы и минуты на проде, когда запрос гоняется по 1.5 млрд строк…)
@Kilorну да, так и делаем :)
Я правда не понял зачем limit 2, когда надо последний документ.
Ну а ваще limit это вещь! Вчера помогал сделать удаление через него из огромных таблиц. Джойны, экзисты, лейтерал не работали. limit все порешал. И индексы заработали и wal не должен сдохнуть да и дождаться шанс появится :)
PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение
Подтверждено в ходе эксперимента. Подробная статья с деталями, в течении следующей недели.
Кажется, мелочь,
Но, в условиях высокой нагрузки и параллельности - разница в производительности на порядки.
Основная причина - Разница в загрузке CPU
Тест "MAX":
cpu_us= 98%,cpu_sy= 2%Система почти полностью загружена пользовательскими процессами. Это указывает на высокую нагрузку от приложения, с минимальными затратами на системные вызовы.
Тест "ARRAY":
cpu_us= 77–79%,cpu_sy= 15–19%,cpu_id= 3–4%Нагрузка более сбалансирована: значительная доля системных вызовов (
cpu_sy), что может свидетельствовать об активной работе с ядром (например, частые системные вызовы или операции I/O).
Вывод: В тесте "MAX" CPU интенсивнее используется приложением, тогда как в "ARRAY" выше нагрузка на ядро.
Спасибо за наводку про очередной паттерн производительности !
Но, в условиях высокой нагрузки и параллельности - разница в производительности на порядки.
Параллельность тут вообще не при чем. Ни в одном из моих планов она даже не пыталась использоваться.
Вывод: В тесте "MAX" CPU интенсивнее используется приложением, тогда как в "ARRAY" выше нагрузка на ядро.
Тут все достаточно очевидно - при агрегации доминируют затраты на JOIN, производимые как раз в usertime. При его отсутствии, за счет уменьшения общего времени, увеличивается доля чтений данных в systime.
Доброго времени суток.
На самом деле, не совсем понимаю почему акцент статьи на "плохих" (образно) агрегатных функциях, когда вся суть сводится к одному Вашему предложению:
По каждому из 94 нашедшихся клиентов мы вычитывали, в среднем, по 101 документу, что дало на выходе
Nested Loop9465 записей - но зачем мы прочитали столько?
Для чистоты эксперимента повторил Ваше решение на своём железе:
И получил +/- аналогичное ускорение:
Однако, так как всё решение сводится к тому, чтобы отбросить лишние данные на предварительном этапе анализа никто не мешает нам использовать агрегатные функции (мы же знаем, что теперь они будут эффективны):
MATERIALIZED даёт аналогичный результат (на свой запрос MATERIALIZED не накладывал, вероятно, будет ещё быстрее):
Возможно, стоило бы ещё добавить индекс на дату документа, чтоб прям вообще всё ускорить, что только можно.
Может я упустил какой-то момент? Буду рад комментарию
P. S. Версия PG - 17
Безусловно, если делать JOIN по уже заведомо ограниченной парой записей выборке, то агрегатные функции и группировка становятся гораздо менее неэффективными. Но издержки на соединение и группировку никуда не исчезают.
Если закинете планы на explain, будут более наглядно видны затраты на каждом из узлов. Разницу в последних примерах я отношу к погрешности измерений - у меня до 10% разброс между замерами получался, но в статью брал минимально достигнутые.
Спасибо за ответ. Не знал о ресурсе, обязательно ознакомлюсь.
Так LATERAL подразумевает неявный вызов JOIN согласно документации? Ну а группировку, возможно, можно на distinct заменить, записи как раз предварительно отсортированы.
Что касается замеров, то я брал случайный пример, последний запрос скакал в диапазоне 0.9-1.1, а materialized был более стабильным 0.98-1.03
Так LATERAL подразумевает неявный вызов JOIN согласно документации?
В случае возврата из LATERAL единственной записи, в плане видно, что узлов соединений (Nested Loop, ... Join) не появляется. Вот если их хотя бы две, как вот тут, то приходится тратиться и на соединение.
Разницу в последних примерах я отношу к погрешности измерений - у меня до 10% разброс между замерами получался,
Не кажется ли вам, что 10% это очень много для погрешности измерения ?
А какие вообще были статистические показатели измерений ? Сколько замеров делалось чтобы получить указанные границы значений ?
Если немного развить тему, то можно "агрегацию" оконными функциями внести внутрь скобок:
SELECT
cli.id
, first_value dt
FROM
cli
, LATERAL (
SELECT
first_value(dt) OVER w
, nth_value(dt, 2) OVER w
FROM
doc
WHERE
cli = cli.id
WINDOW w AS(ORDER BY cli, dt DESC ROWS UNBOUNDED PRECEDING)
ORDER BY
cli, dt DESC
LIMIT 2
) doc
WHERE
cli.name LIKE '!%' AND
nth_value IS NOT NULL;Но результат все равно чуть хуже из-за необходимости фильтрации "первых" записей при соединении:
Спасибо за идею теста
Гипотеза о лучшей производительности паттерна ARRAY перед паттерном MAX подтверждена экспериментально https://dzen.ru/a/aSVmNMQfaV04rJUT
Интересно , почему в другом случае, использование array не привело к снижению стоимости плана
SELECT c использованием max
SELECT flight_id, max(boarding_time) AS max_boarding_time
FROM bookings.boarding_passes
GROUP BY flight_id ;SELECT c использованием array
SELECT flight_id, (ARRAY_AGG(boarding_time ORDER BY boarding_time DESC))[1] AS max_boarding_time
FROM bookings.boarding_passes
GROUP BY flight_id;Тестовая таблица
demo=# \d bookings.boarding_passes
Unlogged table "bookings.boarding_passes"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
ticket_no | text | | not null |
flight_id | integer | | not null |
seat_no | text | | not null |
boarding_no | integer | | |
boarding_time | timestamp with time zone | | |
Indexes:
"boarding_passes_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
"boarding_passes_flight_id_boarding_no_key" UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
"boarding_passes_flight_id_boarding_time" btree (flight_id, boarding_time)
"boarding_passes_flight_id_seat_no_key" UNIQUE CONSTRAINT, btree (flight_id, seat_no)
Foreign-key constraints:
"boarding_passes_ticket_no_flight_id_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES segments(ticket_no, flight_id)demo=# select * from pg_stat_all_tables where relname ='boarding_passes'\gx
-[ RECORD 1 ]-------+------------------------------
relid | 8169623
schemaname | bookings
relname | boarding_passes
seq_scan | 51900
last_seq_scan | 2025-11-27 07:19:15.316786+00
seq_tup_read | 205297812833
idx_scan | 82596508
last_idx_scan | 2025-11-27 07:25:39.118427+00
idx_tup_fetch | 2946656229
n_tup_ins | 19870560
n_tup_upd | 19831846
n_tup_del | 0
n_tup_hot_upd | 0
n_tup_newpage_upd | 6267745
n_live_tup | 19870557
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum | 2025-11-27 05:00:10.432594+00
last_autovacuum | 2025-11-17 13:09:35.118797+00
last_analyze | 2025-11-27 05:00:26.488626+00
last_autoanalyze | 2025-11-17 13:09:49.000099+00
vacuum_count | 114
autovacuum_count | 32
analyze_count | 115
autoanalyze_count | 14EXPLAIN ANALYZE с использованием max
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize HashAggregate (cost=287737.64..288590.98 rows=85335 width=12) (actual time=1947.406..1971.006 rows=97461 loops=1)
Group Key: flight_id
Batches: 1 Memory Usage: 9233kB
-> Gather (cost=251043.59..286030.94 rows=341340 width=12) (actual time=1681.508..1765.893 rows=433513 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial HashAggregate (cost=250043.59..250896.94 rows=85335 width=12) (actual time=1670.910..1700.935 rows=86703 loops=5)
Group Key: flight_id
Batches: 1 Memory Usage: 8721kB
Worker 0: Batches: 1 Memory Usage: 8721kB
Worker 1: Batches: 1 Memory Usage: 8721kB
Worker 2: Batches: 1 Memory Usage: 8721kB
Worker 3: Batches: 1 Memory Usage: 8721kB
-> Parallel Seq Scan on boarding_passes (cost=0.00..225205.39 rows=4967639 width=12) (actual time=0.044..610.620 rows=3974111 loops=5)
Planning Time: 0.208 ms
Execution Time: 1980.245 ms
(16 rows)EXPLAIN ANALYZE с использованием array
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=18.77..1904463.85 rows=85335 width=12) (actual time=1.471..16508.645 rows=97461 loops=1)
Group Key: flight_id
-> Incremental Sort (cost=18.77..1804044.39 rows=19870556 width=12) (actual time=1.111..12789.624 rows=19870557 loops=1)
Sort Key: flight_id, boarding_time DESC
Presorted Key: flight_id
Full-sort Groups: 93168 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB
Pre-sorted Groups: 91575 Sort Method: quicksort Average Memory: 37kB Peak Memory: 37kB
-> Index Only Scan using boarding_passes_flight_id_boarding_time on boarding_passes (cost=0.56..382097.80 rows=19870556 width=12) (actual time=0.788..3785.574 rows=19870557 loops=1)
Heap Fetches: 0
Planning Time: 0.202 ms
Execution Time: 16516.294 ms
(11 rows)C использованием max: Finalize HashAggregate (cost=287737.64..288590.98 )
C использованием array: GroupAggregate (cost=18.77..1904463.85 )
А вот это уже более интересно. Если использовать сложный тяжелый запрос и большую тестовую БД для нагрузочного тестирования то
В условиях данной тестовой среды с параллельной нагрузкой замена агрегатной функции MAX на конструкцию ARRAY не дает ожидаемого прироста производительности.
PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение