ProHoster > Blog > Adminisztráció > PostgreSQL antipatterns: CTE x CTE

PostgreSQL antipatterns: CTE x CTE

Munkámból adódóan olyan helyzetekkel kell megküzdenem, amikor egy fejlesztő kérést ír, és azt gondolja,Az alap okos, mindent elbír magától!«

Egyes esetekben (részben az adatbázis képességeinek tudatlansága, részben az idő előtti optimalizálás miatt) ez a megközelítés a „frankensteinek” megjelenéséhez vezet.

Először is mondok egy példát egy ilyen kérésre:

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

Egy kérés minőségének érdemi értékeléséhez hozzunk létre néhány tetszőleges adatkészletet:

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

Kiderült, hogy az adatok elolvasása az idő kevesebb mint negyedét vett igénybe lekérdezés végrehajtása:

PostgreSQL antipatterns: CTE x CTE[megtekintés itt: magyarázat.tensor.ru]

Darabról darabra szedve szét

Nézzük meg közelebbről a kérést, és értetlenkedjünk:

  1. Miért van itt a WITH RECURSIVE, ha nincsenek rekurzív CTE-k?
  2. Miért csoportosítanánk a min/max értékeket egy külön CTE-ben, ha úgyis az eredeti mintához vannak kötve?
    +25% idő
  3. Miért használjunk feltétel nélküli 'SELECT * FROM'-ot a végén az előző CTE megismétléséhez?
    +14% idő

Jelen esetben nagy szerencsénk volt, hogy a Hash Join-t választották a csatlakozáshoz, és nem a Nested Loopot, mert akkor nem csak egy CTE Scan belépőt kaptunk volna, hanem 10K-t!

egy kicsit a CTE Scan-rólItt emlékeznünk kell erre A CTE Scan hasonló a Seq Scan-hez - azaz nincs indexelés, hanem csak egy teljes keresés, amihez szükség lenne 10K x 0.3ms = 3000ms ciklusokhoz a cte_max szerint vagy 1K x 1.5ms = 1500ms amikor a cte_bind ciklust hajt végre!
Tulajdonképpen mit akartál elérni ennek eredményeként? Igen, általában ez a kérdés merül fel valahol az 5. percben a „háromszintes” lekérdezések elemzésekor.

Minden egyedi kulcspárhoz ki akartunk adni min/max csoportból kulcs_a szerint.
Használjuk tehát erre ablak funkciók:

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
[megtekintés itt: magyarázat.tensor.ru]

Mivel mindkét opciónál az adatok kiolvasása körülbelül 4-5 ms-ot vesz igénybe, akkor az összes időnyereségünk -32% - ez a legtisztább formában terhelés eltávolítva az alap CPU-ról, ha egy ilyen kérést elég gyakran hajtanak végre.

Általában nem szabad erőltetni az alapot, hogy „vigye a kereket, tekerje a négyzetet”.

Forrás: will.com

Hozzászólás