Sobre como tivemos que otimizar a consulta PostgreSQL e o que resultou disso tudo.
Por que você teve que fazer isso? Sim, porque nos últimos 4 anos tudo funcionou silenciosamente, com calma, como o tique-taque de um relógio.
Como uma epígrafe.
Baseado em eventos reais.
Todos os nomes foram alterados, as coincidências são aleatórias.
Quando se alcança determinado resultado, é sempre interessante lembrar qual foi o impulso para o início, onde tudo começou.
Então, o que aconteceu como resultado é brevemente descrito no artigo “".
Provavelmente será interessante recriar a cadeia de eventos anteriores.
O histórico salvou a data de início exata - 2018/09/10 18:02:48.
Além disso, na história há um pedido com o qual tudo começou:
Solicitação de problemaSELECIONE
p.“PARAMETER_ID” como parâmetro_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS número_da_cliente,
w. "LRM" COMO LRM,
w. "LOTID" COMO lotido,
w.“RTD_VALUE” COMO RTD_value,
w.“LOWER_SPEC_LIMIT” COMO limite_de_especificação inferior,
w.“UPPER_SPEC_LIMIT” COMO limite_de_especificação superior,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS gasto_nome,
s.“SPENT_DATE” COMO data_gasto,
extrair(ano de "SPENT_DATE") AS ano,
extrair (mês de "SPENT_DATE") como mês,
s."REPORT_NAME" AS nome_do_relatório,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS clienteparam_name
DE wdata w,
gastou,
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”)
COM gasto s2,
dados w2
ONDE s2.“SPENT_ID” = w2.“SPENT_ID”
E w2.“LRM” = w.“LRM”);
A descrição do problema é previsivelmente padrão - “Tudo está ruim. Diga-me qual é o problema.
Lembrei-me imediatamente de uma anedota da época das unidades de 3 polegadas e meia:
O lamer chega ao hacker.
-Nada funciona para mim, diga-me onde está o problema.
-No ADN...
Mas é claro que esta não é a maneira de resolver incidentes de desempenho. “Eles podem não nos entender" (Com). Precisamos descobrir isso.
Bem, vamos cavar. Talvez algo se acumule como resultado.
Investigação iniciada
Então, o que pode ser visto imediatamente a olho nu, sem sequer recorrer ao EXPLICAR.
1) JOINs não são usados. Isso é ruim, especialmente se o número de conexões for maior que um.
2) Mas o que é ainda pior são as subconsultas correlacionadas, além disso, com agregação. Isso é muito ruim.
Isso é ruim, claro. Mas isso é apenas por um lado. Por outro lado, isso é muito bom, porque o problema tem claramente uma solução e uma solicitação que pode ser melhorada.
Não vá a uma cartomante (C).
O plano de consulta não é tão complicado, mas é bastante indicativo:
Plano de execução
O mais interessante e útil, como sempre, está no início 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 constrói o plano errado.
Para visualizar o plano de execução, utilizaremos o site . Porém, o site não mostrou nada de interessante ou útil. À primeira e à segunda vista, não há nada que possa realmente ajudar. É possível que a verificação completa seja mínima. Vá em frente.
Hipótese 2-Impacto na base pelo lado do autovácuo, é preciso se livrar dos freios.
Mas os daemons autovacuum se comportam bem, não há processos demorados. Nenhuma carga séria. Precisamos procurar outra coisa.
Hipótese 3 – As estatísticas estão desatualizadas, tudo precisa ser recalculado
Novamente, não é isso. As estatísticas estão atualizadas. O que, dada a falta de problemas com o vácuo automático, não é surpreendente.
Vamos começar a otimizar
A tabela principal 'wdata' certamente não é pequena, quase 3 milhões de registros.
E é essa tabela que o Full Scan segue.
Condição de hash: ((w."SPENT_ID" = s."SPENT_ID") AND ((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 padrão: “vamos lá, vamos fazer um índice e tudo vai voar”.
Criado um índice no campo “SPENT_ID”
Resultado:
Plano de execução de consulta usando índice
Bem, isso ajudou?
Foi: 8ms (um pouco mais de 2 horas)
Tornou-se: 6 985 431.575 ms (quase 2 horas)
Em geral, as mesmas maçãs, vista lateral.
Vamos relembrar os clássicos:
“Você tem o mesmo, mas sem asas? Procurará".
Em princípio, isso poderia ser considerado um bom resultado, bem, não é bom, mas aceitável. No mínimo, forneça um grande relatório ao cliente descrevendo o quanto foi feito e por que o que foi feito foi bom.
Mas ainda assim, a decisão final ainda está longe. Muito longe.
E agora o mais interessante - continuamos otimizando, vamos aprimorar a solicitação
Passo Um - Use JOIN
A solicitação reescrita agora se parece com isto (bem, pelo menos mais bonito):
Consultar usando JOINSELECIONE
p.“PARAMETER_ID” como parâmetro_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS número_da_cliente,
w. "LRM" COMO LRM,
w. "LOTID" COMO lotido,
w.“RTD_VALUE” COMO RTD_value,
w.“LOWER_SPEC_LIMIT” COMO limite_de_especificação inferior,
w.“UPPER_SPEC_LIMIT” COMO limite_de_especificação superior,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS gasto_nome,
s.“SPENT_DATE” COMO data_gasto,
extrair(ano de "SPENT_DATE") AS ano,
extrair (mês de "SPENT_DATE") como mês,
s."REPORT_NAME" AS nome_do_relatório,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS clienteparam_name
FROM wdata w INNER JOIN gastou s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN gasto_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' E s.“SPENT_DATE” <= '2018/09/30'AND
s.“SPENT_DATE” = (SELECIONE MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN gastou s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
Tempo de planejamento: 2.486 ms
Tempo de execução: 1223680.326 ms
Então, o primeiro resultado.
Foi: 6 ms (quase 985 horas).
Tornou-se: 1 223 680.326 ms (pouco mais de 20 minutos).
Bom resultado. Em princípio, mais uma vez, poderíamos parar por aí. Mas é tão desinteressante que você não consegue parar.
Mbo
Etapa dois – livrar-se da subconsulta correlacionada
Texto da solicitação alterado:
Sem subconsulta correlacionadaSELECIONE
p.“PARAMETER_ID” como parâmetro_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS número_da_cliente,
w. "LRM" COMO LRM,
w. "LOTID" COMO lotido,
w.“RTD_VALUE” COMO RTD_value,
w.“LOWER_SPEC_LIMIT” COMO limite_de_especificação inferior,
w.“UPPER_SPEC_LIMIT” COMO limite_de_especificação superior,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS gasto_nome,
s.“SPENT_DATE” COMO data_gasto,
extrair(ano de "SPENT_DATE") AS ano,
extrair (mês de "SPENT_DATE") como mês,
s."REPORT_NAME" AS nome_do_relatório,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS clienteparam_name
FROM wdata w INNER JOIN gastou s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN gasto_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (SELECIONE w2.“LRM”, MAX(s2.“SPENT_DATE”)
FROM gasto s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPO 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 ms (pouco mais de 021.870 minutos).
Isso já é muito bom.
No entanto, como dizem os britânicos "Mas, há sempre um mas" Um resultado demasiado bom deve automaticamente levantar suspeitas. Algo está errado aqui.
A hipótese de corrigir a consulta para se livrar da subconsulta correlacionada está correta. Mas é preciso ajustar um pouco para que o resultado final fique correto.
Como resultado, o primeiro resultado intermediário:
Consulta editada sem subconsulta correlacionadaSELECIONE
p.“PARAMETER_ID” como parâmetro_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS número_da_cliente,
w. "LRM" COMO LRM,
w. "LOTID" COMO lotido,
w.“RTD_VALUE” COMO RTD_value,
w.“LOWER_SPEC_LIMIT” COMO limite_de_especificação inferior,
w.“UPPER_SPEC_LIMIT” COMO limite_de_especificação superior,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS gasto_nome,
s.“SPENT_DATE” COMO data_gasto,
extrair(ano de s.“SPENT_DATE”) AS ano,
extrair(mês de s.“SPENT_DATE”) como mês,
s."REPORT_NAME" AS nome_do_relatório,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS clienteparam_name
FROM wdata w INNER JOIN gastou s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN gasto_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 gasto s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPO 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 obtemos é o primeiro resultado aceitável, que não tem vergonha de mostrar ao cliente:
Começou com: 8 222 351.640 ms (mais de 2 horas)
Conseguimos atingir: 1 ms (pouco mais de 223 minutos).
Resultado (provisório): 208 ms (pouco mais de 014.134 minutos).
Ótimo resultado.
Total
Poderíamos ter parado por aí.
MAS ...
O apetite vem com a alimentação. Quem caminha dominará a estrada. Qualquer resultado é intermediário. Parou e morreu. Etc.
Vamos continuar a otimização.
Boa ideia. Principalmente considerando que o cliente nem se importou. E até fortemente por isso.
Então, é hora de reformular o banco de dados. A estrutura da consulta em si não pode mais ser otimizada (embora, como descobrimos mais tarde, haja uma opção para garantir que tudo realmente falhe). Mas começar a otimizar e desenvolver o design do banco de dados já é uma ideia muito promissora. E o mais importante é interessante. Mais uma vez, lembre-se de sua juventude. Não me tornei DBA imediatamente, cresci como programador (BASIC, assembler, C, double-plus C, Oracle, plsql). Um tópico interessante, claro, para um livro de memórias separado ;-).
No entanto, não vamos nos distrair.
Assim,
Ou talvez o particionamento nos ajude?
Spoiler - “Sim, ajudou, inclusive na otimização do desempenho.”
Mas essa é uma história completamente diferente ...
Continua…
Fonte: habr.com