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);

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

Както и предполагахме, намерихме всичките 30 записа. Но за това изразходихме 60% от общото време — защото направихме и 30 търсения по индекса. А по-малко — може ли?

Разглеждаме в детайли

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

1. Защо тук има WITH RECURSIVE, когато няма никакви рекурсивни CTE?
2. Защо да групираме min/max-стойностите в отделен CTE, когато те все пак се свързват с оригиналната извадка?
   +25% време
3. Защо да използваме в края повторно прочитане от предходния 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!
Всъщност, какво искахме да получим като резултат? Аха, обикновено именно такъв въпрос идва на ум около петата минута на разглеждане на «трехетажни» заявки.

Искахме за всяка уникална ключова двойка да изведем 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);

Както и предполагахме, намерихме всичките 30 записа. Но за това изразходихме 60% от общото време — защото направихме и 30 търсения по индекса. А по-малко — може ли?

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

В общи линии, не трябва да нагаждате базата да „носи кръгло и да търкаля квадратното“.

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