ProHoster > Blog > Tsamaiso > PostgreSQL Antipatterns: CTE x CTE

PostgreSQL Antipatterns: CTE x CTE

Ka lebaka la mosebetsi oa ka, ke tlameha ho sebetsana le maemo ha moqapi a ngola kopo mme a nahana "Motheo o bohlale, o khona ho sebetsana le ntho e 'ngoe le e' ngoe ka boeona!«

Maemong a mang (karolo e 'ngoe ka lebaka la ho hloka tsebo ea bokhoni ba database, karolo e 'ngoe ea ho ntlafatsa pele ho nako), mokhoa ona o lebisa ponahalong ea "Frankensteins".

Pele, ke tla fana ka mohlala oa kopo e joalo:

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

Ho lekola boleng ba kopo ka botlalo, ha re theheng sete ea data e sa reroang:

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

Hoa etsahala hore ho bala data ho ile ha nka nako e ka tlase ho kotara ea nako phetho ea potso:

PostgreSQL Antipatterns: CTE x CTE[sheba ho explain.tensor.ru]

Ho e arola sekoto ka sengoathoana

Ha re shebisiseng kopo eo 'me re makale:

  1. Hobaneng ha LE RECURSIVE mona haeba ho se na li-CTE tse iphetang?
  2. Hobaneng ha lihlopha tsa min/max boleng ho CTE e arohaneng haeba li tlameletsoe ho sampole ea mantlha leha ho le joalo?
    + 25% ea nako
  3. Ke hobane'ng ha u sebelisa 'KHETHA * HO TSOA' qetellong ho pheta CTE e fetileng?
    + 14% ea nako

Tabeng ena, re ne re le lehlohonolo haholo hore Hash Join e khethiloe bakeng sa ho hokahanya, eseng Nested Loop, hobane joale re ka be re sa fumane feela pase e le 'ngoe ea CTE Scan, empa 10K!

hanyane ka CTE ScanMona re lokela ho hopola seo CTE Scan e tšoana le Seq Scan - ke hore, ha ho indexing, empa ho batlisisa ka botlalo feela, ho ka hlokang 10K x 0.3ms = 3000ms bakeng sa lipotoloho ka cte_max kapa 1K x 1.5ms = 1500ms ha u roala ka cte_bind!
Ha e le hantle, u ne u batla ho fumana eng ka lebaka leo? E, hangata ena ke potso e hlahang kae-kae metsotsong ea 5 ea ho hlahloba lipotso tsa "mekato e meraro".

Re ne re batla ho hlahisa bakeng sa para e 'ngoe le e 'ngoe ea linotlolo tse ikhethang min/max ho tloha sehlopheng ka key_a.
Kahoo ha re e sebelisetseng sena mesebetsi ea fensetere:

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

PostgreSQL Antipatterns: CTE x CTE
[sheba ho explain.tensor.ru]

Kaha data ea ho bala likhethong ka bobeli e nka hoo e ka bang 4-5ms, joale nako eohle ea rona e fumana phaello -32% - sena se ka sebopeho sa sona se hloekileng mojaro o tlositsoe ho base CPU, haeba kopo e joalo e etsoa hangata ho lekana.

Ka kakaretso, ha ua lokela ho qobella setsi ho "jara e pota-potileng, rola sekoere se le seng."

Source: www.habr.com

Eketsa ka tlhaloso