🥇Que cala EXPLAIN e como conseguir que fale

A pregunta clásica que un desenvolvedor lle trae ao seu DBA ou que un empresario lle trae a un consultor de PostgreSQL case sempre soa igual: "Por que as solicitudes tardan tanto en completarse na base de datos?"

Conxunto tradicional de razóns:

algoritmo ineficiente
cando decides UNIRTE a varios CTE sobre un par de decenas de miles de rexistros
estatísticas obsoletas
se a distribución real dos datos da táboa xa é moi diferente á recollida por ANALIZA a última vez
"conectar" aos recursos
e xa non hai suficiente potencia de computación dedicada da CPU, gigabytes de memoria están bombeándose constantemente ou o disco non pode estar ao día con todos os "queridos" da base de datos
bloqueo de procesos competitivos

E se os bloqueos son bastante difíciles de capturar e analizar, entón para todo o demais necesitamos plan de consulta, que se pode obter utilizando operador EXPLAIN (É mellor, por suposto, EXPLICAR inmediatamente (ANALIZAR, BUFFERS)...) ou módulo auto_explain.

Pero, como se indica na mesma documentación,

"Entender un plan é unha arte, e dominalo require certa experiencia..."

Pero podes prescindir del se usas a ferramenta correcta!

Como é normalmente un plan de consulta? Algo así:

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

ou así:

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

Pero ler o plan no texto "da folla" é moi difícil e pouco claro:

móstrase no nodo suma por recursos da subárbore
é dicir, para comprender canto tempo levou a execución dun nodo en particular, ou canto exactamente esta lectura da táboa traía datos do disco, cómpre restar dalgún xeito un do outro.
é necesario tempo de nodo multiplicar por bucles
si, a resta non é a operación máis complexa que se debe facer "na cabeza"; despois de todo, o tempo de execución indícase como media para unha execución dun nodo e pode haber centos deles
ben, e todo isto xunto impídenos responder á pregunta principal: entón quen "o elo máis débil"?

Cando tentamos explicar todo isto a varios centos dos nosos desenvolvedores, decatámonos de que desde fóra parecía algo así:

E iso significa que necesitamos...

Ferramenta

Nel tentamos recoller toda a mecánica clave que axude a entender “quen ten a culpa e que facer” segundo o plan e a solicitude. Ben, e comparte parte da túa experiencia coa comunidade.
Coñece e usa - explicar.tensor.ru

Visibilidade dos planos

É doado entender o plan cando se ve así?

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

Non realmente.

Pero así, en forma abreviadacando os indicadores clave están separados, é moito máis claro:

Pero se o plan é máis complicado, el acudirá ao rescate distribución temporal de gráfico circular por nodos:

Ben, para as opcións máis difíciles ten présa por axudar gráfico de progreso:

Por exemplo, hai situacións non triviais nas que un plan pode ter máis dunha raíz real:

Pistas estruturais

Ben, se toda a estrutura do plan e os seus puntos doloridos xa están expostos e visibles, por que non destacalos ao programador e explicalos en "lingua rusa"?

Xa recollemos un par de ducias de modelos de recomendación deste tipo.

Perfilador de consulta liña por liña

Agora, se superpón a consulta orixinal ao plan analizado, pode ver canto tempo se gastou en cada declaración individual, algo así:

... ou incluso así:

Substitución de parámetros nunha solicitude

Se "anexou" non só unha solicitude ao plan, senón tamén os seus parámetros desde a liña DETALLE do rexistro, tamén pode copialo nunha das opcións:

con substitución de valor na solicitude
para a execución directa na súa base e máis perfiles
```
SELECT 'const', 'param'::text;
```
con substitución de valor mediante PREPARE/EXECUTE
para emular o traballo do planificador, cando se pode ignorar a parte paramétrica, por exemplo, cando se traballa en táboas particionadas
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Arquivo de planos

Pega, analiza, comparte cos compañeiros! Os plans permanecerán arquivados e podes volver a eles máis tarde: explicar.tensor.ru/archive

Pero se non queres que outros vexan o teu plan, non esquezas marcar a caixa "non publicar no arquivo".

Nos seguintes artigos falarei das dificultades e decisións que xorden á hora de analizar un plan.

Fonte: www.habr.com

Sobre o que EXPLAIN cala e como facelo falar