Optimalité de IN (utilisation des index)

[ledjlale]

Sous Postgresql 8.2, PGadmin sous WindowsXP et Serveur sous Freebsd6.2.
J'ai deux tables A et B. A contient des ID en clef primaire. B contient des lignes d'info sur chaque ID de A (clef étrangère).

Mon but est de récupérer toutes les lignes de B associés aux ID de A dont la liste est fournit. La premiere chose qui m'ai venu à l'esprit est d'utiliser IN:

Code :

1
2
3
4
5
6
7
8
9
10
11
 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id IN(110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120);
 
=>
"Hash Join  (cost=60.65..1342640.85 rows=2105472 width=127) (actual time=162111.979..182338.830 rows=4157352 loops=1)"
"  Hash Cond: (B.id = A.id)"
"  ->  Seq Scan on B  (cost=0.00..1106953.08 rows=57219308 width=37) (actual time=2.572..94668.692 rows=57219307 loops=1)"
"  ->  Hash  (cost=60.44..60.44 rows=17 width=90) (actual time=25.505..25.505 rows=17 loops=1)"
"        ->  Seq Scan on A  (cost=0.00..60.44 rows=17 width=90) (actual time=3.624..25.471 rows=17 loops=1)"
"              Filter: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"Total runtime: 188175.473 ms"

On voit qu'il ne passe pas par les index.
Etant sidéré par le résultat, j'ai tenté le OR (bin oui quoi, on ne sait jamais )

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id 
AND (A.id =110 OR A.id=110 OR A.id=106 OR A.id=109 OR A.id=105
 OR A.id=108 OR A.id=104 OR A.id=107
 OR A.id=111 OR A.id=112 OR A.id=113 OR A.id=114
 OR A.id=115 OR A.id=116 OR A.id=117 OR A.id=118 
OR A.id=119 OR A.id=120);
=>
"Hash Join  (cost=71.63..1343890.36 rows=2229324 width=127) (actual time=184682.044..208278.921 rows=4157352 loops=1)"
"  Hash Cond: (B.id = A.id)"
"  ->  Seq Scan on B  (cost=0.00..1106953.08 rows=57219308 width=37) (actual time=3.083..108440.509 rows=57219307 loops=1)"
"  ->  Hash  (cost=71.41..71.41 rows=18 width=90) (actual time=25.823..25.823 rows=17 loops=1)"
"        ->  Seq Scan on A  (cost=0.00..71.41 rows=18 width=90) (actual time=4.866..25.783 rows=17 loops=1)"
"              Filter: ((id = 110) OR (id = 110) OR (id = 106) OR (id = 109) OR (id = 105) OR (id = 108) OR (id = 104) OR (id = 107) OR (id = 111) OR (id = 112) OR (id = 113) OR (id = 114) OR (id = 115) OR (id = 116) OR (id = 117) OR (id = 118) OR (id = 119) OR (id = 120))"
"Total runtime: 215223.763 ms"

Bon, ce test est normal, rien à dire sauf qu'il ne passe pas non plus par les index.
Le bourrin que je suis refait le même test avec des reqêtes SQL unique :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =110;
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =106; 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =109; 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =105; 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =108; 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =104; 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =111; 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =112; 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =113; 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =114; 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =115;
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =116;
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =117;
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =118;
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =119;
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =120;
 
=>
"Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=10.054..107.973 rows=22479 loops=1)"
"  ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.016..0.021 rows=1 loops=1)"
"        Index Cond: (id = 120)"
"  ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=10.028..44.841 rows=22479 loops=1)"
"        Recheck Cond: (120 = id)"
"        ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=6.746..6.746 rows=22479 loops=1)"
"              Index Cond: (120 = id)"
"Total runtime: 139.458 ms"
 
Bon, comme ce n'est que pour la derniere requête, voici le temps que m'a mis l'envoi de tous:
=>
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
Le résultat de la requête contient 8 ligne(s) non-extraite(s).
 
Durée totale d'exécution de la requête :31578 ms.
8 lignes récupérées.

Gosh! On voit que maintenant, les index sont utilisés et le temps du résultat est vraiment autre chose!
Essayons maintenant avec une jointure:

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
EXPLAIN analyze SELECT * FROM B, A WHERE A.id=B.id AND A.id =110 UNION 
SELECT * FROM B, A WHERE A.id=B.id AND A.id =106 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =109 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =105 UNION 
SELECT * FROM B, A WHERE A.id=B.id AND A.id =108 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =104 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =111 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =112 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =113 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =114 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =115 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =116 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =117 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =118 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =119 UNION
SELECT * FROM B, A WHERE A.id=B.id AND A.id =120;
 
