🥇Você se lembra de como tudo começou? Tudo era pela primeira vez e de novo

Sobre como eu tive que otimizar uma consulta PostgreSQL e o que resultou disso.
Por que isso era necessário? Porque, durante os quatro anos anteriores, tudo havia funcionado de forma tranquila e sem problemas, como um relógio.
Como uma epígrafe.

Baseado em eventos reais.
Todos os nomes foram alterados; quaisquer coincidências são mera coincidência.

Ao alcançar um determinado resultado, é sempre interessante lembrar qual foi o impulso inicial, como tudo começou.

Assim, o resultado é descrito brevemente no artigo “Síntese como um dos métodos para melhorar o desempenho do PostgreSQL".

Seria interessante recriar a sequência de eventos que o precederam.
O histórico preservou a data exata de início: 2018-09-10 18:02:48.
Além disso, na história existe um pedido com o qual tudo começou:
Consulta problemáticaSELECIONE
p.“PARAMETER_ID” como parameter_id,
pd.«PD_NAME» COMO pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
com «LRM» COMO LRM,
w.«LOTID» COMO lotid,
com «RTD_VALUE» como valor_RTD,
com “LOWER_SPEC_LIMIT” COMO lower_spec_limit,
com “UPPER_SPEC_LIMIT” COMO upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“DATA_DE_GASTO” COMO data_de_gasto,
extrair(ano de "DATA_DE_GASTO") COMO ano,
extrair(mês de "DATA_GASTA") como mês,
s."REPORT_NAME" AS nome_do_relatório,
p.«STPM_NAME» COMO stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
DE wdata w,
gasto s,
pmtr p,
gasto_pd sp,
pd pd
ONDE s.“SPENT_ID” = w.“SPENT_ID”
E p."PARAMETER_ID" = w."PARAMETER_ID"
E s.“SPENT_ID” = sp.“SPENT_ID”
E pd."PD_ID" = sp."PD_ID"
E s.“SPENT_DATE” >= '2018-07-01' E s.“SPENT_DATE” <= '2018-09-30'
e s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
A partir do gasto s2,
wdata w2
ONDE s2.“SPENT_ID” = w2.“SPENT_ID”
E w2.«LRM» = w.«LRM»);

A descrição do problema é previsivelmente padrão: "Está tudo ruim. Diga-me qual é o problema."
Imediatamente me lembrei de uma piada dos tempos dos drives de disquete de 3 polegadas:

Um leigo se aproxima de um hacker.
-Nada funciona para mim, diga-me onde está o problema.
-No DNA…

Mas certamente essa não é a maneira de resolver problemas de desempenho.”Eles podem não nos entender."(c). Precisamos descobrir isso.
Bem, vamos investigar. Talvez encontremos algo no final.

A investigação foi iniciada.

Então, o que é imediatamente visível a olho nu, mesmo sem recorrer à ajuda do EXPLICAR.
1) Não usar JOINs. Isso é ruim, especialmente se houver mais de um JOIN.
2) Mas o que é ainda pior são as subconsultas correlacionadas, especialmente com agregação. Isso é muito ruim.
Isso é ruim, claro. Mas isso é apenas um lado da moeda. Por outro lado, é muito bom, porque o problema claramente tem uma solução e uma necessidade, e pode ser melhorado.
Você não precisa ir a uma cartomante (C).
O plano de consulta não é tão complicado, mas é bastante indicativo:
Plano de implementação

As coisas mais interessantes e úteis, como sempre, estão no começo e no fim.
Loop aninhado (custo=935.84..479763226.18 linhas=3322 largura=135) (tempo real=31.536..8220420.295 linhas=8111656 loops=1)
Tempo de planejamento: 3.807 ms
Tempo de execução: 8222351.640 ms
O tempo de conclusão é superior a 2 horas.

Hipóteses falsas que levaram tempo

Hipótese 1 - O otimizador comete um erro e elabora um plano incorreto.

Para visualizar o plano de implementação, utilizaremos o site. https://explain.depesz.com/No entanto, o site não revelou nada de interessante ou útil. À primeira vista, não havia nada que pudesse realmente ajudar. Talvez a verificação completa tenha sido mínima. Vamos em frente.

Hipótese 2 - Impacto na base devido ao vácuo automático, necessidade de eliminar os freios.

Mas os serviços de autovacuum estão funcionando bem, sem processos de longa duração. Não há carga significativa. Precisamos procurar outra coisa.

Hipótese 3 - As estatísticas estão desatualizadas, tudo precisa ser recalculado.

Repito, não é isso. As estatísticas são atuais. O que, considerando a ausência de problemas com o sistema de vácuo automático, não é surpreendente.

Vamos começar a otimizar.

A tabela principal 'wdata' certamente não é pequena, com quase 3 milhões de registros.
E é de acordo com essa tabela que a Varredura Completa é realizada.

Condição de Hash: ((w."SPENT_ID" = s."SPENT_ID") E ((SubPlano 1) = s."SPENT_DATE"))
-> Varredura Sequencial em wdata w (custo=0.00..574151.49 linhas=26886249 largura=46) (tempo real=0.005..8153.565 linhas=26873950 loops=1)
Fazemos o que se espera: "vamos criar um índice e tudo vai correr bem".
Criou-se um índice no campo "SPENT_ID".
Resultado:
Plano de execução de consulta usando índice

E aí, isso ajudou?
Foi: 8 222 351.640 ms (um pouco mais de 2 horas)
Tornou-se: 6 985 431.575 ms (quase 2 horas)
Basicamente, são as mesmas maçãs, vistas de lado.
Relembrando os clássicos:
"Você tem um parecido com este, mas sem asas? Vamos continuar procurando."

