ProHoster > Blog > podávání > Antipatterny PostgreSQL: CTE x CTE

Antipatterny PostgreSQL: CTE x CTE

Vzhledem ke svému oboru práce musím řešit situace, kdy vývojář napíše požadavek a myslí si „Základna je chytrá, se vším si poradí sama!«

V některých případech (částečně z neznalosti možností databáze, částečně z předčasných optimalizací) tento přístup vede ke vzniku „Frankensteinů“.

Nejprve uvedu příklad takového požadavku:

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

Abychom mohli věcně vyhodnotit kvalitu požadavku, vytvořte si libovolný soubor dat:

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

Ukázalo se, že čtení dat trvalo méně než čtvrtinu času provedení dotazu:

Antipatterny PostgreSQL: CTE x CTE[podívejte se na explain.tensor.ru]

Rozebrat to kus po kuse

Podívejme se na žádost blíže a nechme se zmást:

  1. Proč je zde WITH RECURSIVE, když neexistují žádné rekurzivní CTE?
  2. Proč seskupovat hodnoty min/max do samostatného CTE, pokud jsou pak stejně svázány s původním vzorkem?
    +25 % času
  3. Proč používat bezpodmínečné 'SELECT * FROM' na konci k opakování předchozího CTE?
    +14 % času

V tomto případě jsme měli velké štěstí, že pro připojení byl vybrán Hash Join, a ne Nested Loop, protože pak bychom dostali nejen jeden průchod CTE Scan, ale 10 XNUMX!

něco o CTE ScanTady to musíme mít na paměti CTE Scan je podobný Seq Scan - tedy žádné indexování, ale pouze kompletní vyhledávání, které by vyžadovalo 10K x 0.3 ms = 3000ms pro cykly podle cte_max nebo 1K x 1.5 ms = 1500ms při opakování pomocí cte_bind!
Vlastně, čeho jste chtěli dosáhnout? Jo, to je obvykle otázka, která se objeví někde v 5. minutě analýzy „třípatrových“ dotazů.

Chtěli jsme vytvořit výstup pro každý jedinečný pár klíčů min/max ze skupiny podle klíče_a.
Tak to k tomu použijme funkce okna:

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

Antipatterny PostgreSQL: CTE x CTE
[podívejte se na explain.tensor.ru]

Vzhledem k tomu, že čtení dat v obou možnostech trvá stejně přibližně 4-5 ms, získáme veškerý náš čas -32% - to je ve své nejčistší podobě zátěž odstraněna ze základního CPU, pokud je takový požadavek prováděn dostatečně často.

Obecně platí, že byste neměli nutit základnu, aby „nesla kulatou, rolovala čtvercovou“.

Zdroj: www.habr.com

Přidat komentář