=>
"Unique  (cost=12118253.70..12498149.10 rows=10130544 width=127) (actual time=53045.073..76026.855 rows=3792894 loops=1)"
"  ->  Sort  (cost=12118253.70..12143580.06 rows=10130544 width=127) (actual time=53045.068..61569.970 rows=3792894 loops=1)"
"        Sort Key: id, trade_date, price, size, trade_type, id, full_name, name, bloomberg_id, exchange, currency, "type", country, last_intraday_trading_date"
"        ->  Append  (cost=14668.05..9554412.05 rows=10130544 width=127) (actual time=209.719..29140.871 rows=3792894 loops=1)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=209.715..1854.533 rows=381676 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=11.881..11.886 rows=1 loops=1)"
"                          Index Cond: (id = 110)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=197.821..765.388 rows=381676 loops=1)"
"                          Recheck Cond: (110 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=196.920..196.920 rows=381676 loops=1)"
"                                Index Cond: (110 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=136.273..1306.497 rows=269992 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.019..0.024 rows=1 loops=1)"
"                          Index Cond: (id = 106)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=136.234..544.500 rows=269992 loops=1)"
"                          Recheck Cond: (106 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=135.629..135.629 rows=269992 loops=1)"
"                                Index Cond: (106 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=155.711..1587.212 rows=332342 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.019..0.024 rows=1 loops=1)"
"                          Index Cond: (id = 109)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=155.674..648.843 rows=332342 loops=1)"
"                          Recheck Cond: (109 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=154.922..154.922 rows=332342 loops=1)"
"                                Index Cond: (109 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=139.376..1352.241 rows=271366 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.019..0.023 rows=1 loops=1)"
"                          Index Cond: (id = 105)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=139.338..585.931 rows=271366 loops=1)"
"                          Recheck Cond: (105 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=132.109..132.109 rows=271366 loops=1)"
"                                Index Cond: (105 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=150.368..1778.474 rows=365374 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.020..0.026 rows=1 loops=1)"
"                          Index Cond: (id = 108)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=150.331..746.200 rows=365374 loops=1)"
"                          Recheck Cond: (108 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=147.706..147.706 rows=365374 loops=1)"
"                                Index Cond: (108 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=207.864..1454.164 rows=279586 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=6.012..6.017 rows=1 loops=1)"
"                          Index Cond: (id = 104)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=201.835..658.534 rows=279586 loops=1)"
"                          Recheck Cond: (104 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=199.319..199.319 rows=279586 loops=1)"
"                                Index Cond: (104 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=102.347..1512.403 rows=317148 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.020..0.025 rows=1 loops=1)"
"                          Index Cond: (id = 111)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=102.309..616.135 rows=317148 loops=1)"
"                          Recheck Cond: (111 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=101.583..101.583 rows=317148 loops=1)"
"                                Index Cond: (111 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=7.738..98.341 rows=20298 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.020..0.025 rows=1 loops=1)"
"                          Index Cond: (id = 112)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=7.702..40.674 rows=20298 loops=1)"
"                          Recheck Cond: (112 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=7.654..7.654 rows=20298 loops=1)"
"                                Index Cond: (112 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=224.180..2502.560 rows=506542 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.018..0.023 rows=1 loops=1)"
"                          Index Cond: (id = 113)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=224.144..1059.475 rows=506542 loops=1)"
"                          Recheck Cond: (113 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=222.966..222.966 rows=506542 loops=1)"
"                                Index Cond: (113 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=13.373..135.232 rows=27301 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.020..0.025 rows=1 loops=1)"
"                          Index Cond: (id = 114)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=13.338..57.950 rows=27301 loops=1)"
"                          Recheck Cond: (114 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=13.270..13.270 rows=27301 loops=1)"
"                                Index Cond: (114 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=17.901..115.171 rows=21835 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.019..0.024 rows=1 loops=1)"
"                          Index Cond: (id = 115)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=17.865..53.596 rows=21835 loops=1)"
"                          Recheck Cond: (115 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=17.820..17.820 rows=21835 loops=1)"
"                                Index Cond: (115 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=104.532..1225.125 rows=249396 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.019..0.024 rows=1 loops=1)"
"                          Index Cond: (id = 116)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=104.496..521.122 rows=249396 loops=1)"
"                          Recheck Cond: (116 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=103.916..103.916 rows=249396 loops=1)"
"                                Index Cond: (116 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=170.937..2201.755 rows=462972 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.020..0.025 rows=1 loops=1)"
"                          Index Cond: (id = 117)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=170.899..895.831 rows=462972 loops=1)"
"                          Recheck Cond: (117 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=169.822..169.822 rows=462972 loops=1)"
"                                Index Cond: (117 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=11.872..114.330 rows=23342 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.020..0.025 rows=1 loops=1)"
"                          Index Cond: (id = 118)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=11.837..48.134 rows=23342 loops=1)"
"                          Recheck Cond: (118 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=11.786..11.786 rows=23342 loops=1)"
"                                Index Cond: (118 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=93.905..1152.346 rows=241245 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=7.603..7.609 rows=1 loops=1)"
"                          Index Cond: (id = 119)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=86.286..463.921 rows=241245 loops=1)"
"                          Recheck Cond: (119 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=85.766..85.766 rows=241245 loops=1)"
"                                Index Cond: (119 = id)"
"              ->  Nested Loop  (cost=14668.05..590819.16 rows=633159 width=127) (actual time=11.806..110.855 rows=22479 loops=1)"
"                    ->  Index Scan using A_pkey on A  (cost=0.00..8.27 rows=1 width=90) (actual time=0.022..0.028 rows=1 loops=1)"
"                          Index Cond: (id = 120)"
"                    ->  Bitmap Heap Scan on B  (cost=14668.05..584479.31 rows=633159 width=37) (actual time=11.766..47.290 rows=22479 loops=1)"
"                          Recheck Cond: (120 = id)"
"                          ->  Bitmap Index Scan on id_trade_idx  (cost=0.00..14509.76 rows=633159 width=0) (actual time=7.677..7.677 rows=22479 loops=1)"
"                                Index Cond: (120 = id)"
"Total runtime: 81346.953 ms"

