🥇PostgreSQL Antipatterns: CTE x CTE

Troch myn line fan wurk moat ik omgean mei situaasjes as in ûntwikkelder in fersyk skriuwt en tinkt "De basis is tûk, it kin alles sels oan!«

Yn guon gefallen (foar in part út ûnwittendheid fan de mooglikheden fan de databank, foar in part fan foartidige optimizations), dizze oanpak liedt ta it ferskinen fan "Frankensteins".

Earst sil ik in foarbyld jaan fan sa'n fersyk:

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

Om de kwaliteit fan in fersyk ynhâldlik te evaluearjen, litte wy wat willekeurige dataset oanmeitsje:

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

It docht bliken dat it lêzen fan de gegevens naam minder as in kwart fan 'e tiid query útfiering:

[sjoch op explain.tensor.ru]

It stik foar stik útinoar helje

Litte wy it fersyk in tichterby besjen en fernuverje:

Wêrom is WITH RECURSIVE hjir as d'r gjin rekursive CTE's binne?
Wêrom groepearje min / max wearden yn in aparte CTE as se dan yn elts gefal bûn binne oan it orizjinele stekproef?
+25% tiid
Wêrom in ûnbedoelde 'SELECT * FROM' oan 'e ein brûke om de foarige CTE te werheljen?
+14% tiid

Yn dit gefal wiene wy tige gelok dat Hash Join waard keazen foar de ferbining, en net Nested Loop, want dan soene wy net allinich ien CTE Scan-pas krigen hawwe, mar 10K!

in bytsje oer CTE ScanHjir moatte wy dat betinke CTE Scan is fergelykber mei Seq Scan - dat is, gjin yndeksearring, mar allinich in folsleine sykopdracht, dat soe fereaskje 10K x 0.3ms = 3000ms foar syklusen troch cte_max of 1K x 1.5ms = 1500ms by looping troch cte_bind!
Eigentlik, wat woene jo krije as resultaat? Ja, normaal is dit de fraach dy't earne opkomt yn 'e 5e minút fan it analysearjen fan "trije-ferhaal" fragen.

Wy woenen útfiere foar elk unyk kaaipaar min/max fan groep troch key_a.
Dat litte wy it hjirfoar brûke finster funksjes:

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

[sjoch op explain.tensor.ru]

Sûnt it lêzen fan gegevens yn beide opsjes nimt itselde likernôch 4-5ms, dan al ús tiid winst -32% - dit is yn syn suverste foarm load fuortsmiten fan basis CPU, as sa'n fersyk faak genôch útfierd wurdt.

Yn 't algemien moatte jo de basis net twinge om "de rûne te dragen, de fjouwerkante te rôljen."

Boarne: www.habr.com

PostgreSQL Antipatterns: CTE x CTE

It stik foar stik útinoar helje

Add a comment cancel reply