multiple left join [Résolu]

[lanve]

Bonjour tout le monde

je suis face à un problème et j'aurais besoin de votre aide.

j'ai 3 tables:
1°) sda (reel, numero, plaque) :
prim_sda_id PRIMARY KEY, btree (reel, numero, plaque)
ind_sda_reel btree (reel text_pattern_ops)
ind_sda_rev_numero btree (reverse(numero::text) text_pattern_ops)

2°) routes (reel, pid)
prim_routes_id PRIMARY KEY, btree (reel)
ind_routes_palier btree (pid)
ind_routes_reel_paliers btree (reel, pid)

3°) paliers (id) index:
prim_palier_id PRIMARY KEY, btree (id)

J'ai une requete:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
EXPLAIN ANALYZE SELECT
a.numero AS rnum,
a.plaque AS plaque,
COALESCE(r.reel, 'UNKNOWN') AS reel
FROM sda AS a
LEFT JOIN routes AS r ON r.reel=a.reel
LEFT JOIN paliers AS p ON p.id = r.pid
WHERE REVERSE(a.numero) LIKE ( REVERSE('155553638') || '%' );
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Merge RIGHT JOIN  (cost=812.60..814.74 rows=213 width=150) (actual time=0.389..0.392 rows=1 loops=1)
   Merge Cond: ("outer".id = "inner".pid)
   ->  Sort  (cost=3.80..3.97 rows=69 width=8) (actual time=0.155..0.185 rows=53 loops=1)
         Sort KEY: p.id
         ->  Seq Scan ON paliers p  (cost=0.00..1.69 rows=69 width=8) (actual time=0.004..0.070 rows=69 loops=1)
   ->  Sort  (cost=808.80..809.34 rows=213 width=154) (actual time=0.172..0.173 rows=1 loops=1)
         Sort KEY: r.pid
         ->  Merge RIGHT JOIN  (cost=583.50..800.57 rows=213 width=154) (actual time=0.159..0.165 rows=1 loops=1)
               Merge Cond: (("outer".reel)::text = "inner"."?column4?")
               ->  INDEX Scan USING prim_routes_id ON routes r  (cost=0.00..202.04 rows=4731 width=56) (actual time=0.053..0.075 rows=10 loops=1)
               ->  Sort  (cost=583.50..584.04 rows=213 width=150) (actual time=0.062..0.062 rows=1 loops=1)
                     Sort KEY: (a.reel)::text
                     ->  Bitmap Heap Scan ON sda a  (cost=3.28..575.27 rows=213 width=150) (actual time=0.053..0.054 rows=1 loops=1)
                           Filter: (reverse((numero)::text) ~~ '836355551%'::text)
                           ->  Bitmap INDEX Scan ON ind_sda_rev_numero  (cost=0.00..3.28 rows=213 width=0) (actual time=0.014..0.014 rows=1 loops=1)
                                 INDEX Cond: ((reverse((numero)::text) ~>=~ '836355551'::text) AND (reverse((numero)::text) ~<~ '836355552'::text))
 Total runtime: 0.486 ms

Je remarque qu'il n'utilise pas la clé primaire (sur la colone id) de la table paliers mais qui la parcours séquentiellement (si je comprends bien)

J'ai donc essayer

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge LEFT JOIN  (cost=1104.40..1131.25 rows=213 width=150) (actual time=39.804..39.808 rows=1 loops=1)
   Merge Cond: ("outer"."?column4?" = "inner"."?column2?")
   ->  Sort  (cost=583.50..584.04 rows=213 width=150) (actual time=0.073..0.073 rows=1 loops=1)
         Sort KEY: (a.reel)::text
         ->  Bitmap Heap Scan ON sda a  (cost=3.28..575.27 rows=213 width=150) (actual time=0.063..0.064 rows=1 loops=1)
               Filter: (reverse((numero)::text) ~~ '836355551%'::text)
               ->  Bitmap INDEX Scan ON ind_sda_rev_numero  (cost=0.00..3.28 rows=213 width=0) (actual time=0.024..0.024 rows=1 loops=1)
                     INDEX Cond: ((reverse((numero)::text) ~>=~ '836355551'::text) AND (reverse((numero)::text) ~<~ '836355552'::text))
   ->  Sort  (cost=520.90..532.73 rows=4731 width=52) (actual time=39.707..39.711 rows=10 loops=1)
         Sort KEY: (r.reel)::text
         ->  Merge RIGHT JOIN  (cost=0.00..232.12 rows=4731 width=52) (actual time=0.020..11.228 rows=4731 loops=1)
               Merge Cond: ("outer".id = "inner".pid)
               ->  INDEX Scan USING prim_palier_id ON paliers p  (cost=0.00..6.76 rows=69 width=8) (actual time=0.007..0.091 rows=69 loops=1)
               ->  INDEX Scan USING ind_routes_palier ON routes r  (cost=0.00..197.04 rows=4731 width=56) (actual time=0.007..5.018 rows=4731 loops=1)
 Total runtime: 40.108 ms

La il utilise bien les index mais le total runtime est 100 fois plus important
Toujours si je lis bien l'analyse c'est parce qu'il cherche d'abors les associations route<=>palier(4731*69) et apres il fait l'association sda <=> route.

Or une route a 0 ou 1 palier et un sda 0 ou 1 route
(1 palier a * route, une route a * sda)

Donc si j'arrivais à faire comprendre a psql qu'il doit d'abord trouver le bon sda, puis trouver la route et à la fin chercher un palier (sachant que si il n'y a pas de route il n'y a pas de palier)

PS: j'utilise le REVERSE(numero) car j'ai constaté que psql n'utilise pas l'index quand on fait 'totu' like '%otu' alors que pour 'utot' LIKE 'uto%' il utilise l'index.

[lanve]

En fait je pense avoir trouvé. Postgres n'utilise pas les index quand la table tiens dans une page disque. Et c'est le cas pour la table paliers...