Ici, les index sont utilisés. Mais ça ne me prends que la moitier du temps!
Comment expliquez-vous tout ceci? Y a t'il des implémentations à faire pour pouvoir utiliser la notation IN?
Y a t'il des trucs que j'ai pas vu dans les explain?
Ou bien est-ce là une preuve que l'utilisation de IN n'est pas optimale?

[gleu_]

À ma connaissance, PG utilise les index dans le cas de IN. En tout cas, il est sûr qu'il peut les utiliser dans ton gros ensemble de OR.

Donc la première possibilité que je vois : les stats des tables ne sont pas à jour.

Autre chose, quel sont les types de A.id et B.id ?

[ledjlale]

J'avais fais un vacuum analize et un analize (pgadmin) sur B avant ces tests (excusez-moi de l'oublie :/). Je ne me rappelle pas l'avoir fait pour A. Bon apres avoir mis à jour avec ces 2 commandes sur les 2 tables cette fois : rien ne change.

Sinon, les identifiants sont des bigint pour B, et bigserial pour A.

Se peut-il que ce soit lié une une configuration serveur?

[gleu_]

Configuration serveur, je ne pense pas.

As-tu essayé la requête IN en castant tous les nombres pour qu'ils soient explicitement des bigint ? je serais étonné que cela donne de meilleurs résultats, étant donné que ce problème a été corrigé pour les smallint sur les anciennes versions, mais malheureusement je n'ai rien d'autres à te proposer pour l'instant.

Quelle volumétrie au niveau des tables A et B ? en terme de nombre de lignes et de taille ? Quelle taille pour l'index ?

Et pourrait-on avoir la définition exacte des deux tables ?

[ledjlale]

Le castage en bigint ne change rien.

Table A:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE TABLE A
(
  id bigserial NOT NULL,
  full_name text NOT NULL,
  name text NOT NULL,
  internal_id text NOT NULL,
  title text NOT NULL,
  rank text NOT NULL,
  "type" text NOT NULL,
  country text NOT NULL,
  last_trading_date timestamp without time zone,
  CONSTRAINT A_pkey PRIMARY KEY (id)
) 
WITHOUT OIDS;

