PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение / forpes.ru

Главная
PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение +21

21.11.2025 09:20

Kilor 22 6600 Источник

Иногда в архиве нашего сервиса анализа планов запросов к PostgreSQL встречаются примеры не очень эффективных, мягко говоря, запросов.

Фильтр на 1.5 миллиарда записей и почти 14 минут... ух!

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

Задача: найти дату последнего документа по некоторой выборке клиентов, у которых таких документов несколько.

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

CREATE TABLE cli(
  id
    integer
      PRIMARY KEY
, name
    text
);

CREATE TABLE doc(
  cli
    integer
, dt
    date
);
CREATE INDEX ON doc(cli, dt);

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

Наполним наши таблички для отладки какими-то данными - 10K клиентов и 1M документов:

INSERT INTO cli(id, name)
  SELECT
    id.id
  , name
  FROM
    generate_series(1, 1e4) id
  , LATERAL (
      SELECT
        id
      , string_agg(chr(32 + (random() * 95)::integer), '') name
      FROM
        generate_series(1, (random() * 255)::integer)
    ) T;

INSERT INTO doc(cli, dt)
  SELECT
    (random() * 1e4)::integer cli
  , '2025-01-01'::date + (random() * 365)::integer dt
  FROM
    generate_series(1, 1e6);

Если заглянем в список клиентов, то увидим там очень странные "имена":

Про генерацию случайных данных

В качестве микро-отступления замечу, что если из генерирующего эти имена запроса всего лишь убрать вроде бы неиспользующееся поле id, результат может вас удивить:

  SELECT
    id.id
  , name
  FROM
    generate_series(1, 1e4) id
  , LATERAL (
      SELECT
        -- id -- это поле нигде ведь не используется?..
        string_agg(chr(32 + (random() * 95)::integer), '') name
      FROM
        generate_series(1, (random() * 255)::integer)
    ) T;

-- 1 | cb%y?$sTT&Z*)EYe:O[GH&IQE>xd"*rG?Ft~j}XQ
-- 2 | cb%y?$sTT&Z*)EYe:O[GH&IQE>xd"*rG?Ft~j}XQ
-- 3 | cb%y?$sTT&Z*)EYe:O[GH&IQE>xd"*rG?Ft~j}XQ

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

Допустим, нас попросили решить исходную задачу для тех клиентов, чье "имя" начинается с '!' - понятно, что для этого понадобится подходящий индекс:

CREATE INDEX ON cli(name text_pattern_ops);

Про pattern_ops и индексы для LIKE

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

Наконец, давайте напишем запрос в стиле "как слышится, так и пишется":

SELECT
  cli.id
, max(doc.dt) dt -- дата последнего документа
FROM
  cli
JOIN
  doc
    ON doc.cli = cli.id
WHERE
  cli.name LIKE '!%' -- клиенты с "именем", начинающимся на !
GROUP BY
  cli.id
HAVING
  count(*) > 1; -- хотя бы несколько документов

Ответ мы получим достаточно быстро всего за 4.5 миллисекунды плюс-минус:

"Жирноватый" Nested Loop — "Жирноватый" `Nested Loop`

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

По каждому из 94 нашедшихся клиентов мы вычитывали, в среднем, по 101 документу, что дало на выходе Nested Loop 9465 записей - но зачем мы прочитали столько?..

Нас ведь всего-то просили выдать дату последнего (одного!) документа, если их несколько (хотя бы 2!).

Давайте читать лишь 2 последних документа по каждому из клиентов вместо 101, складывая их в отсортированный массив. Тогда count можно заменить на проверку длины массива, а max - на извлечение первого элемента:

SELECT
  cli.id
, doc.dts[1] dt -- вместо max
FROM
  cli
, LATERAL (
    SELECT
      ARRAY(
        SELECT
          dt
        FROM
          doc
        WHERE
          cli = cli.id
        ORDER BY
          dt DESC -- не забыли отсортировать
        LIMIT 2 -- ограничиили чтение
      ) dts
  ) doc
WHERE
  cli.name LIKE '!%' AND
  array_length(doc.dts, 1) > 1; -- вместо count

Этот подход сразу позволяет ускорить запрос больше чем в 3 раза!

Повторяющиеся SubPlan — Повторяющиеся `SubPlan`

Кейс с повторяющимися SubPlan я уже рассматривал в статье "PostgreSQL Antipatterns: «где-то я тебя уже видел...»", там же приведено и лечение - завернуть в CTE:

WITH pre AS MATERIALIZED (
  SELECT
    cli.id
  , ARRAY(
      SELECT
        dt
      FROM
        doc
      WHERE
        cli = cli.id
      ORDER BY
        dt DESC
      LIMIT 2
    ) dts
  FROM
    cli
  WHERE
    cli.name LIKE '!%'
)
SELECT
  id
, dts[1] dt
FROM
  pre
WHERE
  array_length(dts, 1) > 1;

Такое небольшое изменение позволяет нам добиться результата быстрее 1мс!

Итак, немного проиграв в объеме SQL-кода, мы сделали запрос гораздо более понятным алгоритмически как для человека, так и для PostgreSQL. За это он нас вознаградил ускорением в 5 раз - с 4.587ms до 0.933ms.

Кажется, мелочь, но если вспомнить время с "жирноватого" Nested Loop из первой картинки статьи...

Комментарии (22)

pg_expecto
21.11.2025 09:29
#29144308
Спасибо за идею очередного эксперимента по проверке паттернов производительности в условиях параллельной нагрузки и high load .

1) Index Scan vs Seq Scan - гипотеза не подтверждается экспериментально в общем случае

