Vous n'êtes pas identifié(e).
Bonjour,
J'ai une requête SELECT qui a de mauvaises performances, dues au choix, par l'optimiser, de nested loops sur plusieurs nœuds parce que le nombre de ligne est mal estimé.
En simplifiant la requête, j'ai pu isoler la jointure où l'estimation du nombre de lignes est trop basse, ce qui se répercute ensuite sur le reste de la requête.
Voici la jointure en question :
SELECT e.id_ech, d.id_point, pl.echelon, d.caracthab
FROM description d
INNER JOIN echantillon e USING (id_ech)
INNER JOIN point_lt pl USING (id_ech, id_point)
WHERE d.caracthab IS NULL;Et l'explain analyze correspondant:
Hash Join (cost=4891.64..10652.69 rows=5481 width=13) (actual time=48.476..105.321 rows=87137 loops=1)
Hash Cond: (d.id_ech = e.id_ech)
-> Hash Join (cost=4887.45..10633.48 rows=5481 width=17) (actual time=48.414..95.828 rows=87137 loops=1)
Hash Cond: ((pl.id_ech = d.id_ech) AND (pl.id_point = d.id_point))
-> Seq Scan on point_lt pl (cost=0.00..4571.05 rows=223805 width=11) (actual time=0.004..11.601 rows=223805 loops=1)
-> Hash (cost=3569.09..3569.09 rows=87891 width=10) (actual time=48.336..48.337 rows=87137 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4428kB
-> Seq Scan on description d (cost=0.00..3569.09 rows=87891 width=10) (actual time=0.009..31.108 rows=87137 loops=1)
Filter: (caracthab IS NULL)
Rows Removed by Filter: 84872
-> Hash (cost=2.97..2.97 rows=97 width=4) (actual time=0.056..0.056 rows=97 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on echantillon e (cost=0.00..2.97 rows=97 width=4) (actual time=0.011..0.030 rows=97 loops=1)
Planning Time: 0.530 ms
Execution Time: 107.281 ms
Y a-t-il un moyen d'améliorer la sélectivité du hash join (par la création de statistiques par exemple) ? Je sais le faire pour deux colonnes d'une même table, mais entre différentes tables, c'est faisable ?
Merci d'avance pour votre aide,
ced
Hors ligne
Non, ce n'est pas faisable sur des tables différentes. Ceci étant dit, supposons que l'estimation soit exacte, en quoi cela changerait-il le plan ? Le nombre de lignes serait revu à la hausse, ce qui favoriserait encore plus un Hash Join. Du coup, ici, sur ce plan spécifique, je ne vois pas en quoi une sélectivité plus proche de la réalité améliorerait les choses.
Donc ma question est assez simple : quel autre plan vous semblerait plus rapide ? et si oui, avez-vous essayé de le tester ?
Guillaume.
Hors ligne
Comme indiqué, cette requête n'est qu'une partie d'une plus grosse requête.
Or, la sous-estimation du nombre de lignes provoque, sur les opérations suivantes, une cascade de nested loops qui, eux, compte tenu de la sous-estimation du nombre de lignes, entraînent des performances déplorables... En désactivant le nested loop, la plus grosse requête prend moins d'une seconde, là où les nested loops allongent le délai à 23 secondes.
D'où ma recherche d'une solution pour améliorer l'estimation du nombre de lignes au niveau du hash join.
Hors ligne
Avec le vrai mauvais plan complet, je ne peux pas garantir qu'on puisse vous aider mais sans le vrai mauvais plan complet, je peux garantir qu'on n'arrivera à rien 
Guillaume.
Hors ligne
Voici une requête plus complète:
SELECT e.id_ech, d.id_point, p.npp, pl.echelon, p2.csa, d.caracthab, f.caracthab
FROM description d
INNER JOIN echantillon e USING (id_ech)
INNER JOIN point_lt pl USING (id_ech, id_point)
INNER JOIN point p USING (id_point)
INNER JOIN inv_prod.e2point p2 USING (npp)
INNER JOIN inv_prod.g3foret f USING (npp)
WHERE d.caracthab IS NULL;Et le plan d'exécution associé :
Nested Loop (cost=4892.91..16307.31 rows=452 width=34) (actual time=27.204..949.370 rows=79848 loops=1)
-> Hash Join (cost=4892.48..16099.82 rows=452 width=49) (actual time=27.192..601.904 rows=79848 loops=1)
Hash Cond: (d.id_ech = e.id_ech)
-> Nested Loop (cost=4888.30..16094.40 rows=452 width=53) (actual time=27.169..589.386 rows=79848 loops=1)
-> Nested Loop (cost=4887.88..13699.38 rows=5481 width=34) (actual time=27.154..248.767 rows=87137 loops=1)
Join Filter: (d.id_point = p.id_point)
-> Hash Join (cost=4887.45..10633.48 rows=5481 width=21) (actual time=27.139..95.810 rows=87137 loops=1)
Hash Cond: ((pl.id_ech = d.id_ech) AND (pl.id_point = d.id_point))
-> Seq Scan on point_lt pl (cost=0.00..4571.05 rows=223805 width=11) (actual time=0.002..12.972 rows=223805 loops=1)
-> Hash (cost=3569.09..3569.09 rows=87891 width=10) (actual time=27.098..27.099 rows=87137 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4428kB
-> Seq Scan on description d (cost=0.00..3569.09 rows=87891 width=10) (actual time=0.004..17.110 rows=87137 loops=1)
Filter: (caracthab IS NULL)
Rows Removed by Filter: 84872
-> Index Scan using pk_point on point p (cost=0.43..0.55 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=87137)
Index Cond: (id_point = pl.id_point)
-> Index Scan using pkg3foret on g3foret f (cost=0.42..0.44 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=87137)
Index Cond: (npp = (p.npp)::bpchar)
-> Hash (cost=2.97..2.97 rows=97 width=4) (actual time=0.020..0.020 rows=97 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on echantillon e (cost=0.00..2.97 rows=97 width=4) (actual time=0.006..0.012 rows=97 loops=1)
-> Index Scan using pke2point on e2point p2 (cost=0.42..0.46 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=79848)
Index Cond: (npp = (p.npp)::bpchar)
Planning Time: 1.343 ms
Execution Time: 951.515 ms
On voit que le nested loop est privilégié parce que l'estimation du nombre de lignes est sous-évaluée. Je n'ai pas mis toutes les jointures de la requête initiale, mais plus il y a de jointures, plus les nested loop s'empilent et plus ça se dégrade.
En tout cas, merci pour le coup de main.
Hors ligne
Vous pouvez toujours tenter de créer une statistique étendue sur les colonnes id_ech et id_point de la table point_lt et de la table description, mais je ne sais pas si PostgreSQL saura les utiliser dans ce ce contexte. Néanmoins, c'est un test que je ferais.
Guillaume.
Hors ligne
Merci pour cette piste.
Après essai, il s'avère que ça ne change rien à l'estimation des lignes de la jointure, et donc le nested loop reste privilégié pour les nœuds suivants.
Hors ligne
Vous pourriez essayer la même chose sur description(id_ech, id_point), mais à priori cela ne règlerait pas le soucis, les statistiques étandues n'étant pas utilisées sur les jointures.
En l'état la seule possibilité serait d'utiliser pg_hint_plans, et/ou de tester le patch disponible à https://www.postgresql.org/message-id/7 … grespro.ru .
Julien.
https://rjuju.github.io/
Hors ligne
Merci pour ce retour. L'ajout de statistiques sur description ne change effectivement rien au problème de sélectivité.
Pour le test du patch, comme je suis sur un serveur de production en v12, je vais éviter pour l'instant. Mais le problème est visiblement connu.
Hors ligne