🥇PostgreSQL Antipatterns: CTE x CTE

За діяльністю доводиться стикатися з ситуаціями, коли розробник пише запит і думає «база розумна, сама з усім впорається!«

У деяких випадках (частково від незнання можливостей БД, частково від передчасних оптимізації) такий підхід призводить до появи «франкенштейнів».

Спочатку наведу приклад такого запиту:

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

Щоб предметно оцінювати якість запиту, давайте створимо якийсь довільний набір даних:

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

Виявляється, саме читання даних зайняло менше чверті всього часу виконання запиту:

[Подивитися на explain.tensor.ru]

Розбираємо по кісточках

Уважно подивимося на запит, і здивуємося:

Навіщо тут WITH RECURSIVE, якщо жодних рекурсивних CTE немає?
Навіщо групувати min/max-значення в окремій CTE, якщо потім вони однаково прив'язуються до оригінальної вибірки?
+25% часу
Навіщо використовувати наприкінці повторну начитку з попередньої CTE через безумовний 'SELECT * FROM'?
+14% часу

В даному випадку нам ще дуже пощастило, що для з'єднання було обрано Hash Join, а не Nested Loop, оскільки тоді ми отримали б не один-єдиний прохід CTE Scan, а 10K!

трохи про CTE ScanТут треба згадати, що CTE Scan є аналогом Seq Scan — тобто ніякої індексації, а лише повний перебір, який би зажадав 10K x 0.3ms = 3000ms при циклах по cte_max або 1K x 1.5ms = 1500ms при циклах з cte_bind!
Власне, а що хотіли отримати в результаті? Ага, зазвичай саме таке питання і відвідує десь на 5-й хвилині розбору «триповерхових» запитів.

Ми хотіли для кожної унікальної ключової пари вивести min/max із групи по key_a.
Тож скористаємося для цього віконними функціями:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

[Подивитися на explain.tensor.ru]

Оскільки читання даних в обох варіантах займає приблизно 4-5ms, то весь наш виграш за часом -32% - Це в чистому вигляді навантаження, прибране з CPU базиякщо такий запит виконується досить часто.

Загалом, не варто основу змушувати «кругле – носити, квадратне – катати».

Джерело: habr.com

PostgreSQL Antipatterns: CTE x CTE

Розбираємо по кісточках

Додати коментар або відгук Скасувати відповідь