Forums PostgreSQL.fr

ced

Membre

Estimation des lignes issues d'une jointure

Bonjour,

J'ai la requête suivante :

SELECT p2.npp, round(p2.xgps :: numeric, 8) as xgps, round(p2.ygps :: numeric, 8) as ygps, echelon_init as echelon
FROM inv_prod.e2point p2
INNER JOIN metaifn.abmode m2 ON m2.mode = echelon_init and m2.unite = 'EX'
INNER JOIN inv_prod.unite_ech ue ON p2.id_unite = ue.id_unite
INNER JOIN inv_prod.echantillon e ON ue.id_ech = e.id_ech
WHERE e.incref = 11
AND p2.tir2 = '1'
AND e.inv IN ('R', 'T');

Dont la sortie d'EXPLAIN (ANALYZE) donne ceci :

Hash Join  (cost=22902.45..31314.85 rows=16 width=84) (actual time=126.721..159.288 rows=7518 loops=1)
  Output: p2.npp, round((p2.xgps)::numeric, 8), round((p2.ygps)::numeric, 8), p2.echelon_init
  Hash Cond: (p2.echelon_init = (m2.mode)::bpchar)
  Buffers: shared hit=4861
  ->  Hash Join  (cost=14920.32..23079.09 rows=16887 width=36) (actual time=126.122..140.296 rows=7518 loops=1)
        Output: p2.npp, p2.xgps, p2.ygps, p2.echelon_init
        Hash Cond: (ue.id_unite = p2.id_unite)
        Buffers: shared hit=4861
        ->  Nested Loop  (cost=0.43..7442.72 rows=146030 width=4) (actual time=0.035..30.451 rows=100184 loops=1)
              Output: ue.id_unite
              Buffers: shared hit=617
              ->  Seq Scan on inv_prod.echantillon e  (cost=0.00..1.58 rows=3 width=4) (actual time=0.018..0.035 rows=4 loops=1)
                    Output: e.id_ech, e.nom_ech, e.type_enquete, e.type_unites, e.usite, e.site, e.surf_dom, e.deb_temp, e.fin_temp, e.proprietaire, e.phase_stat, e.id_parent, e.taille_ech, e.url_script, e.cyc, e.incref, e.inv
                    Filter: ((e.inv = ANY ('{R,T}'::bpchar[])) AND (e.incref = 11))
                    Rows Removed by Filter: 35
                    Buffers: shared hit=1
              ->  Index Only Scan using unite_ech_pkey on inv_prod.unite_ech ue  (cost=0.43..1993.61 rows=48677 width=8) (actual time=0.011..4.669 rows=25046 loops=4)
                    Output: ue.id_ech, ue.famille, ue.format, ue.id_unite
                    Index Cond: (ue.id_ech = e.id_ech)
                    Heap Fetches: 0
                    Buffers: shared hit=616
        ->  Hash  (cost=13360.55..13360.55 rows=124747 width=40) (actual time=90.332..90.332 rows=124537 loops=1)
              Output: p2.npp, p2.xgps, p2.ygps, p2.echelon_init, p2.id_unite
              Buckets: 131072  Batches: 1  Memory Usage: 10506kB
              Buffers: shared hit=4244
              ->  Bitmap Heap Scan on inv_prod.e2point p2  (cost=2375.21..13360.55 rows=124747 width=40) (actual time=10.731..63.092 rows=124537 loops=1)
                    Output: p2.npp, p2.xgps, p2.ygps, p2.echelon_init, p2.id_unite
                    Recheck Cond: (p2.tir2 = '1'::bpchar)
                    Heap Blocks: exact=3882
                    Buffers: shared hit=4244
                    ->  Bitmap Index Scan on idx_e2point_tir2  (cost=0.00..2344.03 rows=124747 width=0) (actual time=10.145..10.145 rows=124537 loops=1)
                          Index Cond: (p2.tir2 = '1'::bpchar)
                          Buffers: shared hit=362
  ->  Hash  (cost=7982.01..7982.01 rows=10 width=5) (actual time=0.571..0.571 rows=50 loops=1)
        Output: m2.mode
        Buckets: 1024  Batches: 1  Memory Usage: 10kB
        ->  Foreign Scan on metaifn.abmode m2  (cost=100.00..7982.01 rows=10 width=5) (actual time=0.553..0.558 rows=50 loops=1)
              Output: m2.mode
              Remote SQL: SELECT mode FROM metaifn.abmode WHERE ((unite = 'EX'::text))
Planning time: 0.687 ms
Execution time: 160.383 ms

Ce que je ne comprends pas bien, c'est comment le planificateur, sur la dernière jointure, en arrive à estimer qu'il n'y aura que 16 lignes à l'arrivée (alors qu'en réalité, il y en a 7518)... Il fait l'hypothèse qu'il n'y a pas de correspondance sur toutes les lignes des deux tables ?

Merci d'avance pour vos éclaircissements.

gleu

Administrateur

Re : Estimation des lignes issues d'une jointure

La condition de jointure se fait sur deux colonnes qui ne semblent pas être de même type. De ce fait, il devient difficile d'estimer le nombre de correspondances. Sur ce cas précis, même si l'erreur d'estimation est importante, elle n'a pas de conséquence sur le choix du plan. Même s'il avait estimé le nombre de lignes résultants à 7518 lignes, il aurait toujours fait un HashJoin (le nombre de lignes est trop important pour faire un NestedLoop et trop petit pour pouvoir ignorer le HashJoin).

---

Guillaume.

ced

Membre

Re : Estimation des lignes issues d'une jointure

Merci Guillaume pour cette piste de types différents. Je vais essayer de creuser de ce côté-là.
Dans la requête que j'ai indiquée, le planificateur ne choisit effectivement pas le NestedLoop, mais avec plus de jointures, là il finit par choisir cette option, ce qui cause des problèmes de performances.
Je vais essayer de réécrire les requêtes qui font les jointures entre données en base et métadonnées pour lever le problème.

gleu

Administrateur

Re : Estimation des lignes issues d'une jointure

Je tiens à dire qu'un NestedLoop n'est pas forcément une mauvaise option. Parfois, c'est la seule. Parfois, c'est la bonne. Et toutes les autres fois, c'est la mauvaise. Mais bon, le NestedLoop n'est pas un mal en soi.

---

Guillaume.

ced

Membre

Re : Estimation des lignes issues d'une jointure

Effectivement, mais dans le cas présent, compte tenu de l'erreur sur le nombre réel de lignes, il se trouve qu'il devient la mauvaise option.
C'est pour ça qu'il faut que je cherche à améliorer l'estimation du nombre de lignes. C'est plus là qu'est le point sensible.

Encore merci pour tes réponses.