PostgreSQL-i antimustrid: CTE x CTE

Oma töövaldkonnast tulenevalt pean tegelema olukordadega, kui arendaja kirjutab päringu ja mõtleb “Alus on nutikas, saab kõigega ise hakkama!«

Mõnel juhul (osaliselt andmebaasi võimaluste teadmatusest, osaliselt ennatlikust optimeerimisest) põhjustab selline lähenemine "Frankensteinide" ilmumist.

Esiteks toon sellise taotluse näite:

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

Päringu kvaliteedi sisuliseks hindamiseks loome suvalise andmestiku:

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 veerandi ajast päringu täitmine:

[vaadake saidil magyarázat.tensor.ru]

Tükkide kaupa lahti võttes

Vaatame taotlust lähemalt ja oleme hämmingus:

1. Miks on siin WITH RECURSIVE, kui rekursiivseid CTE-sid pole?
2. Miks rühmitada min/max väärtused eraldi CTE-sse, kui need on niikuinii seotud algse valimiga?
   +25% aeg
3. Miks kasutada eelmise CTE kordamiseks lõpus tingimusteta 'SELECT * FROM'?
   +14% aeg

Antud juhul meil vedas väga, et ühendamiseks valiti Hash Join, mitte aga Nested Loop, sest siis oleksime saanud mitte ainult ühe CTE Scan passi, vaid 10K!

natuke CTE ScanistSiin peame seda meeles pidama CTE Scan on sarnane Seq Scaniga - see tähendab, et ei mingit indekseerimist, vaid ainult täielik otsing, mis nõuaks 10 k x 0.3 ms = 3000ms tsüklite jaoks cte_max järgi või 1 k x 1.5 ms = 1500ms cte_bindi loomisel!
Tegelikult, mida sa selle tulemusel saada tahtsid? Jah, tavaliselt kerkib see küsimus "kolmekorruseliste" päringute analüüsimisel esile kuskil 5. minutil.

Tahtsime väljastada iga kordumatu võtmepaari min/max rühmast võtme_a järgi.
Nii et kasutame seda selleks akna funktsioonid:

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

[vaadake saidil magyarázat.tensor.ru]

Kuna andmete lugemine mõlemas valikus võtab umbes 4-5 ms, siis kogu aeg võidab -32% - see on kõige puhtamal kujul põhiprotsessorilt eemaldatud koormus, kui sellist taotlust täidetakse piisavalt sageli.

Üldiselt ei tohiks alust sundida kandma ümmargust, veerema kandilist.

Allikas: www.habr.com