ProHoster > Blog > Administración > Antipatróns de PostgreSQL: CTE x CTE

Antipatróns de PostgreSQL: CTE x CTE

Debido á miña liña de traballo, teño que facer fronte a situacións nas que un programador escribe unha solicitude e pensa "A base é intelixente, pode xestionar todo por si mesma!«

Nalgúns casos (en parte por descoñecemento das capacidades da base de datos, en parte por optimizacións prematuras), este enfoque leva á aparición de "Frankensteins".

En primeiro lugar, vou dar un exemplo de tal petición:

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

Para avaliar substancialmente a calidade dunha solicitude, creemos un conxunto de datos arbitrario:

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 a lectura dos datos levou menos dunha cuarta parte do tempo execución de consulta:

Antipatróns de PostgreSQL: CTE x CTE[Mira explicar.tensor.ru]

Desmontándoo peza a peza

Vexamos máis de cerca a solicitude e desconcertamos:

  1. Por que está WITH RECURSIVE aquí se non hai CTE recursivos?
  2. Por que agrupar os valores mínimos/máximos nun CTE separado se de todos os xeitos están ligados á mostra orixinal?
    +25% de tempo
  3. Por que usar un "SELECT * FROM" incondicional ao final para repetir o CTE anterior?
    +14% de tempo

Neste caso, tivemos a gran sorte de que se escolleu Hash Join para a conexión, e non Nested Loop, porque entón teriamos recibido non só un pase de exploración CTE, senón 10K.

un pouco sobre CTE ScanAquí hai que lembralo CTE Scan é semellante ao Seq Scan - é dicir, sen indexación, senón só unha busca completa, que requiriría 10K x 0.3 ms = 3000ms para ciclos por cte_max ou 1K x 1.5 ms = 1500ms ao realizar un bucle por cte_bind!
En realidade, que querías conseguir como resultado? Si, normalmente esta é a pregunta que aparece nalgún lugar no minuto 5 de analizar as consultas de "tres pisos".

Queriamos emitir para cada par de claves único min/max do grupo por key_a.
Entón imos usalo para iso funcións da fiestra:

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

Antipatróns de PostgreSQL: CTE x CTE
[Mira explicar.tensor.ru]

Dado que a lectura de datos en ambas opcións leva o mesmo aproximadamente 4-5 ms, entón todo o noso tempo gaña -32% - isto está na súa forma máis pura Carga eliminada da CPU base, se tal solicitude se executa con suficiente frecuencia.

En xeral, non debes forzar a base a "levar a redonda, rodar a cadrada".

Fonte: www.habr.com

Engadir un comentario