Forums PostgreSQL.fr

ced

Membre

A quoi sert le nested loop ?

Bonjour,

Je me pose une question sur l'utilité du "nested loop" dans la stratégie choisie par l'optimiseur de requête de PostgreSQL.
Je m'explique : je constate très souvent des requêtes drastiquement longues (plus de 30 secondes pour 20 000 lignes) pour lesquelles le temps devient inférieur à 1 seconde dès lors qu'on passe le paramètre enable_nestloop à FALSE. La plupart du temps, quand j'ai des requêtes longues, c'est comme ça que je résout le problème... et l'écart est énorme avant et après le changement de ce paramètre.

Quelle est l'utilité de cette stratégie ?
Pourquoi l'optimiseur y a-t-il facilement recours ?
Quels peuvent être les risques si, au niveau de la base de donnée, je passe ce paramètre à FALSE par défaut ?

Merci d'avance pour vos éclaircissements.

Cédric

rjuju

Administrateur

Re : A quoi sert le nested loop ?

Bonjour,

L'intérêt des nested loop est principalement des jointures pour un faible nombre d'enregistrements ou les requêtes ayant une clause limit, car il est peut commencer à renvoyer des données dès le démarrage. Les autres types de jointures (merge join et hash join) nécessitent un traitement préalable potentiellement couteux (trier toutes les lignes ou créer une table de hashage). Je vous déconseille fortement de désactiver de manière globale les nested loop, sinon vous risquez de vous retrouver avec des écarts de performances inverses pour d'autres requêtes. Commencez par voir si les statistiques annoncées correspondent à la réalité, et également à vérifier que votre configuration correspond bien aux performances de votre serveur.

---

Julien.
https://rjuju.github.io/

gleu

Administrateur

Re : A quoi sert le nested loop ?

L'intérêt principal du Nested Loop est quand même de pouvoir traiter des jointures utilisant des opérateurs autres que l'égalité. Autant l'opérateur d'égalité permet un MergeJoin ou un HashJoin, autant les autres opérateurs ne sont pas compatibles.

Mais bon, comme dit Julien, désactiver les NestedLoop n'est pas la bonne solution. Il serait intéressant de voir pour une requête son plan d'exécution pour pouvoir mieux vous guider (un EXPLAIN ANALYZE, pas un EXPLAIN seul qui n'a que très peu d'intérêt).

---

Guillaume.

ced

Membre

Re : A quoi sert le nested loop ?

Bonjour à tous les deux et merci pour l'aide que vous m'apportez.

Voici le résultat d'un EXPLAIN ANALYZE sans avoir désactivé les NestedLoop :
QUERY PLAN
Nested Loop  (cost=6.42..2185.25 rows=1 width=302) (actual time=7.269..196975.150 rows=17816 loops=1)
  Join Filter: ((p5.absc = n.absc) AND (p5.ord = n.ord))
  Rows Removed by Join Filter: 1024214276
  ->  Seq Scan on pts5 p5  (cost=0.00..1700.00 rows=1 width=148) (actual time=0.033..51.698 rows=21788 loops=1)
        Filter: ((tirmax > 1) AND ("poi$" = '1'::bpchar) AND (nincref = 7))
        Rows Removed by Filter: 59142
  ->  Bitmap Heap Scan on noeuds n  (cost=6.42..481.12 rows=275 width=158) (actual time=2.517..5.569 rows=47009 loops=21788)
        Recheck Cond: (zpop = '1'::text)
        Heap Blocks: exact=12353796
        ->  Bitmap Index Scan on nd_zpop_idx  (cost=0.00..6.35 rows=275 width=0) (actual time=2.469..2.469 rows=47009 loops=21788)
              Index Cond: (zpop = '1'::text)
Planning time: 0.490 ms
Execution time: 196976.834 ms

Et voici le résultat de l'EXPLAIN ANALYZE après désactivation des NestedLoop :
QUERY PLAN
Hash Join  (cost=485.25..2185.34 rows=1 width=302) (actual time=25.282..62.898 rows=17816 loops=1)
  Hash Cond: ((p5.absc = n.absc) AND (p5.ord = n.ord))
  ->  Seq Scan on pts5 p5  (cost=0.00..1700.00 rows=1 width=148) (actual time=0.040..15.750 rows=21788 loops=1)
        Filter: ((tirmax > 1) AND ("poi$" = '1'::bpchar) AND (nincref = 7))
        Rows Removed by Filter: 59142
  ->  Hash  (cost=481.12..481.12 rows=275 width=158) (actual time=25.226..25.226 rows=47009 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 3489kB
        ->  Bitmap Heap Scan on noeuds n  (cost=6.42..481.12 rows=275 width=158) (actual time=5.866..15.316 rows=47009 loops=1)
              Recheck Cond: (zpop = '1'::text)
              Heap Blocks: exact=567
              ->  Bitmap Index Scan on nd_zpop_idx  (cost=0.00..6.35 rows=275 width=0) (actual time=5.745..5.745 rows=47009 loops=1)
                    Index Cond: (zpop = '1'::text)
Planning time: 0.187 ms
Execution time: 63.409 ms

N'étant pas DBA et n'ayant pas ces connaissances, je ne suis pas trop au fait de la lecture de ces résultats.

rjuju

Administrateur

Re : A quoi sert le nested loop ?

Le problème ici est que les statistiques sont fausses, et postgres se retrouve à traiter beaucoup plus de ligne que prévu.

Pouvez-vous essayer de lancer les requêtes suivantes :

ANALYZE pts5;
ANALYZE noeuds;

et de rééxcuter l'explain analyze ?

---

Julien.
https://rjuju.github.io/

gleu

Administrateur

Re : A quoi sert le nested loop ?

Pour détailler un peu plus ce que dit Julien, dans l'exemple avec le Nested Loop, PostgreSQL suppose ne récupérer qu'une seule ligne dans le parcours de pts5. Dans ce cas, une Nested Loop est le plus simple et certainement le plus performant. Cependant, en réalité, il récupère 21788 lignes, et  là, clairement, un Nested Loop est la plus mauvaise option. Donc, comme le dit Julien, les statistiques sont fausses. Il faudrait recalculer les statistiques (commandes ANALYZE ci-dessus), puis voir si cela impacte le plan d'exécution. Si ce n'est pas le cas, il serait intéressant de connaître les statistiques sur les colonnes filtrées (tirmax, "poi$", nincref), le nombre de lignes de la table et le nombre de lignes résultant des différents filtres.

---

Guillaume.

ced

Membre

Re : A quoi sert le nested loop ?

Il va vraiment falloir que j'apprenne à lire un explain...

Voici le plan de requête à l'issue d'un ANALYZE sur chacune des tables (la requête s'exécute très rapidement) :

Merge Join  (cost=9149.08..9706.91 rows=1164 width=84) (actual time=92.203..110.693 rows=17816 loops=1)
  Merge Cond: ((p5.absc = n.absc) AND (p5.ord = n.ord))
  ->  Sort  (cost=4244.33..4306.88 rows=25023 width=46) (actual time=33.797..35.399 rows=21788 loops=1)
        Sort Key: p5.absc, p5.ord
        Sort Method: quicksort  Memory: 2471kB
        ->  Seq Scan on pts5 p5  (cost=0.00..2416.28 rows=25023 width=46) (actual time=2.037..19.254 rows=21788 loops=1)
              Filter: ((tirmax > 1) AND ("poi$" = '1'::bpchar) AND (nincref = 7))
              Rows Removed by Filter: 59142
  ->  Sort  (cost=4904.66..5022.27 rows=47043 width=42) (actual time=58.393..63.883 rows=46758 loops=1)
        Sort Key: n.absc, n.ord
        Sort Method: external sort  Disk: 2664kB
        ->  Seq Scan on noeuds n  (cost=0.00..1253.72 rows=47043 width=42) (actual time=0.020..11.331 rows=47009 loops=1)
              Filter: (zpop = '1'::text)
              Rows Removed by Filter: 7929
Planning time: 0.693 ms
Execution time: 111.634 ms

rjuju

Administrateur

Re : A quoi sert le nested loop ?

La cardinalité de la jointure est encore fausse d'un facteur 10, mais au moins les cardinalités sur les deux parcours de tables sont bons, ce qui explique que postgres choisit maintenant un plan beaucoup plus adapté.

Quelle version de postgres utilisez vous, et l'autovacuum est-il bien activé ?

---

Julien.
https://rjuju.github.io/

ced

Membre

Re : A quoi sert le nested loop ?

La version de PostgreSQL sur ce serveur est la 9.4.4.
L'autovacuum est activé.

Mais les deux tables en question sont des tables temporaires. Ce qui explique peut-être une partie du problème, non ?
Précision : ces tables ont tout de même une clé primaire et sont indexées.

Dernière modification par ced (13/01/2016 15:41:43)

rjuju

Administrateur

Re : A quoi sert le nested loop ?

Oui, s'il s'agit de tables temporaires l'autovacuum ne peut pas les voir et ne peut donc pas lancer de mainteance dessus. Il s'agit d'un des cas où il est généralement nécessaire de procéder à des VACUUM et/ou ANALYZE manuellement.

---

Julien.
https://rjuju.github.io/

ced

Membre

Re : A quoi sert le nested loop ?

Alors ceci explique cela.

Je sais à présent que dès qu'on utilise des tables temporaires, il est bon de procéder manuellement à un VACUUM ANALYZE pour ne pas perdre l'optimiseur de requêtes.

Merci pour toute votre aide.