ProHoster > Blog > administração > Antipadrões PostgreSQL: CTE x CTE

Antipadrões PostgreSQL: CTE x CTE

Devido à minha linha de trabalho, tenho que lidar com situações em que um desenvolvedor escreve uma solicitação e pensa “A base é inteligente, dá conta de tudo sozinha!«

Em alguns casos (em parte por ignorância das capacidades da base de dados, em parte por otimizações prematuras), esta abordagem leva ao aparecimento de “Frankensteins”.

Primeiro, darei um exemplo de tal solicitação:

-- для каждой ключевой пары находим ассоциированные значения полей
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 qualidade de uma solicitação, vamos criar alguns conjuntos de dados arbitrários:

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

Acontece que a leitura dos dados levou menos de um quarto do tempo execução da consulta:

Antipadrões PostgreSQL: CTE x CTE[veja explica.tensor.ru]

Desmontando peça por peça

Vamos dar uma olhada no pedido e ficar intrigados:

  1. Por que COM RECURSIVO está aqui se não há CTEs recursivos?
  2. Por que agrupar os valores mínimo/máximo em um CTE separado se eles estiverem vinculados à amostra original de qualquer maneira?
    +25% de tempo
  3. Por que usar um 'SELECT * FROM' incondicional no final para repetir o CTE anterior?
    +14% de tempo

Nesse caso, tivemos muita sorte que Hash Join foi escolhido para a conexão, e não Nested Loop, pois assim teríamos recebido não apenas um passe de CTE Scan, mas 10K!

um pouco sobre CTE ScanAqui devemos lembrar que CTE Scan é semelhante ao Seq Scan - isto é, nenhuma indexação, mas apenas uma pesquisa completa, o que exigiria 10K x 0.3ms = 3000ms para ciclos por cte_max ou 1K x 1.5ms = 1500ms ao fazer loop por cte_bind!
Na verdade, o que você queria como resultado? Sim, geralmente essa é a pergunta que surge em algum momento no 5º minuto de análise de consultas de “três andares”.

Queríamos gerar para cada par de chaves exclusivo min/max do grupo por key_a.
Então vamos usá-lo para isso funções de janela:

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

Antipadrões PostgreSQL: CTE x CTE
[veja explica.tensor.ru]

Como a leitura de dados em ambas as opções leva aproximadamente 4-5ms, todo o nosso ganho de tempo -32% - isto está na sua forma mais pura carga removida da CPU base, se tal solicitação for executada com frequência suficiente.

Em geral, não se deve forçar a base a “carregar o redondo, rolar o quadrado”.

Fonte: habr.com

Adicionar um comentário