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

Иногда в архиве нашего сервиса анализа планов запросов к 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 из первой картинки статьи...

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

Публикации

Информация