Vous souvenez-vous comment tout a commencé. Tout était pour la première fois et encore

À propos de la façon dont j'ai dû gérer l'optimisation des requêtes PostgreSQL et de ce qui en est ressorti.
Pourquoi avez-vous dû? Oui, parce que les 4 années précédentes, tout fonctionnait tranquillement, calmement, comme une horloge qui tournait.
Comme épigraphe.

Basé sur des événements réels.
Tous les noms ont été changés, les coïncidences sont aléatoires.

Lorsqu'un certain résultat est atteint, il est toujours intéressant de se rappeler quelle a été l'impulsion du début, comment tout a commencé.

Ainsi, ce qui s'est passé en conséquence est brièvement décrit dans l'article "La synthèse comme l'une des méthodes pour améliorer les performances de PostgreSQL».

Il sera probablement intéressant de recréer l'enchaînement des événements précédents.
L'historique a conservé la date de début exacte — 2018-09-10 18:02:48.
De plus, dans l'histoire, il y a une demande à partir de laquelle tout a commencé :
Demande de problèmeSELECT
p. "PARAMETER_ID" en tant que parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS lower_spec_limit,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" comme type_calcul,
s."SPENT_NAME" COMME nom_passé,
s."SPENT_DATE" comme date_passée,
extrait(année de "SPENT_DATE") AS année,
extrait(mois de "SPENT_DATE") comme mois,
s."REPORT_NAME" COMME nom_rapport,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS nom_param_client
DE wdataw,
passé s,
pmtrp,
dépensé_pdsp,
pd pd
OÙ s."SPENT_ID" = w."SPENT_ID"
ET p."ID_PARAMETRE" = w."ID_PARAMETRE"
ET s."SPENT_ID" = sp."SPENT_ID"
AND pd. "PD_ID" = sp. "PD_ID"
ET s."SPENT_DATE" >= '2018-07-01' ET s."SPENT_DATE" <= '2018-09-30'
et s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
DE passé s2,
wdonnées w2
OÙ s2."SPENT_ID" = w2."SPENT_ID"
ET w2."LRM" = w."LRM");

Description du problème, standard prévisible - "Tout va mal. Dites-moi quel est le problème."
Je me suis immédiatement souvenu d'une blague de l'époque des lecteurs de 3 pouces et demi :

Le lamer vient au hacker.
- Rien ne fonctionne pour moi, dis-moi où est le problème.
-Dans l'ADN...

Mais, bien sûr, ce n'est pas la façon de résoudre les incidents de performance. "Nous ne pouvons pas être compris" (Avec). Besoin de comprendre.
Eh bien, creusons. Peut-être que cela s'accumulera en conséquence.

l'investissement a commencé

Donc, ce qui peut être vu immédiatement à l'œil nu, sans même recourir à l'aide d'EXPLAIN.
1) Les JOIN ne sont pas utilisés. C'est mauvais, surtout si le nombre de connexions est supérieur à un.
2) Mais ce qui est encore pire - une sous-requête corrélée, de plus, avec l'agrégation. C'est très mauvais.
C'est mauvais, bien sûr. Mais ce n'est que d'une part. En revanche, c'est très bien, car le problème a clairement une solution et la demande peut être améliorée.
N'allez pas chez la diseuse de bonne aventure (C).
Le plan de requête n'est pas si compliqué, mais assez indicatif :
Plan d'exécution

Le plus intéressant et utile, comme d'habitude, au début et à la fin.
Boucle imbriquée (coût=935.84..479763226.18 lignes=3322 largeur=135) (temps réel=31.536..8220420.295 lignes=8111656 boucles=1)
Temps de planification : 3.807 ms
Temps d'exécution : 8222351.640 ms
Le temps d'exécution est supérieur à 2 heures.

De fausses hypothèses qui ont pris du temps

Hypothèse 1- L'optimiseur se trompe, construit le mauvais plan.