Taille table : 424 KB
Taille de la table TOAST : 8192 bytes
Taille Index : 56 KB
Lignes éstimées : 462

Table B:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE TABLE B
(
  id bigint NOT NULL,
  trade_date timestamp without time zone NOT NULL,
  price double precision NOT NULL,
  size bigint NOT NULL,
  trade_type character(1) NOT NULL DEFAULT 'T'::bpchar,
  CONSTRAINT B_pkey PRIMARY KEY (id, trade_date, price, trade_type),
  CONSTRAINT B_id_fkey FOREIGN KEY (id)
      REFERENCES A (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
) 
WITHOUT OIDS;
CREATE INDEX id_trade_idx  ON B  USING btree  (id, trade_date);
CREATE INDEX trade_idx  ON B  USING btree  (trade_date);

Taille table : 4179 MB
Taille de la table TOAST : aucun
Taille Index : 5778 MB
Lignes éstimées : 57219300

[gleu_]

Tu es sûr de la taille de ton index ? J'ai dû mal à comprendre pourquoi PG s'embêterait à parcourir un index de 5G si la table elle-même est plus petite

As-tu fait un REINDEX de ton index dernièrement ?

Enfin, peut-être ne considère-t'il pas l'utilisation d'un Index Bitmap Scan dans le cas d'un IN. Peut-être que la création d'un index sur id uniquement solutionnerait ton problème. Rien que pour le test, je serais très intéressé par ce résultat (si évidemment, tu as le temps de le faire).

[ledjlale]

La définition d'une clef primaire ne definit-elle pas un index?
Il y a 5Gb d'index, mais il faut voir qu'il y a 3 index sur la table B! (id, trade_date et le couple (id,trade_date)).

Pour ce qui est du REINDEX, oui j'en avais fait un. Je vais le refaire pour voir.

[gleu_]

Je crois que oui pour la première question. Cependant B.id n'est pas une clé primaire. La clé primaire de B est sur trois champs. Moi, je parlais d'un index sur id seul. Je ne vois que deux index sur ta table B : un sur id et trad_date et un autre sur trad_date seul.

Il serait aussi possible d'avoir la taille des deux index ? et la réponse à mon intervention précédente au vu des nouvelles infos données ci-dessus ? Merci.

[ledjlale]

Haaaaaaaaaaaaa oui effectivement, j'ai confondu clef étrangere et primaire...

Merci, je viens d'apprendre ce que tu voulais dire par un "Index Bitmap Scan" qui est un scan d'index defini en multi-colonne

et encore un oops : j'ai cru t'avoir deja mis les resultats du test avec l'index sur ID :/ Je le refais!

Code :

1
2
3
4
5
6
7
8
9
10
 
"Hash Join  (cost=104.17..1343333.59 rows=2106479 width=127) (actual time=161927.457..182293.533 rows=4169645 loops=1)"
"  Hash Cond: (B.id = A.id)"
"  ->  Seq Scan on B  (cost=0.00..1107489.64 rows=57246664 width=37) (actual time=2.651..94598.153 rows=57246663 loops=1)"
"  ->  Hash  (cost=103.96..103.96 rows=17 width=90) (actual time=6.450..6.450 rows=17 loops=1)"
"        ->  Bitmap Heap Scan on A  (cost=56.40..103.96 rows=17 width=90) (actual time=6.353..6.418 rows=17 loops=1)"
"              Recheck Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"              ->  Bitmap Index Scan on A_pkey  (cost=0.00..56.40 rows=17 width=0) (actual time=6.334..6.334 rows=17 loops=1)"
"                    Index Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"Total runtime: 188145.139 ms"

On voit qu'il fait un parcours sequentiel sur la premiere condition.
J'ai tésté en changeant l'ordre des conditions ('IN' avant le '=') mais rien ne change.
On voit aussi que ça se passe dans la premiere branche. Donc certainement lors de la construction de (A,B).
Si je réécris ma requête pour limiter cette construction pour ce test:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
EXPLAIN analyze SELECT * FROM B, (SELECT * FROM A WHERE id IN(110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120)) AS u WHERE B.id IN(110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120) AND u.id=B.id;
"Hash Join  (cost=169590.13..1039871.45 rows=348800 width=127) (actual time=1559.016..20947.338 rows=4169645 loops=1)"
"  Hash Cond: (B.id = A.id)"
"  ->  Bitmap Heap Scan on B  (cost=169485.95..1000732.45 rows=9479152 width=37) (actual time=1537.814..7968.645 rows=4169645 loops=1)"
"        Recheck Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"        ->  Bitmap Index Scan on B_id_idx  (cost=0.00..167116.16 rows=9479152 width=0) (actual time=1515.328..1515.328 rows=4169645 loops=1)"
"              Index Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"  ->  Hash  (cost=103.96..103.96 rows=17 width=90) (actual time=21.162..21.162 rows=17 loops=1)"
"        ->  Bitmap Heap Scan on A  (cost=56.40..103.96 rows=17 width=90) (actual time=21.084..21.122 rows=17 loops=1)"
"              Recheck Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"              ->  Bitmap Index Scan on A_id_idx  (cost=0.00..56.40 rows=17 width=0) (actual time=10.500..10.500 rows=17 loops=1)"
"                    Index Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"Total runtime: 26791.100 ms"

Hum.
J'ai fais un chtit test pour voir ce que ça donne en remplaçant la condition d'égalité:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
EXPLAIN analyze SELECT * FROM B, A WHERE B.id IN(110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120) AND A.id IN(110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120)
AND A.id=B.id;
"Hash Join  (cost=169590.13..1039871.45 rows=348800 width=127) (actual time=707.537..20105.305 rows=4169645 loops=1)"
"  Hash Cond: (B.id = A.id)"
"  ->  Bitmap Heap Scan on B (cost=169485.95..1000732.45 rows=9479152 width=37) (actual time=694.701..7127.337 rows=4169645 loops=1)"
"        Recheck Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"        ->  Bitmap Index Scan on B_id_idx  (cost=0.00..167116.16 rows=9479152 width=0) (actual time=672.820..672.820 rows=4169645 loops=1)"
"              Index Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"  ->  Hash  (cost=103.96..103.96 rows=17 width=90) (actual time=0.191..0.191 rows=17 loops=1)"
"        ->  Bitmap Heap Scan on A  (cost=56.40..103.96 rows=17 width=90) (actual time=0.116..0.152 rows=17 loops=1)"
"              Recheck Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"              ->  Bitmap Index Scan on A_id_idx  (cost=0.00..56.40 rows=17 width=0) (actual time=0.090..0.090 rows=17 loops=1)"
"                    Index Cond: (id = ANY ('{110,106,109,105,108,104,107,111,112,113,114,115,116,117,118,119,120}'::bigint[]))"
"Total runtime: 25942.904 ms"

Ok. Il doit effectuer l'égalité sans prendre en considération les autres conditionnels.C'est comme si j'écrivais 'SELECT * FROM A,B WHERE A.id=B.id;'. (Je demande confirmation)
Mais je ne vois toujours pas pourquoi il ne passe pas par un index pour repondre à une clause d'égalité entre deux colonnes de deux tables. (il fait des parcours sequentiels si j'enleve les clauses IN).

