🥇PostgreSQL-antimönster: CTE x CTE

På grund av min arbetslinje måste jag hantera situationer när en utvecklare skriver en förfrågan och tänker "Basen är smart, den klarar allt själv!«

I vissa fall (delvis från okunnighet om databasens möjligheter, dels från för tidiga optimeringar) leder detta tillvägagångssätt till uppkomsten av "Frankensteins".

Först ska jag ge ett exempel på en sådan begäran:

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

För att väsentligt utvärdera kvaliteten på en begäran, låt oss skapa en godtycklig datamängd:

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 visar sig att att läsa data tog mindre än en fjärdedel av tiden fråga körning:

[titta på explain.tensor.ru]

Ta isär den bit för bit

Låt oss ta en närmare titt på begäran och bli förbryllade:

Varför är WITH RECURSIVE här om det inte finns några rekursiva CTE?
Varför gruppera min/max-värden i en separat CTE om de sedan är knutna till originalprovet ändå?
+25 % tid
Varför använda ett ovillkorligt "SELECT * FROM" i slutet för att upprepa föregående CTE?
+14 % tid

I det här fallet hade vi väldigt tur att Hash Join valdes för anslutningen, och inte Nested Loop, för då hade vi inte bara fått ett CTE Scan-pass, utan 10K!

lite om CTE ScanHär måste vi komma ihåg det CTE Scan liknar Seq Scan - det vill säga ingen indexering, utan bara en fullständig sökning, vilket skulle kräva 10K x 0.3ms = 3000ms för cykler med cte_max eller 1K x 1.5ms = 1500ms vid looping av cte_bind!
Egentligen, vad ville du få som resultat? Ja, vanligtvis är det här frågan som dyker upp någonstans i den 5:e minuten när man analyserar "tre våningar"-frågor.

Vi ville skriva ut för varje unikt nyckelpar min/max från grupp av key_a.
Så låt oss använda det för detta fönsterfunktioner:

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

[titta på explain.tensor.ru]

Eftersom att läsa data i båda alternativen tar samma ungefär 4-5ms, så vinner vi all vår tid -32% - det här är i sin renaste form belastning borttagen från bas-CPU, om en sådan begäran utförs tillräckligt ofta.

I allmänhet bör du inte tvinga basen att "bära den runda, rulla den fyrkantiga."

Källa: will.com

PostgreSQL-antimönster: CTE x CTE

Ta isär den bit för bit

Lägg en kommentar Avbryt svar