Em princípio, isso poderia ser considerado um bom resultado — bem, não ótimo, mas aceitável. No mínimo, forneceríamos ao cliente um relatório completo, detalhando o que foi realizado e por que o trabalho realizado foi bom.
Mas, ainda assim, a decisão final está muito longe. Muito longe mesmo.

Agora vem a parte divertida: vamos continuar a otimizar e aprimorar a consulta.

O primeiro passo é usar o comando JOIN.

A consulta reescrita agora se parece com isto (Bom, pelo menos é mais bonito.):
Consulta usando JOINSELECIONE
p.“PARAMETER_ID” como parameter_id,
pd.«PD_NAME» COMO pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
com «LRM» COMO LRM,
w.«LOTID» COMO lotid,
com «RTD_VALUE» como valor_RTD,
com “LOWER_SPEC_LIMIT” COMO lower_spec_limit,
com “UPPER_SPEC_LIMIT” COMO upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“DATA_DE_GASTO” COMO data_de_gasto,
extrair(ano de "DATA_DE_GASTO") COMO ano,
extrair(mês de "DATA_GASTA") como mês,
s."REPORT_NAME" AS nome_do_relatório,
p.«STPM_NAME» COMO stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN spent s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
ONDE
s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30' AND
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN spent s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
LIGADO w2.«LRM» = w.«LRM» );
Tempo de planejamento: 2.486 ms
Tempo de execução: 1223680.326 ms

Então, o primeiro resultado.
Foi: 6.985.431,575 ms (quase 2 horas).
Tornou-se: 1.223.680,326 ms (pouco mais de 20 minutos).
Um bom resultado. Em princípio, poderíamos ter parado por aí. Mas é tão chato que não conseguimos parar.
Mbo

Passo dois: Elimine a subconsulta correlacionada

Texto da solicitação modificado:
Sem subconsulta correlacionadaSELECIONE
p.“PARAMETER_ID” como parameter_id,
pd.«PD_NAME» COMO pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
com «LRM» COMO LRM,
w.«LOTID» COMO lotid,
com «RTD_VALUE» como valor_RTD,
com “LOWER_SPEC_LIMIT” COMO lower_spec_limit,
com “UPPER_SPEC_LIMIT” COMO upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“DATA_DE_GASTO” COMO data_de_gasto,
extrair(ano de "DATA_DE_GASTO") COMO ano,
extrair(mês de "DATA_GASTA") como mês,
s."REPORT_NAME" AS nome_do_relatório,
p.«STPM_NAME» COMO stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN spent s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”)
FROM spent s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
AGRUPAR POR w2.«LRM»
) md em w.“LRM” = md.“LRM”
ONDE
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Tempo de planejamento: 2.291 ms
Tempo de execução: 165021.870 ms

Foi: 1.223.680,326 ms (pouco mais de 20 minutos).
Tornou-se: 165.021,870 ms (pouco mais de 2 minutos).
Isso é realmente muito bom.
No entanto, como dizem os ingleses,Mas sempre há um "mas"."Um resultado bom demais deve automaticamente levantar suspeitas. Algo está errado aqui."

A hipótese de refinar a consulta para remover a subconsulta correlacionada está correta. No entanto, são necessários alguns ajustes para garantir que o resultado final esteja correto.
Consequentemente, o primeiro resultado intermediário foi:
Consulta editada sem subconsulta correlacionadaSELECIONE
p.“PARAMETER_ID” como parameter_id,
pd.«PD_NAME» COMO pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
com «LRM» COMO LRM,
w.«LOTID» COMO lotid,
com «RTD_VALUE» como valor_RTD,
com “LOWER_SPEC_LIMIT” COMO lower_spec_limit,
com “UPPER_SPEC_LIMIT” COMO upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“DATA_DE_GASTO” COMO data_de_gasto,
extrair(ano de s.“DATA_DE_GASTO”) COMO ano,
extrair(mês de s.“DATA_DE_GASTO”) como mês,
s."REPORT_NAME" AS nome_do_relatório,
p.«STPM_NAME» COMO stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN spent s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
FROM spent s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
AGRUPAR POR w2.«LRM»
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” AND md.“LRM” = w.“LRM”
ONDE
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Tempo de planejamento: 3.192 ms
Tempo de execução: 208014.134 ms

Então, o que temos no final — o primeiro resultado aceitável que não nos envergonhamos de mostrar ao cliente:
Começou com: 8.222.351,640 ms (mais de 2 horas)
Consegui atingir o tempo de 1.223.680,326 ms (um pouco mais de 20 minutos).
Resultado (provisório): 208 014.134 ms (um pouco mais de 3 minutos).

Ótimo resultado.

Total

Poderíamos ter parado por aí.
MAS ...
O apetite surge ao comer. A jornada será dominada por quem a percorre. Qualquer resultado é temporário. Pare e você morre. E assim por diante.
Vamos continuar com a otimização.
Excelente ideia. Principalmente considerando que o cliente foi muito favorável.

Então, chegou a hora de redesenhar o banco de dados. A estrutura da consulta em si não pode mais ser otimizada (embora, como se vê, exista uma maneira de fazê-la funcionar de verdade). Mas focar na otimização e no desenvolvimento do design do banco de dados é uma ideia muito promissora. E, mais importante, interessante. Novamente, me lembro da minha juventude. Afinal, eu não me tornei um DBA imediatamente; cresci programando (BASIC, assembler, C, C++, Oracle, PL/SQL). Um assunto interessante, para um livro de memórias à parte.
No entanto, não nos deixemos distrair.

Assim,

Talvez o seccionamento nos ajude?
Aviso de spoiler: "Sim, ajudou, inclusive na otimização de desempenho."