Иногда при анализе производительности запроса на предмет "куда ушло все время" возникает стойкое ощущение deja vu, что вот ровно этот же кусок плана ты уже где-то раньше видел...

Пролистываешь выше - и таки-да, вот он рядом - но почему он там оказался, и как выйти из Матрицы самому и помочь коллегам?

Wake up, Neo. The Matrix has you.
Wake up, Neo. The Matrix has you.

Одна запись - несколько полей

Возьмем простую и достаточно типовую бизнес-задачу - показать последний документ по каждому из некоторого набора покупателей:

CREATE TABLE doc(
  doc_id
    serial
      PRIMARY KEY
, customer_id
    integer
, dt
    date
, sum
    numeric(32,2)
);
CREATE INDEX ON doc(customer_id, dt DESC);

INSERT INTO doc(
  customer_id
, dt
, sum
)
SELECT
  (random() * 1e5)::integer
, now() - random() * '1 year'::interval
, random() * 1e6
FROM
  generate_series(1, 1e5) id;

Для каждого клиента мы хотим иметь в результате исполнения запроса все значимые поля этого "последнего" документа. Что ж, "как слышится, так и пишется":

SELECT
  id customer_id
, (SELECT doc_id FROM doc WHERE customer_id = id ORDER BY dt DESC LIMIT 1) doc_id
, (SELECT dt     FROM doc WHERE customer_id = id ORDER BY dt DESC LIMIT 1) dt
, (SELECT sum    FROM doc WHERE customer_id = id ORDER BY dt DESC LIMIT 1) sum
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id;

И... мы героически вытаскиваем одну и ту же запись из таблицы трижды! [посмотреть на explain.tensor.ru]

Троекратное "ура!" разработчику от сервера
Троекратное "ура!" разработчику от сервера

Возврат целой записи таблицы

И вот зачем мы каждое поле отдельно ищем? Мало того, что это раздувает размер запроса, так еще и выполняется каждый раз заново!

Давайте вернем из вложенного запроса сразу всю запись (только id мы теперь не будем переименовывать, чтобы не получилось два customer_id в результате):

SELECT
  id
, (
    SELECT
      doc -- это запись всей таблицы
    FROM
      doc
    WHERE
      customer_id = id
    ORDER BY
      dt DESC
    LIMIT 1
  ).* -- разворачиваем запись в отдельные поля
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id;

И... теперь вместо 3 циклов у нас стало 4 - по одному на каждое поле извлекаемой вложенным запросом записи, включая customer_id (причем Index Only Scan, когда dt можно было вернуть прямо из индекса, превратился в менее эффективный, зато полностью совпадающий с остальными, Index Scan):

Экранируем запись с помощью CTE

WITH dc AS (
  SELECT
    id
  , (
      SELECT
        doc
      FROM
        doc
      WHERE
        customer_id = id
      ORDER BY
        dt DESC
      LIMIT 1
    ) doc -- это одно поле-запись
  FROM
    unnest(ARRAY[1,2,4,8,16,32,64]) id
)
SELECT
  id
, (doc).* -- разворачиваем в отдельные поля
FROM
  dc;

И, если вы используете версию PostgreSQL ниже 12-й, то все отлично - теперь индекс сканируется однократно (точнее, 7 раз вместо 28):

А вот начиная с PostgreSQL 12, планировщик "разворачивает" содержимое CTE, сводя все к тому же плану с 4 SubPlan. И чтобы он этого не делал, а наш "хак" продолжил работать, для CTE необходимо указать ключевое слово MATERIALIZED:

