IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PostgreSQL Discussion :

Optimalité de IN (utilisation des index)


Sujet :

PostgreSQL

  1. #1
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Novembre 2006
    Messages
    77
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2006
    Messages : 77
    Points : 39
    Points
    39
    Par défaut Optimalité de IN (utilisation des index)
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : 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
    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 : 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
    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?

  2. #2
    Membre habitué
    Profil pro
    Inscrit en
    Août 2007
    Messages
    128
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2007
    Messages : 128
    Points : 143
    Points
    143
    Par défaut
    À 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 ?

  3. #3
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Novembre 2006
    Messages
    77
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2006
    Messages : 77
    Points : 39
    Points
    39
    Par défaut
    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?

  4. #4
    Membre habitué
    Profil pro
    Inscrit en
    Août 2007
    Messages
    128
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2007
    Messages : 128
    Points : 143
    Points
    143
    Par défaut
    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 ?

  5. #5
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Novembre 2006
    Messages
    77
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2006
    Messages : 77
    Points : 39
    Points
    39
    Par défaut
    Le castage en bigint ne change rien.

    Table A:
    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
     
    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 : 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
     
    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

  6. #6
    Membre habitué
    Profil pro
    Inscrit en
    Août 2007
    Messages
    128
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2007
    Messages : 128
    Points : 143
    Points
    143
    Par défaut
    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).

  7. #7
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Novembre 2006
    Messages
    77
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2006
    Messages : 77
    Points : 39
    Points
    39
    Par défaut
    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.

  8. #8
    Membre habitué
    Profil pro
    Inscrit en
    Août 2007
    Messages
    128
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2007
    Messages : 128
    Points : 143
    Points
    143
    Par défaut
    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.

  9. #9
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Novembre 2006
    Messages
    77
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2006
    Messages : 77
    Points : 39
    Points
    39
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  10. #10
    Membre habitué
    Profil pro
    Inscrit en
    Août 2007
    Messages
    128
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2007
    Messages : 128
    Points : 143
    Points
    143
    Par défaut
    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.

  11. #11
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Novembre 2006
    Messages
    77
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2006
    Messages : 77
    Points : 39
    Points
    39
    Par défaut
    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!!

  12. #12
    Membre habitué
    Profil pro
    Inscrit en
    Août 2007
    Messages
    128
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2007
    Messages : 128
    Points : 143
    Points
    143
    Par défaut
    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.

Discussions similaires

  1. [SQL2000] Utilisation des index ...
    Par scornille dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 03/05/2006, 17h07
  2. Utilisation des Indexes
    Par Wurlitzer dans le forum Oracle
    Réponses: 1
    Dernier message: 24/04/2006, 19h46
  3. Requête SELECT : limite d'utilisation des index
    Par DadaWeb dans le forum Requêtes
    Réponses: 7
    Dernier message: 07/12/2005, 23h24
  4. Compteur sur l'utilisation des index
    Par hkhan dans le forum Administration
    Réponses: 11
    Dernier message: 14/10/2004, 18h57
  5. Utilisation des "indexs" ?
    Par vandeyy dans le forum Décisions SGBD
    Réponses: 1
    Dernier message: 07/09/2004, 08h49

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo