ProHoster > Blog > Идораи > Antipatterns PostgreSQL: CTE x CTE

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]

Онро пора ба порча ҷудо кунед

Биёед дархостро бодиққат дида бароем ва ҳайрон шавем:

  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!
Воқеан, шумо дар натиҷа чӣ ба даст овардан мехостед? Бале, одатан ин саволест, ки дар дақиқаи 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-5ms мегирад, пас тамоми вақти мо фоида меорад -32% - ин дар шакли тозаи он аст сарборӣ аз CPU асосӣ хориҷ карда шуд, агар чунин дархост ба таври кофӣ иҷро карда шавад.

Умуман, шумо набояд пойгоҳро маҷбур кунед, ки "даврро бардоред, чоркунҷаро ғелонед".

Манбаъ: will.com

Илова Эзоҳ