🥇PostgreSQL Antipatterns: CTE x CTE

Tegevuse iseloomu tõttu tuleb ette olukordi, kus arendaja kirjutab päringu ja mõtleb: "andmebaas on nutikas, lahendab kõik ise!"«

Mõnel juhul (osaliselt teadmatusest andmebaasi võimalustest, osaliselt liigsete varajaste optimeerimiste tõttu) viib selline lähenemine "frankensteini" loomisele.

Alustuseks toome näite sellisest päringust:

-- iga võtme paari jaoks leiame seotud väljade väärtused
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
)
-- leiame iga esimese võtme min/max väärtused
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- seome esimese võtme järgi võtme paarid ja min/max väärtused
, 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;

Kvaliteedi hindamiseks loome tüüpilise andmestiku kogumi:

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

Selgub, et andmete lugemine võttis vähem kui veerand kogu päringu täitmisest: Eurekala:

PostgreSQL antipatternid: CTE x CTE [vaata explain.tensor.ru]

Läbime põhjalikult

Vaadates päringut lähemalt, jääme mõtlema:

Miks on siin WITH RECURSIVE, kui mingit rekurssiivset CTE-d — ei ole?
Miks grupeerida min/max väärtused eraldi CTE-s, kui nad siis ikkagi seotakse originaalvalikuga?
+25% aega
Miks kasutada lõpus uuesti eelmist CTE-d tingimusteta ‘SELECT * FROM’ kaudu?
+14% aega

Meil oli sel juhul eriti vedanud, et ühenduseks valiti Hash Join, mitte Nested Loop, sest muidu saaksime mitte ühte, vaid 10K läbitud CTE skaneeringut!

natuke CTE skaneerimisestSiin tuleb meeles pidada, et CTE skaneerimine on Seq skaneerimise analoog — see tähendab, et mingit indekseerimist ei ole, vaid täielik läbitöötamine, mis nõuab 10K x 0.3ms = 3000ms cte_max-i silmade kaudu või 1K x 1.5ms = 1500ms cte_bind-i silmade kaudu!
Tegelikult, mida me tulemuseks tahtsime? Jah, tavaliselt selline küsimus tekib kusagil viienda minuti jooksul "kolmeastmeliste" päringute analüüsimisel.

Soovisime igale unikaalsele võtme paarile esitada min/max grupi järgi key_a.
Nii et kasutame selleks akenfunktsioone:

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 antipatternid: CTE x CTE
[vaata explain.tensor.ru]

Kuna andmete lugemine mõlemas variandis kestab umbes 4-5 ms, siis on kogu meie aja sääst -32% — puhtalt koormus, mis on eemaldatud CPU põhilt, kui sellist päringut tehakse piisavalt tihti.

Üldiselt ei tasu baasi sundida "ümmargust kandma, kandilist veeretama".

Allikas: habr.com