PostgreSQL antipatterns: CTE x CTE

Mana darba veida dēļ man nākas saskarties ar situācijām, kad izstrādātājs raksta pieprasījumu un domā “Bāze gudra, pati ar visu tiek galā!«

Dažos gadījumos (daļēji datu bāzes iespēju nezināšanas, daļēji priekšlaicīgas optimizācijas dēļ) šī pieeja noved pie “frankenšteinu” parādīšanās.

Pirmkārt, es sniegšu šāda pieprasījuma piemēru:

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

Lai pēc būtības novērtētu pieprasījuma kvalitāti, izveidosim patvaļīgu datu kopu:

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

Izrādās, ka datu lasīšana aizņēma mazāk nekā ceturto daļu laika vaicājuma izpilde:

[apskatiet skaidro.tensor.ru]

Izjaucot to pa gabalu

Apskatīsim pieprasījumu tuvāk un būsim neizpratnē:

1. Kāpēc šeit ir WITH RECURSIVE, ja nav rekursīvu CTE?
2. Kāpēc grupēt minimālās/maksimālās vērtības atsevišķā CTE, ja tās tik un tā ir piesaistītas sākotnējam paraugam?
   +25% laika
3. Kāpēc izmantot beznosacījumu 'SELECT * FROM' beigās, lai atkārtotu iepriekšējo CTE?
   +14% laika

Šajā gadījumā mums ļoti paveicās, ka savienojumam tika izvēlēts Hash Join, nevis Nested Loop, jo tad mēs būtu saņēmuši nevis vienu CTE Scan caurlaidi, bet 10K!

nedaudz par CTE ScanŠeit mums tas ir jāatceras CTE skenēšana ir līdzīga Seq Scan - tas ir, bez indeksācijas, bet tikai pilnīga meklēšana, kas būtu nepieciešama 10 K x 0.3 ms = 3000ms cikliem pēc cte_max vai 1 K x 1.5 ms = 1500ms veicot cilpu ar cte_bind!
Patiesībā, ko jūs gribējāt iegūt rezultātā? Jā, parasti tas ir jautājums, kas parādās kaut kur 5. minūtē, analizējot “trīsstāvu” vaicājumus.

Mēs vēlējāmies izvadīt katram unikālajam atslēgu pārim min/maks no grupas pēc atslēgas_a.
Tāpēc izmantosim to šim nolūkam logu funkcijas:

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

[apskatiet skaidro.tensor.ru]

Tā kā datu nolasīšana abās opcijās aizņem aptuveni 4–5 ms, tad viss mūsu laiks tiek palielināts -32% - tas ir tīrākajā veidā slodze noņemta no bāzes CPU, ja šāds pieprasījums tiek izpildīts pietiekami bieži.

Kopumā nevajadzētu piespiest pamatni “nest apaļo, ripināt kvadrātveida”.

Avots: www.habr.com