ProHoster > Bloc > Administració > Antipatrons PostgreSQL: CTE x CTE

Antipatrons PostgreSQL: CTE x CTE

A causa de la meva línia de treball, he de fer front a situacions en què un desenvolupador escriu una sol·licitud i pensa "La base és intel·ligent, pot gestionar-ho tot!«

En alguns casos (en part per desconeixement de les capacitats de la base de dades, en part per optimitzacions prematures), aquest enfocament porta a l'aparició de "Frankensteins".

En primer lloc, posaré un exemple d'aquesta petició:

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

Per avaluar de manera substancial la qualitat d'una sol·licitud, creem un conjunt de dades arbitrari:

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

Resulta que llegir les dades va trigar menys d'una quarta part del temps execució de la consulta:

Antipatrons PostgreSQL: CTE x CTE[veure explica.tensor.ru]

Desmuntant-lo peça per peça

Fem una ullada més de prop a la sol·licitud i estiguem desconcertats:

  1. Per què hi ha WITH RECURSIVE aquí si no hi ha CTE recursius?
  2. Per què agrupar els valors mínims/màxims en un CTE separat si de totes maneres estan lligats a la mostra original?
    +25% de temps
  3. Per què utilitzar un "SELECT * FROM" incondicional al final per repetir el CTE anterior?
    +14% de temps

En aquest cas, vam tenir molta sort que s'hagués escollit Hash Join per a la connexió, i no Need Loop, perquè llavors hauríem rebut no només una passada d'escaneig CTE, sinó 10K!

una mica sobre CTE ScanAquí ho hem de recordar CTE Scan és similar a Seq Scan - és a dir, sense indexació, sinó només una cerca completa, que requeriria 10K x 0.3 ms = 3000ms per a cicles per cte_max o 1K x 1.5 ms = 1500ms quan es fa un bucle per cte_bind!
De fet, què volies obtenir com a resultat? Sí, normalment aquesta és la pregunta que apareix en algun moment del minut 5 d'anàlisi de consultes de "tres pisos".

Volíem sortir per a cada parell de claus únic min/max del grup per key_a.
Així que utilitzem-lo per a això funcions de la finestra:

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

Antipatrons PostgreSQL: CTE x CTE
[veure explica.tensor.ru]

Com que la lectura de dades en ambdues opcions triga el mateix aproximadament 4-5 ms, tot el nostre temps guanya -32% - això està en la seva forma més pura càrrega eliminada de la CPU base, si aquesta sol·licitud s'executa amb prou freqüència.

En general, no hauríeu de forçar la base a "portar la rodona, fer rodar la quadrada".

Font: www.habr.com

Afegeix comentari