Pour ce qui est des tailles de chaque index:
Sur B:
(id) => 1226MB
(id, trade_date) => 1722MB
(trade_date) => 1226MB

[gleu_]

J'avoue que je suis un peu sec là aussi. Il ne semble pas capable d'inférer que B IN X si A IN X AND A=B. Pour faire son hash, il a besoin de deux pseudos tables temporaires. Il en compose une en filtrant des éléments de A grâce au IN, et il prend B entièrement car il n'a pas de filtre spécifique sur B. Et seulement ensuite il fait son hachage. Étonnant.

En attendant, tu as une solution avec le A IN X AND B IN X AND A=B. Il faudrait voir si l'index sur B.id est toujours nécessaire.

[ledjlale]

En attendant, et ça m'aide pour construire d'autres requêtes du même genre

Mais je ne vois pas comment utiliser les index dans le cas ci-dussous:
SELECT * FROM A,B WHERE A.id=B.id; (aucune idée des ID)

De plus, postgres n'utilise pas d'index pour les requêtes avec GROUP BY:
SELECT id FROM A GROUP BY id;

.. il fait un parcours sequentiel!!

[gleu_]

Pour la partie du GROUP BY, conceptuellement, je ne vois pas pourquoi il utiliserait un index. Sans compter que la table A est tellement petite que l'utilisation d'un index pour une quelconque opération n'est pas forcément une bonne idée en soi.