WITH dc AS MATERIALIZED (
  ...

Незаслуженно забываемый LATERAL

Глядя на все больше обрастающий "хаками" и становящийся менее читабельным код, невольно возникает вопрос - неужели нельзя как-то попроще?

И такой способ есть - это LATERAL-подзапрос, выполняющийся отдельно для каждой записи выборки, собранной на предыдущих шагах (в нашем случае это набор из 7 строк id):

SELECT
  *
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id
LEFT JOIN
  LATERAL(
    SELECT
      *
    FROM
      doc
    WHERE
      customer_id = id
    ORDER BY
      dt DESC
    LIMIT 1
  ) doc
    ON TRUE; -- LEFT JOIN всегда должен иметь ON-condition

Обратите внимание на комбинацию LEFT JOIN LATERAL ... ON TRUE - это неизбежная плата, если мы хотим обязательно получить запись по каждому из 7 наших id, когда документов по конкретному покупателю нет совсем.

Вот что LATERAL животворящий делает!
Вот что LATERAL животворящий делает!

Такой запрос не только ищет запись однократно, но еще и в 1.5 раза быстрее из-за отсутствия необходимости формировать и читать CTE!

Один источник - разные условия

В предыдущем случае все SubPlan делали ровно одно и то же - искали одну и ту же запись по одинаковому условию. Но что если условия у нас окажутся разными?

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

ALTER TABLE doc
  ADD COLUMN emp_author integer
, ADD COLUMN emp_executor integer;
-- проставляем авторов/исполнителей
UPDATE
  doc
SET
  emp_author = (random() * 1e3)::integer
, emp_executor = (random() * 1e3)::integer;

CREATE TABLE employee(
  emp_id
    serial
      PRIMARY KEY
, emp_name
    varchar
);
-- генерируем "сотрудников"
INSERT INTO employee(
  emp_name
)
SELECT
  (
    SELECT
      string_agg(chr(((random() * 94) + 32)::integer), '')
    FROM
      generate_series(1, (random() * 16 + i % 16)::integer)
  )
FROM
  generate_series(1, 1e3) i;

А теперь давайте попробуем получить имена автора/исполнителя для тех документов, которые мы искали на первом шаге - мы ведь уже знаем, что нам поможет LATERAL:

SELECT
  *
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id
LEFT JOIN
  LATERAL(
    SELECT
      *
    FROM
      doc
    WHERE
      customer_id = id
    ORDER BY
      dt DESC
    LIMIT 1
  ) doc
    ON TRUE
LEFT JOIN
  LATERAL( -- извлекаем автора
    SELECT
      emp_name emp_a
    FROM
      employee
    WHERE
      emp_id = doc.emp_author
    LIMIT 1
  ) emp_a
    ON TRUE
LEFT JOIN
  LATERAL( -- извлекаем исполнителя
    SELECT
      emp_name emp_e
    FROM
      employee
    WHERE
      emp_id = doc.emp_executor
    LIMIT 1
  ) emp_e
    ON TRUE;

Небольшое замечание: пожалуйста, не забывайте LIMIT 1 во вложенных запросах, когда вам необходима только одна запись, даже если уверены, что PostgreSQL "знает", что поиск идет по уникальному первичному ключу. Потому что иначе ничто не помешает ему выбрать вариант с Seq Scan по таблице.

Все нормально, но пара узлов в плане оказывается подозрительно похожа друг на друга:

Можно ли свести эти два прохода по индексу в один? Вполне! Используем для этого PIVOT с помощью условных агрегатов:

SELECT
  *
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id
LEFT JOIN
  LATERAL(
    SELECT
      *
    FROM
      doc
    WHERE
      customer_id = id
    ORDER BY
      dt DESC
    LIMIT 1
  ) doc
    ON TRUE
LEFT JOIN
  LATERAL(
    SELECT -- min + FILTER = PIVOT
      min(emp_name) FILTER(WHERE emp_id = doc.emp_author) emp_a
    , min(emp_name) FILTER(WHERE emp_id = doc.emp_executor) emp_e
    FROM
      employee
    WHERE
      emp_id IN (doc.emp_author, doc.emp_executor) -- отбор сразу по обоим ключам
  ) emp
    ON TRUE;

Более подробно про разные нетривиальные варианты использования агрегатов можно почитать в статьях "SQL HowTo: 1000 и один способ агрегации" и "PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN".

Один Index Scan сразу по набору ключей
Один Index Scan сразу по набору ключей

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

Знаете еще другие случаи "клонированных" узлов в планах - поделитесь в комментариях, а у меня на сегодня все.

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


  1. remzalp
    24.08.2021 20:04
    -1

    Скажите, а чем ТАКОЙ запрос хуже???

    SELECT customer_id, doc_id, dt, sum FROM doc WHERE customer_id IN (1,2,4,8,16,32,64) LIMIT 7


    1. VanKrock
      24.08.2021 20:06
      +1

      Тут нужен последний документ по dt для каждого пользователя, у вас же запрос вернёт все документы для пользователя


    1. Kilor Автор
      24.08.2021 20:32
      +1

      Он вернет какие-то 7 из документов этих покупателей. Ни первые, ни последние, ни по каждому - просто "какие-то".


  1. VanKrock
    24.08.2021 20:39

    Если вы используете в запросе с with поле-запись, то почему бы её не использовать в первоначальном запросе?

    select id, (SELECT doc FROM doc WHERE customer_id = id ORDER BY dt DESC LIMIT 1)

    from unnest(ARRAY[1,2,4,8,16,32,64]) id;


    1. Kilor Автор
      24.08.2021 20:49

      Поскольку драйверы поголовно не понимают тип record, необходимо развернуть запись в столбцы (SELECT ...).* - а тогда это будет ровно первый переписанный вариант.


      1. VanKrock
        24.08.2021 20:59

        Можно обернуть в ещё один select

        select s.*

        from (

        select (SELECT doc FROM doc WHERE customer_id = d.customer_id ORDER BY dt DESC LIMIT 1)

        from doc d

        where d.customer_id in (1,2,4,8,16,32,64)) s

        сделал in чтобы убрать пустые строки


        1. Kilor Автор
          24.08.2021 21:03

          Вот как раз убирать пустые строки - противоречит задаче. Ну и двойной поиск по doc вернет на первом уровне все документы по этим клиентам - то есть далеко не 7.


          1. VanKrock
            24.08.2021 22:17
            +1

            Так как есть первичный ключ, понял, что можно обойтись без record. Вообще можно использовать стандартный вариант решения такой задачи

            select
            	d.customer_id,
            	d.doc_id,
            	d.dt,
            	d.sum,
            	author.emp_name author_name,
            	executor.emp_name executor_name
            from
            	doc d,
            	employee author,
            	employee executor
            where d.customer_id in (1,2,4,8,16,32,64)
            and d.dt = (select max(dt) from doc where doc_id = d.doc_id)
            and d.emp_author = author.emp_id
            and d.emp_executor = executor.emp_id



  1. Danik-ik
    24.08.2021 21:28
    +1

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

    select *
    from (
      select *, row_number()
        over (partition by d.customer_id
              order by dt desc) as rn
      from ...
    ) preselect
    where rn=1

    Поля, правда, перечислил бы (Извините, пишу с телефона). Интересно, как оно в смысле плана именно в postgres (до сих пор только с ms работал)?


    1. Kilor Автор
      24.08.2021 21:43

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


      1. mr_stepik
        24.08.2021 23:28

        Присоединюсь к автору комментария: через оконные функции получаются самые эффективные планы:

        with a as ( 
        	select doc_id, 
        	       row_number() over (partition by customer_id order by dt desc) rn
        	 from doc
        	 where customer_id in (1,2,4,8,16,32,64))
        select doc.* 
          from a
          join doc on doc.doc_id = a.doc_id
         where rn = 1;

        тут для вычисления последней записи он просканирует индекс, а уже саму запись прочитает из кучи


        1. mr_stepik
          25.08.2021 00:20

          Хотя, да, признаю, с LATERAL быстрее


        1. Kilor Автор
          25.08.2021 09:32
          +1

          У этого запроса все-таки несколько существенных проблем (план):

          • он обращается к doc дважды (точнее, Bitmap + N x Index Scan)

          • он вычитывает все записи по этим customer_id

          • он не возвращает строку с id, для которого вообще нет документов

          • он в 1.5-2.5 раза медленнее, чем LATERAL


          1. mr_stepik
            17.09.2021 15:26

            Но вообще, спасибо!

            Начал активно пользоваться lateral. это действительно сильно производительнее чем окна.


  1. nin-jin
    25.08.2021 09:59

    Вам не кажется, что лучше сделать запрос последнего документа для покупателя отдельным? Это позволит легко такой запрос закешировать, причём не только на сервере, но и даже на клиенте. Сбрасывать такой кеш не сложно при добавлении документов.


    1. Kilor Автор
      25.08.2021 10:08
      +1

      Отдельным от чего - от СУБД и SQL-запроса по клиентам?

      Тут мы уже выходим за рамки БД и ступаем на зыбкую почву архитектурных модификаций системы - отдельный кэш и его поддержка в актуальном состоянии отдельная непростая тема. Зато в качестве альтернативы можно пробовать прямо в триггере создания/модификации "последнего" документа записывать его ID прямо в карточку клиента.

      Но эффективность этого способа сильно зависит от контента в записи клиента - если там лежит огромный JSON с какими-то атрибутами, его постоянное перезаписывание и рост bloat могут "съесть" весь эффект от более быстрого запроса при чтении.


      1. nin-jin
        25.08.2021 10:55

        Поэтому я и говорю про кеш, а не денормализацию. В этом случае субд (или обёртке вокруг неё) достаточно кинуть событие, что такой-то документ изменился. А владелец кеша уже решает насколько ему это событие релевантно и сбрасывает кеш, если подозревает его неактуальность.


        1. Kilor Автор
          25.08.2021 11:02
          +1

          А дальше мы занимаемся проблемами этого кэша:

          • поиска недолетевшего события

          • организацией очередей событий

          • некорректно отработавшего обработчика события

          • периодической проверки логической целостности его данных

          • необходимости пересинхронизации при потере связи / проблем на узле

          И не всегда оно того стоит.


          1. nin-jin
            25.08.2021 12:22

            Зачем? Для всего этого уже есть готовые решения. Зато мы получаем качественно иной уровень сервиса - данные обновляются в реальном времени, а не так, что смотришь на данные, а они уже 10 минут как не актуальны.


            1. Kilor Автор
              25.08.2021 12:29
              +1

              Да, решения есть - но их много и разных, что сильно усложняет архитектуру и ее сопровождение. А на другой чаше весов - "все в БД", и нельзя однозначно сказать, что это всегда хуже
              .


            1. VanKrock
              25.08.2021 16:20

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