Pour visualiser le plan d'exécution, nous utiliserons le site https://explain.depesz.com/. Cependant, le site n'a rien montré d'intéressant ou d'utile. Au premier et au deuxième coup d'œil - rien qui puisse vraiment aider. Sauf si - Full Scan est minime. Poursuivre.

Hypothèse 2 - Impact sur la base du côté de l'autovacuum, vous devez vous débarrasser des freins.

Mais, les démons autovacuum se comportent bien, il n'y a pas de processus longs. Toute charge sérieuse - non. Faut chercher autre chose.

Hypothèse 3-Les statistiques sont obsolètes, vous devez recalculer tout vole

Encore une fois, pas ça. Les statistiques sont à jour. Ce qui, étant donné l'absence de problèmes avec l'autovacuum, n'est pas surprenant.

Commencer à optimiser

La table principale 'wdata' n'est certainement pas petite, près de 3 millions d'enregistrements.
Et c'est sur cette table que se déroule Full Scan.

Cond de hachage : ((w."SPENT_ID" = s."SPENT_ID") AND ((SubPlan 1) = s."SPENT_DATE"))
-> Balayage séquentiel sur wdata w (cost=0.00..574151.49 rows=26886249 width=46) (actual time=0.005..8153.565 rows=26873950 loops=1)
Nous agissons en standard : « faisons un index et tout s'envole ».
Fait un index sur le champ "SPENT_ID"
Résultat:
Plan d'exécution de la requête à l'aide d'un index

Eh bien, cela a-t-il aidé?
C'était: 8 222 351.640 millisecondes (un peu plus de 2 heures)
C'est devenu: 6 985 431.575 ms (presque 2 heures)
En général, les mêmes pommes, vue de côté.
Rappelons les classiques :
« Avez-vous le même, mais sans ailes ? Cherchera".

En principe, cela pourrait être appelé un bon résultat, enfin pas bon, mais acceptable. À tout le moins, fournissez un grand rapport au client décrivant tout ce qui a été fait et pourquoi ce qui est fait est bon.
Cependant, la décision finale est encore loin. Très loin.

Et maintenant, la chose la plus intéressante - nous continuons à optimiser, nous allons peaufiner la requête

Première étape - utilisez JOIN

Requête réécrite, ressemble maintenant à ceci (bien au moins plus jolie):
Requête utilisant JOINSELECT
p. "PARAMETER_ID" en tant que parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS lower_spec_limit,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" comme type_calcul,
s."SPENT_NAME" COMME nom_passé,
s."SPENT_DATE" comme date_passée,
extrait(année de "SPENT_DATE") AS année,
extrait(mois de "SPENT_DATE") comme mois,
s."REPORT_NAME" COMME nom_rapport,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS nom_param_client
FROM wdata w INNER JOIN dépensé s ON w."SPENT_ID"=s."SPENT_ID"
INNER JOIN pmtr p ON p."ID_PARAMETRE" = w."ID_PARAMETRE"
INNER JOIN spp_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
OÙ
s."SPENT_DATE" >= '2018-07-01' ET s."SPENT_DATE" <= '2018-09-30'ET
s."SPENT_DATE" = (SÉLECTIONNER MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN passé s2 ON w2."SPENT_ID"=s2."SPENT_ID"
JOINTURE INTÉRIEURE wdata w
ON w2."LRM" = w."LRM" );
Temps de planification : 2.486 ms
Temps d'exécution : 1223680.326 ms

Voici donc le premier résultat.
C'était: 6 985 431.575 ms (presque 2 heures).
C'est devenu: 1 223 680.326 ms (un peu plus de 20 minutes).
Bon résultat. En principe, encore une fois, il serait possible de s'arrêter là. Mais si inintéressant, vous ne pouvez pas vous arrêter.
Mbo

Deuxième étape - Débarrassez-vous de la sous-requête corrélée

