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

Маълум мешавад, ки он аст хондани маълумот камтар аз чоряки вақти умумиро гирифт иҷрои дархост:

Antipatterns PostgreSQL: CTE x CTE [нигаред дар 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-уми таҳлили пурсишҳои "сеошёна" пайдо мешавад.

Мо мехостем, ки барои ҳар як ҷуфт калидҳои беназир баромад кунем дақ/макс аз гурӯҳ аз ҷониби 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);

Antipatterns PostgreSQL: CTE x CTE
[нигаред дар explain.tensor.ru]

Азбаски хондани маълумот дар ҳарду вариант якхела аст, тақрибан 4-5 мс, пас тамоми фоидаи мо бо мурури замон -32% - ин дар шакли тозатарини он аст сарборӣ аз пойгоҳи CPU хориҷ, агар чунин дархост ба таври кофӣ зуд иҷро карда шавад.

Умуман, асосҳоро маҷбур кардан лозим нест, ки «чизҳои мудаввар, чизҳои чоркунҷаро печонед».

Манбаъ: will.com