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:
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)
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
J'ai donc essayer
La il utilise bien les index mais le total runtime est 100 fois plus important
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
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.
Partager