Антимодели на PostgreSQL: 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);

Оказва се, че четенето на данните отне по-малко от една четвърт от времето изпълнение на заявка:

[вижте expand.tensor.ru]

Разглобявайки го парче по парче

Нека разгледаме по-отблизо искането и да бъдем озадачени:

1. Защо тук е WITH RECURSIVE, ако няма рекурсивни CTE?
2. Защо да групирате минимални/максимални стойности в отделен CTE, ако те така или иначе са свързани с оригиналната проба?
   +25% време
3. Защо да използвате безусловно 'SELECT * FROM' в края, за да повторите предишния CTE?
   +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);

[вижте expand.tensor.ru]

Тъй като четенето на данни и в двете опции отнема едни и същи приблизително 4-5ms, тогава цялата ни печалба във времето -32% - това е в най-чист вид натоварването е премахнато от основния процесор, ако такава заявка се изпълнява достатъчно често.

Като цяло не трябва да принуждавате основата да „носи кръглата, търкаля квадратната“.

Източник: www.habr.com