Texte de la demande modifié :
Aucune sous-requête corréléeSELECT
p. "PARAMETER_ID" en tant que parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS lower_spec_limit,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" comme type_calcul,
s."SPENT_NAME" COMME nom_passé,
s."SPENT_DATE" comme date_passée,
extrait(année de "SPENT_DATE") AS année,
extrait(mois de "SPENT_DATE") comme mois,
s."REPORT_NAME" COMME nom_rapport,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS nom_param_client
FROM wdata w INNER JOIN dépensé s ON s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."ID_PARAMETRE" = w."ID_PARAMETRE"
INNER JOIN spp_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
JOINTURE INTERNE (SELECT w2."LRM", MAX(s2."SPENT_DATE")
FROM passé s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUPER PAR w2.LRM
) md sur w. "LRM" = md. "LRM"
OÙ
s."SPENT_DATE" >= '2018-07-01' ET s."SPENT_DATE" <= '2018-09-30' ;
Temps de planification : 2.291 ms
Temps d'exécution : 165021.870 ms

C'était: 1 223 680.326 ms (un peu plus de 20 minutes).
C'est devenu: 165 021.870 ms (un peu plus de 2 minutes).
C'est déjà très bien.
Cependant, comme disent les Anglais,Mais il y a toujours un mais". Un trop bon résultat devrait automatiquement éveiller les soupçons. Quelque chose cloche ici.

L'hypothèse de correction de la requête afin de se débarrasser de la sous-requête corrélée est correcte. Mais il a besoin d'un peu de peaufinage pour obtenir le bon résultat final.
En conséquence, le premier résultat intermédiaire :
Requête modifiée sans sous-requête corréléeSELECT
p. "PARAMETER_ID" en tant que parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_value,
w. "LOWER_SPEC_LIMIT" AS lower_spec_limit,
w. "UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" comme type_calcul,
s."SPENT_NAME" COMME nom_passé,
s."SPENT_DATE" comme date_passée,
extrait(année de s. "SPENT_DATE") AS année,
extrait(mois de s. "SPENT_DATE") comme mois,
s."REPORT_NAME" COMME nom_rapport,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS nom_param_client
FROM wdata w INNER JOIN dépensé s ON s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."ID_PARAMETRE" = w."ID_PARAMETRE"
INNER JOIN spp_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 passé s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUPER PAR w2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" ET md."LRM" = w."LRM"
OÙ
s."SPENT_DATE" >= '2018-07-01' ET s."SPENT_DATE" <= '2018-09-30' ;
Temps de planification : 3.192 ms
Temps d'exécution : 208014.134 ms

Donc, ce que nous avons comme résultat est le premier résultat acceptable, que nous n'avons pas honte de montrer au client :
Commencé avec : 8 222 351.640 ms (plus de 2 heures)
Atteint : 1 223 680.326 ms (un peu plus de 20 minutes).
Résultat (intermédiaire): 208 014.134 ms (un peu plus de 3 minutes).

Excellent résultat.

Total

Cela aurait pu s'arrêter.
MAIS ...
L'appétit vient en mangeant. La route sera maîtrisée en marchant. Tout résultat est intermédiaire. Arrêté net. Etc.
Continuons l'optimisation.
Bonne idée. D'autant plus que le client n'était même pas contre. Et même fortement - pour.

Il est donc temps de repenser la base de données. La structure de la requête elle-même ne peut plus être optimisée (bien que, comme il s'est avéré plus tard, il existe une option pour que tout vole vraiment). Mais maintenant pour optimiser et développer le design de la base de données, c'est déjà une idée très prometteuse. Et surtout intéressant. Encore une fois, souvenez-vous de la jeunesse. Après tout, je ne suis pas immédiatement devenu DBA, je suis issu de programmeurs (basic, assembleur, si, si doublement plus, oracle, plsql). Un sujet intéressant, bien sûr, pour des mémoires séparées ;-).
Cependant, ne nous égarons pas.

ainsi,

Et peut-être que le sectionnement nous aidera ?
Spoiler - "Oui, ça a aidé, et en optimisant les performances, y compris."

Mais c'est une histoire complètement différente ...

À suivre…

Source: habr.com