🥇PostgreSQL Antipatterns: CTE x CTE

Nekuda kwemutsara webasa rangu, ndinofanira kubata nemamiriro ezvinhu kana mugadziri anonyora chikumbiro ofunga “Nheyo yacho yakangwara, inogona kubata zvese pachayo!«

Mune zvimwe zviitiko (zvimwe kubva pakusaziva kugona kweiyo dhatabhesi, zvimwe kubva pakugadzirisa nguva isati yakwana), nzira iyi inotungamirira pakuonekwa kwe "Frankensteins".

Kutanga, ini ndichapa muenzaniso wechikumbiro chakadaro:

-- для каждой ключевой пары находим ассоциированные значения полей
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;

Kuti titarise zvakanyanya kunaka kwechikumbiro, ngatigadzire imwe data isina kujeka seti:

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

Zvinoitika kuti kuverenga data kwakatora isingasviki chikamu chechina chenguva query execution:

[tarisa ku explain.tensor.ru]

Kuchipatsanura chidimbu nechidimbu

Ngatitarisei zvakanyanya pachikumbiro uye tishamisika:

Sei WITH RECURSIVE pano kana pasina maCTE anodzokororwa?
Nei boka min/max kukosha mune yakaparadzana CTE kana ivo vakasungirirwa kune yekutanga sampuli zvakadaro?
+ 25% nguva
Wadii kushandisa isina magumo 'SARADZA * KUBVA' kumagumo kudzokorora iyo yapfuura CTE?
+ 14% nguva

Muchiitiko ichi, takaita rombo rakanaka kuti Hash Join yakasarudzwa yekubatanidza, uye kwete Nested Loop, nekuti ipapo tingadai tisina kugamuchira imwe chete CTE Scan pass, asi 10K!

zvishoma nezve CTE ScanPano tinofanira kurangarira izvozvo CTE Scan yakafanana neSeq Scan - ndiko kuti, hapana indexing, asi chete kutsvaga kwakakwana, izvo zvingada 10K x 0.3ms = 3000ms yema cycles by cte_max kana 1K x 1.5ms = 1500ms kana looping by cte_bind!
Chaizvoizvo, chii chawaida kuwana semhedzisiro? Hongu, kazhinji uyu ndiwo mubvunzo unouya pane imwe nzvimbo muminiti ye5 yekuongorora mibvunzo ye "nyaya nhatu".

Isu taida kuburitsa kune yega yega makiyi peya min/max kubva muboka nekiyi_a.
Saka ngatishandisei izvi hwindo mabasa:

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

[tarisa ku explain.tensor.ru]

Sezvo kuverenga data mune ese maviri sarudzo kunotora yakafanana 4-5ms, saka nguva yedu yese tinowana -32% - ichi chiri muchimiro chayo chakachena mutoro wakabviswa kubva pachigadziko cheCPU, kana chikumbiro chakadaro chichiitwa kakawanda zvakakwana.

Kazhinji, haufanirwe kumanikidza chigadziko kuti "takure chakatenderedza, tenderedza chikwere."

Source: www.habr.com

PostgreSQL Antipatterns: CTE x CTE

Kuchipatsanura chidimbu nechidimbu

Voeg kukanzura mhinduro