ProHoster > Blog > administrasie > PostgreSQL-teenpatrone: CTE x CTE

PostgreSQL-teenpatrone: CTE x CTE

As gevolg van die aard van my werk, moet ek situasies hanteer wanneer 'n ontwikkelaar 'n versoek skryf en dink "Die basis is slim, dit kan alles self hanteer!«

In sommige gevalle (deels uit onkunde oor die vermoëns van die databasis, deels van voortydige optimalisering), lei hierdie benadering tot die verskyning van "Frankensteins".

Eerstens gee ek 'n voorbeeld van so 'n versoek:

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

Om die kwaliteit van 'n versoek substantief te evalueer, kom ons skep 'n paar arbitrêre datastel:

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

Dit blyk dat die lees van die data het minder as 'n kwart van die tyd geneem navraag uitvoering:

PostgreSQL-teenpatrone: CTE x CTE[kyk na explain.tensor.ru]

Om dit stukkie vir stukkie uitmekaar te haal

Kom ons kyk na die versoek van naderby en word verbaas:

  1. Hoekom is WITH RECURSIVE hier as daar geen rekursiewe CTE's is nie?
  2. Waarom min/maks-waardes in 'n aparte CTE groepeer as hulle dan in elk geval aan die oorspronklike monster gekoppel is?
    +25% tyd
  3. Waarom 'n onvoorwaardelike 'SELECT * FROM' aan die einde gebruik om die vorige CTE te herhaal?
    +14% tyd

In hierdie geval was ons baie gelukkig dat Hash Join gekies is vir die verbinding eerder as Nested Loop, aangesien ons toe nie net een CTE Scan-pas ontvang het nie, maar 10K!

'n bietjie oor CTE ScanHier moet ons dit onthou CTE Scan is soortgelyk aan Seq Scan - dit wil sê, geen indeksering nie, maar slegs 'n volledige soektog, wat sou vereis 10K x 0.3ms = 3000ms vir siklusse deur cte_max of 1K x 1.5ms = 1500ms wanneer lus word deur cte_bind!
Eintlik, wat wou jy as resultaat kry? Ja, gewoonlik is dit die vraag wat iewers in die 5de minuut na vore kom van die ontleding van "drie-verhaal" navrae.

Ons wou uitvoer vir elke unieke sleutelpaar min/maksimum van groep deur sleutel_a.
So kom ons gebruik dit hiervoor venster funksies:

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

PostgreSQL-teenpatrone: CTE x CTE
[kyk na explain.tensor.ru]

Aangesien die lees van data in beide opsies dieselfde ongeveer 4-5 ms neem, dan verdien al ons tyd -32% - dit is in sy suiwerste vorm las verwyder van basis SVE, as so 'n versoek gereeld genoeg uitgevoer word.

Oor die algemeen moet jy nie die basis dwing om "die ronde een te dra, die vierkantige een te rol nie."

Bron: will.com

Voeg 'n opmerking