🥇PostgreSQL antimønstre: CTE x CTE

På grund af mit arbejde er jeg nødt til at håndtere situationer, hvor en udvikler skriver en anmodning og tænker "Basen er smart, den kan klare alt selv!«

I nogle tilfælde (delvis fra uvidenhed om databasens muligheder, dels fra for tidlige optimeringer) fører denne tilgang til fremkomsten af "Frankensteins".

Først vil jeg give et eksempel på en sådan anmodning:

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

For at vurdere kvaliteten af en anmodning substantielt, lad os oprette nogle vilkårlige datasæt:

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

Det viser sig at at læse dataene tog mindre end en fjerdedel af tiden udførelse af forespørgsel:

[se på explain.tensor.ru]

At skille det ad stykke for stykke

Lad os se nærmere på anmodningen og undre os:

Hvorfor er WITH RECURSIVE her, hvis der ikke er nogen rekursive CTE'er?
Hvorfor gruppere min/max-værdier i en separat CTE, hvis de så alligevel er bundet til den originale prøve?
+25 % tid
Hvorfor bruge en ubetinget 'SELECT * FROM' i slutningen for at gentage den forrige CTE?
+14 % tid

I dette tilfælde var vi meget heldige, at Hash Join blev valgt til forbindelsen, og ikke Nested Loop, for så havde vi ikke kun modtaget et CTE Scan-pas, men 10K!

lidt om CTE ScanHer skal vi huske det CTE Scan ligner Seq Scan - det vil sige ingen indeksering, men kun en komplet søgning, hvilket ville kræve 10K x 0.3ms = 3000ms for cyklusser med cte_max eller 1K x 1.5ms = 1500ms ved looping af cte_bind!
Hvad ønskede du egentlig at få som resultat? Ja, normalt er dette spørgsmålet, der dukker op et sted i det 5. minut af analyse af "tre-etagers" forespørgsler.

Vi ønskede at udlæse for hvert unikt nøglepar min/max fra gruppe ved key_a.
Så lad os bruge det til dette vinduesfunktioner:

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

[se på explain.tensor.ru]

Da læsning af data i begge muligheder tager det samme cirka 4-5ms, så er al vores tidsgevinst -32% - dette er i sin reneste form belastning fjernet fra basis-CPU, hvis en sådan anmodning udføres ofte nok.

Generelt bør du ikke tvinge basen til at "bære den runde, rul den firkantede."

Kilde: www.habr.com

PostgreSQL-antimønstre: CTE x CTE

At skille det ad stykke for stykke

Tilføj en kommentar Annuller svar