2) Join vs Коррелированный подзапрос - гипотеза не подтверждается экспериментально

3) EXISTS vs IN - в процессе эксперимента

3) MAX vs ARRAY - в плане исследований

SserjIrk
21.11.2025 09:29
#29144416
Лично мне ваш запрос не кажется понятным для человека. По мне гораздо понятнее оконные функции. Не знаю их точной реализации на 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
```
1. Kilor Автор
  21.11.2025 09:29
  #29144492
  Даже на 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) "под ногами" - то есть грабли ровно те же.
  1. SserjIrk
    21.11.2025 09:29
    #29144508
    Поправил, вынес оконки в CTE. На MSSQL все так же не считает строки дальше 2-ой.
    
    Kilor Автор
    21.11.2025 09:29
    #29144588
    "Не считает" или "не читает"?
    
    Можно вот так, и даже будет использоваться 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 раза от исходного:
1. Akina
  21.11.2025 09:29
  #29144522
  Это в какой же версии SQL Server допускает оконные функции во WHERE?
1. mem700
  21.11.2025 09:29
  #29145258
  По мне гораздо понятнее оконные функции
  
  В MS это вряд ли быстрее. Вот только автор сову на глобус тянет, у него фактически нет клиентов у которых менее 2 документов. Если будет наоборот, то его заумный запрос вероятно проиграет исходному варианту.
  1. Kilor Автор
    21.11.2025 09:29
    #29145306
    В традиционных бизнес-системах документы по клиентам имеют тенденцию повторяться неоднократно (счета ежемесячно, договора ежегодно, отгрузки постоянным клиентам, ...).

seekerhan
21.11.2025 09:29
#29145278
Хорошая оптимизация запроса.

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

Подскажите, пожалуйста, по CTE. Пишется ли что-то на диск, при создании CTE? В старых версиях работало не очень быстро в условиях большой нагрузки, много похожих запросов с CTE нагружали дисковую подсистему и фактически выполнялись дольше, чем без CTE. И там в запросе с массивом не надо ли dts[0] вместо dts[1]? У нас же по идее в массив даты в обратном порядке должны собираться, так как сортировка desc?
1. Kilor Автор
  21.11.2025 09:29
  #29145450
  CTE не пишется на диск (temp buffers), пока влезает в work_mem
  
  в PG массивы нумеруются с 1, а не с 0
  1. seekerhan
    21.11.2025 09:29
    #29145946
    1) видимо такую ситуацию и поймали, когда не влезло в work_mem. Но для этого запроса не актуально, он не увеличивает количество памяти по сравнению с версией без CTE.
    
    2) Забыл об этом.
    
    Спасибо.

seekerhan
21.11.2025 09:29
#29145470
Спасибо

rozhnev
21.11.2025 09:29
#29146688
Программа пишется не для компьютеров, а для программистов. Я хочу знать что будут думать о вас те кому придется поддерживать код с такими запросами
1. Kilor Автор
  21.11.2025 09:29
  #29146764
  Программа пишется для решения определенных задач за конкретные деньги.
  
  ФОТ программистов легко конвертируется в затраты на "железо" и обратно. Грубо, чем проще написать программу, тем менее эффективно (долго, дорого) она будет выполняться. Иногда ради эффективности можно многим пожертвовать.
1. breninsul
  21.11.2025 09:29
  #29146974
  Да нормальный запрос же, просто отформатирован нечитаемо.
  
  Ну, правда, я всегда препочту CTE lateral join'у.
  
  Кажется более привычным и читаемым

PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение +21

Комментарии (22)

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор