PostgreSQL-antimønstre: CTE x CTE

På grunn av min arbeidslinje må jeg håndtere situasjoner når en utvikler skriver en forespørsel og tenker "Basen er smart, den klarer alt selv!«

I noen tilfeller (delvis fra uvitenhet om egenskapene til databasen, delvis fra for tidlige optimaliseringer), fører denne tilnærmingen til utseendet til "Frankensteins".

Først vil jeg gi et eksempel på en slik forespørsel:

-- для каждой ключевой пары находим ассоциированные значения полей
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 å vurdere kvaliteten på en forespørsel, la oss lage et vilkårlig datasett:

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 seg at å lese dataene tok mindre enn en fjerdedel av tiden utførelse av spørring:

[se på explain.tensor.ru]

Å ta den fra hverandre bit for bit

La oss se nærmere på forespørselen og bli forundret:

1. Hvorfor er WITH RECURSIVE her hvis det ikke er rekursive CTEer?
2. Hvorfor gruppere min/maks-verdier i en egen CTE hvis de da er knyttet til den opprinnelige prøven uansett?
   +25 % tid
3. Hvorfor bruke en ubetinget 'SELECT * FROM' på slutten for å gjenta forrige CTE?
   +14 % tid

I dette tilfellet var vi veldig heldige at Hash Join ble valgt for tilkoblingen, og ikke Nested Loop, for da hadde vi ikke bare fått ett CTE Scan-pass, men 10K!

litt om CTE ScanHer må vi huske det CTE Scan ligner på Seq Scan - det vil si ingen indeksering, men bare et fullstendig søk, som ville kreve 10K x 0.3ms = 3000ms for sykluser med cte_max eller 1K x 1.5ms = 1500ms ved looping av cte_bind!
Egentlig, hva ønsket du å få som resultat? Ja, vanligvis er dette spørsmålet som dukker opp et sted i det femte minuttet av å analysere "tre-etasjers" spørringer.

Vi ønsket å skrive ut for hvert unike nøkkelpar min/maks fra gruppe ved key_a.
Så la oss bruke det til dette vindusfunksjoner:

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]

Siden lesing av data i begge alternativene tar det samme ca. 4-5 ms, vil all vår tidsgevinst -32% – dette er i sin reneste form last fjernet fra basis-CPU, hvis en slik forespørsel utføres ofte nok.

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

Kilde: www.habr.com