PostgreSQL Antipatterns: в этом плане кто-то лишний

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

Aggregate

Недавно на глаза попался примерно вот такой запрос:

SELECT
  k
  -- тут несколько выражений по 5-10 строк
, sum(s / q) v
FROM
  (
    SELECT
      CASE
        WHEN i % 5 = 0 THEN 5
        WHEN i % 3 = 0 THEN 3
        WHEN i % 2 = 0 THEN 2
        ELSE 1
      END k
    -- тут десятки полей
    , sum(i) s
    , count(i) q
    FROM
      generate_series(1, 1e6) i
    GROUP BY
      k
  ) T
GROUP BY
  1;

Очевидно, что в ходе постепенного усложнения запроса (а я уже рассказывал в "PostgreSQL Antipatterns: сказ об итеративной доработке поиска по названию, или «Оптимизация туда и обратно»", что при этом может происходить) вслед за одной группировкой пришла другая, и визуально их не особо многое связывает - в одном случае это ключ k, а в другом... тот же самый ключ, который стоит у нас в позиции номер 1!

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

HashAggregate (actual time=1240.398..1240.400 rows=4 loops=1)
  Group Key: (CASE WHEN ((i.i % '5'::numeric) = '0'::numeric) THEN 5 WHEN ((i.i % '3'::numeric) = '0'::numeric) THEN 3 WHEN ((i.i % '2'::numeric) = '0'::numeric) THEN 2 ELSE 1 END)
  ->  HashAggregate (actual time=1240.383..1240.386 rows=4 loops=1)
        Group Key: CASE WHEN ((i.i % '5'::numeric) = '0'::numeric) THEN 5 WHEN ((i.i % '3'::numeric) = '0'::numeric) THEN 3 WHEN ((i.i % '2'::numeric) = '0'::numeric) THEN 2 ELSE 1 END
        ->  Function Scan on generate_series i (actual time=183.734..998.200 rows=1000000 loops=1)
Planning Time: 0.122 ms
Execution Time: 1252.607 ms

Но для этого у нас есть визуализация этого плана на explain.tensor.ru, который подскажет, что повторная группировка идет по тому же ключу:

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

Исправим, избавившись от повторной сортировки:

SELECT
  CASE
    WHEN i % 5 = 0 THEN 5
    WHEN i % 3 = 0 THEN 3
    WHEN i % 2 = 0 THEN 2
    ELSE 1
  END k
, sum(i) / count(i) v
FROM
  generate_series(1, 1e6) i
GROUP BY
  k;

Sort

Судьба повторных сортировок развивается обычно точно так же - отлаживали, да недочистили:

SELECT
  i
, 1e6 - i
FROM
  (
    SELECT
      *
    FROM
      generate_series(1, 1e6) i
    WHERE
      (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6)
    ORDER BY -- осталось от отладки
      i DESC
  ) T
ORDER BY
  1;

Sort (actual time=765.072..765.245 rows=4761 loops=1)
  Sort Key: t.i
  Sort Method: quicksort  Memory: 416kB
  ->  Subquery Scan on t (actual time=761.475..763.069 rows=4761 loops=1)
        ->  Sort (actual time=761.471..761.739 rows=4761 loops=1)
              Sort Key: i.i DESC
              Sort Method: quicksort  Memory: 416kB
              ->  Function Scan on generate_series i (actual time=187.029..759.222 rows=4761 loops=1)
                    Filter: (((i % '2'::numeric) = '1'::numeric) AND ((i % '3'::numeric) = '2'::numeric) AND ((i % '5'::numeric) = '4'::numeric) AND ((i % '7'::numeric) = '6'::numeric))"
                    Rows Removed by Filter: 995239
Planning Time: 0.109 ms
Execution Time: 777.414 ms

Про разные причины их возникновения и способы избавиться от них я подробно тоже уже рассказывал в статье "PostgreSQL Antipatterns: убираем медленные и ненужные сортировки". К сожалению, не для всех из них можно однозначно рекомендовать какой-то способ исправления, но иногда - можно.

В отличие от группировки, где совпадение Group Key обязательно для наших "подозреваемых", в сортировке Sort Key могут быть разными, но сами Sort-узлы должны идти или сразу друг за другом, или могут быть разделены Subquery Scan, как в этом случае.

Исправление очевидно - убрать бесполезную внутреннюю сортировку:

SELECT
  i
, 1e6 - i
FROM
  (
    SELECT
      *
    FROM
      generate_series(1, 1e6) i
    WHERE
      (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6)
  ) T
ORDER BY
  1;

Группировки и сортировки - не единственные узлы в плане, которые могут бесполезно повторяться - иногда это происходит даже с целыми сегментами плана. Подробнее читайте в "PostgreSQL Antipatterns: «где-то я тебя уже видел...»".

Знаете еще какие-то неочевидные "грабли" - расскажите в комментариях.