Vous n'êtes pas identifié(e).
Pages : 1
Merci pour votre aide et votre réactivité 
Je n'ai pas trouvé de combination d'index permettant de ne garder pour le planificateur uniquement les enregistrements issus des 2 filtres (date_realisation is NULL moins date_erreur is NULL).
Par contre, vous nous avez mis sur la voie pour trouver le moyen de détourner le planficateur dans son estimation sur ces colonnes fonctionnellement corrélées.
Une astuce trouvée pas mon chef (qui n'est pas chef pour rien) consiste à compliquer le premier filtre pour l'obliger à mieux l'estimer...
SELECT
a.adr_clef As ID,
a.fac_clef As ID_Facade,
fb.ID AS ID_facboucle,
fb.ID_Boucle,
a.adr_nb_bal As Nb_Bal,
(adr_nb_bal - adr_qte_sp - adr_qte_bs) As Nb_BAL_distri,
a.adr_numero As Numero,
hor.tyh_clef As ID_Horaire,
hor.tyh_lib As Libelle_Horaire
FROM
(SELECT ID_Iti FROM itineraire.demande_generation_carte WHERE (CASE WHEN Date_Realisation IS NULL THEN 1 ELSE 0 END) * (CASE WHEN Date_Erreur IS NULL THEN 1 ELSE 0 END) = 1 ) as dgc
JOIN itineraire.itineraire
ON (dgc.ID_Iti = itineraire.itineraire.ID)
JOIN itineraire.boucle b ON b.ID_Iti = dgc.ID_Iti
JOIN itineraire.facade_boucle fb ON fb.ID_Boucle = b.ID
JOIN rm_facade f ON f.fac_clef = fb.ID_Facade
JOIN rm_adresse a ON a.fac_clef = f.fac_clef
JOIN rm_typ_acces ta ON a.tya_clef = ta.tya_clef
JOIN rm_typ_tranche_horaire hor ON a.tyh_clef = hor.tyh_clef ;
Hash Join (cost=2.31..496059.67 rows=48877 width=55) (actual time=9.577..43.266 rows=931 loops=1)
Hash Cond: (a.tyh_clef = hor.tyh_clef)
-> Hash Join (cost=1.14..495142.05 rows=48877 width=46) (actual time=9.481..38.982 rows=931 loops=1)
Hash Cond: (a.tya_clef = ta.tya_clef)
-> Nested Loop (cost=0.00..494468.86 rows=48877 width=50) (actual time=9.402..34.920 rows=931 loops=1)
-> Nested Loop (cost=0.00..154786.55 rows=8523 width=32) (actual time=9.372..18.316 rows=214 loops=1)
-> Nested Loop (cost=0.00..55213.27 rows=8523 width=24) (actual time=9.345..12.620 rows=214 loops=1)
-> Nested Loop (cost=0.00..2918.51 rows=626 width=8) (actual time=9.318..9.545 rows=11 loops=1)
-> Nested Loop (cost=0.00..1095.06 rows=79 width=16) (actual time=9.290..9.336 rows=2 loops=1)
-> Seq Scan on demande_generation_carte (cost=0.00..464.00 rows=79 width=8) (actual time=9.245..9.250 rows=2 loops=1)
Filter: ((CASE WHEN (date_realisation IS NULL) THEN 1 ELSE 0 END * CASE WHEN (date_erreur IS NULL) THEN 1 ELSE 0 END) = 1)
-> Index Scan using pk_itineraire on itineraire (cost=0.00..7.98 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=2)
Index Cond: (demande_generation_carte.id_iti = itineraire.id)
-> Index Scan using idx_boucle_itineraire on boucle b (cost=0.00..22.98 rows=8 width=16) (actual time=0.017..0.072 rows=6 loops=2)
Index Cond: (b.id_iti = demande_generation_carte.id_iti)
-> Index Scan using idx_facboucle_boucle on facade_boucle fb (cost=0.00..83.09 rows=36 width=24) (actual time=0.015..0.194 rows=19 loops=11)
Index Cond: (fb.id_boucle = b.id)
-> Index Scan using rm_facade_pk on rm_facade f (cost=0.00..11.67 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=214)
Index Cond: (f.fac_clef = fb.id_facade)
-> Index Scan using rm_adresse_fk1 on rm_adresse a (cost=0.00..39.74 rows=9 width=34) (actual time=0.013..0.051 rows=4 loops=214)
Index Cond: (a.fac_clef = f.fac_clef)
-> Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.044..0.044 rows=6 loops=1)
-> Seq Scan on rm_typ_acces ta (cost=0.00..1.06 rows=6 width=4) (actual time=0.005..0.018 rows=6 loops=1)
-> Hash (cost=1.08..1.08 rows=8 width=13) (actual time=0.063..0.063 rows=8 loops=1)
-> Seq Scan on rm_typ_tranche_horaire hor (cost=0.00..1.08 rows=8 width=13) (actual time=0.015..0.032 rows=8 loops=1)
Total runtime: 45.398 ms
On voit que l'erreur d'estimation dans Seq Scan on demande_generation_carte (cost=0.00..464.00 rows=79 width=8) (actual time=9.245..9.250 rows=2 loops=1) est moins importante.
Vos commentaires sur cette manière particulière de "bluffer" le planificateur sont les bienvenus.
Christophe
Bonjour,
Oui j'y avais bien pensé, il l'a même utilisé dans des exemples ci-dessus (c'est idx_demande_generation_carte_date_realisation).
Je suis d'accord que c'est là que se situe le problème, à savoir exploiter les retours de ce premier filtre (les quelques lignes de demande_generation_carte) de la même manière que si je lui passait la liste des ID_Iti par un in(ID1, ID2, ID3,...). Je ne l'avais pas précisé mais si je modifie ma requête en excluant la recherche de ces ID et en lui passant une liste en dur, là, il n'y a plus aucun sq_scan.
C'est pour cela que j'avais pensé à "externaliser" cette liste, mais je n'arrive pas à comprendre pourquoi il l'évalue mal... alors que la problématique n'est pas très compliquée.
Pour info, sur la 8.4 en local, je n'évite pas le seq_scan.
Christophe
C'est bien une constante dans la manière de récupérer les (quelques) éléments à traiter et j'aimerais conserver ce système "compacte" sans passer par une table temporaire ou un bouclage sur les identifiants à traiter.
Oui, je ne l'ai pas changée. Mais du coup, j'ai essayé en retirant le Date_Demande is not null (qui est inutile, oups), mais c'est pareil.
Merci pour l'idée d'augmenter les données de pg_statistics !
En passant des SET STATISTICS 1000 sur les bonnes colonnes de mes tables (ce qui avait été fait d'ailleurs par l'admin sur les grosses tables du schéma Public), j'obtiens le plan suivant :
Hash Join (cost=337564.10..743295.26 rows=106747 width=51) (actual time=13.801..43278.803 rows=69 loops=1)
Hash Cond: (a.tya_clef = ta.tya_clef)
-> Hash Join (cost=337562.96..741826.35 rows=106747 width=55) (actual time=13.697..43278.379 rows=69 loops=1)
Hash Cond: (a.tyh_clef = hor.tyh_clef)
-> Hash Join (cost=337561.78..740357.40 rows=106747 width=46) (actual time=13.615..43277.969 rows=69 loops=1)
Hash Cond: (a.fac_clef = fb.id_facade)
-> Seq Scan on rm_adresse a (cost=0.00..366209.20 rows=9471720 width=30) (actual time=0.129..23747.320 rows=9471720 loops=1)
-> Hash (cost=337292.83..337292.83 rows=21516 width=32) (actual time=5.438..5.438 rows=41 loops=1)
-> Nested Loop (cost=0.00..337292.83 rows=21516 width=32) (actual time=1.293..5.167 rows=41 loops=1)
-> Nested Loop (cost=0.00..85938.71 rows=21516 width=24) (actual time=1.085..3.605 rows=41 loops=1)
-> Nested Loop (cost=0.00..7416.53 rows=1581 width=8) (actual time=0.882..3.117 rows=3 loops=1)
-> Nested Loop (cost=0.00..2681.28 rows=229 width=16) (actual time=0.683..2.876 rows=1 loops=1)
-> Index Scan using idx_demande_generation_carte_date_realisation on demande_generation_carte (cost=0.00..882.44 rows=229 width=8) (actual time=0.479..2.664 rows=1 loops=1)
Filter: ((date_demande IS NOT NULL) AND (date_erreur IS NULL))
-> Index Scan using pk_itineraire on itineraire (cost=0.00..7.84 rows=1 width=8) (actual time=0.191..0.193 rows=1 loops=1)
Index Cond: (demande_generation_carte.id_iti = itineraire.id)
-> Index Scan using idx_boucle_itineraire on boucle b (cost=0.00..20.59 rows=7 width=16) (actual time=0.189..0.217 rows=3 loops=1)
Index Cond: (b.id_iti = itineraire.id)
-> Index Scan using idx_facboucle_boucle on facade_boucle fb (cost=0.00..49.42 rows=20 width=24) (actual time=0.071..0.101 rows=14 loops=3)
Index Cond: (fb.id_boucle = b.id)
-> Index Scan using rm_facade_pk on rm_facade f (cost=0.00..11.67 rows=1 width=8) (actual time=0.024..0.026 rows=1 loops=41)
Index Cond: (f.fac_clef = fb.id_facade)
-> Hash (cost=1.08..1.08 rows=8 width=13) (actual time=0.060..0.060 rows=8 loops=1)
-> Seq Scan on rm_typ_tranche_horaire hor (cost=0.00..1.08 rows=8 width=13) (actual time=0.019..0.035 rows=8 loops=1)
-> Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.079..0.079 rows=6 loops=1)
-> Seq Scan on rm_typ_acces ta (cost=0.00..1.06 rows=6 width=4) (actual time=0.033..0.053 rows=6 loops=1)
Total runtime: 43279.190 ms
Bon, il me reste un Seq Scan sur rm_adresse ... que je peux faire sauter avec un LIMIT 100 maintenant ... Hum ..
Hash Join (cost=11.79..520806.67 rows=46452 width=51) (actual time=0.621..4.799 rows=69 loops=1)
Hash Cond: (a.tya_clef = ta.tya_clef)
-> Hash Join (cost=10.65..520166.82 rows=46452 width=55) (actual time=0.543..4.443 rows=69 loops=1)
Hash Cond: (a.tyh_clef = hor.tyh_clef)
-> Nested Loop (cost=9.47..519526.93 rows=46452 width=46) (actual time=0.477..4.085 rows=69 loops=1)
-> Nested Loop (cost=9.47..146662.18 rows=9363 width=32) (actual time=0.316..2.397 rows=41 loops=1)
-> Nested Loop (cost=9.47..37283.89 rows=9363 width=24) (actual time=0.289..1.265 rows=41 loops=1)
-> Nested Loop (cost=9.47..3114.26 rows=688 width=8) (actual time=0.258..0.640 rows=3 loops=1)
-> Nested Loop (cost=9.47..1023.63 rows=100 width=16) (actual time=0.233..0.575 rows=1 loops=1)
-> Limit (cost=9.47..225.44 rows=100 width=8) (actual time=0.201..0.536 rows=1 loops=1)
-> Bitmap Heap Scan on demande_generation_carte (cost=9.47..504.04 rows=229 width=8) (actual time=0.188..0.509 rows=1 loops=1)
Recheck Cond: (date_realisation IS NULL)
Filter: ((date_demande IS NOT NULL) AND (date_erreur IS NULL))
-> Bitmap Index Scan on idx_demande_generation_carte_date_realisation (cost=0.00..9.42 rows=233 width=0) (actual time=0.085..0.085 rows=233 loops=1)
-> Index Scan using pk_itineraire on itineraire (cost=0.00..7.96 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1)
Index Cond: (dgc.id_iti = itineraire.id)
-> Index Scan using idx_boucle_itineraire on boucle b (cost=0.00..20.82 rows=7 width=16) (actual time=0.015..0.042 rows=3 loops=1)
Index Cond: (b.id_iti = itineraire.id)
-> Index Scan using idx_facboucle_boucle on facade_boucle fb (cost=0.00..49.42 rows=20 width=24) (actual time=0.016..0.148 rows=14 loops=3)
Index Cond: (fb.id_boucle = b.id)
-> Index Scan using rm_facade_pk on rm_facade f (cost=0.00..11.67 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=41)
Index Cond: (f.fac_clef = fb.id_facade)
-> Index Scan using rm_adresse_fk1 on rm_adresse a (cost=0.00..39.71 rows=9 width=30) (actual time=0.012..0.026 rows=2 loops=41)
Index Cond: (a.fac_clef = f.fac_clef)
-> Hash (cost=1.08..1.08 rows=8 width=13) (actual time=0.047..0.047 rows=8 loops=1)
-> Seq Scan on rm_typ_tranche_horaire hor (cost=0.00..1.08 rows=8 width=13) (actual time=0.006..0.022 rows=8 loops=1)
-> Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.055..0.055 rows=6 loops=1)
-> Seq Scan on rm_typ_acces ta (cost=0.00..1.06 rows=6 width=4) (actual time=0.011..0.031 rows=6 loops=1)
Total runtime: 5.132 ms
Je teste sur la 8.4 dès que possible.
Christophe
Bonjour à tous,
Après plusieurs recherches infructueuses ou non-sastisfaisantes, je vous soumets mon problème de planification de requête sur le type de requête ci-dessous:
SELECT
a.adr_clef As ID,
a.fac_clef As ID_Facade,
fb.ID AS ID_facboucle,
fb.ID_Boucle,
a.adr_nb_bal As Nb_Bal,
a.adr_numero As Numero,
hor.tyh_clef As ID_Horaire,
hor.tyh_lib As Libelle_Horaire
FROM
(
SELECT ID
FROM
(SELECT ID_Iti FROM itineraire.demande_generation_carte WHERE Date_Demande is not null AND Date_Realisation IS NULL AND date_erreur is NULL ) as dgc
JOIN itineraire.itineraire
ON (dgc.ID_Iti = itineraire.itineraire.ID)
) as i
JOIN itineraire.boucle b ON b.ID_Iti = i.ID
JOIN itineraire.facade_boucle fb ON fb.ID_Boucle = b.ID
JOIN rm_facade f ON f.fac_clef = fb.ID_Facade
JOIN rm_adresse a ON a.fac_clef = f.fac_clef
JOIN rm_typ_acces ta ON a.tya_clef = ta.tya_clef
JOIN rm_typ_tranche_horaire hor ON a.tyh_clef = hor.tyh_clef
Il s’agit de ramener, par une série de jointures simples, certaines informations stockées dans des tables volumineuses (+ de 9.000.000 pour rm_adresse, indexées et contraintes, à partir d’un très faible volume d’enregistrements de départ 1 ou 2 enregistrements de demande_generation_carte sur 14.000).
Jusqu’à un certain temps, le plan de requête utilisé était particulièrement efficace:
Hash Join (cost=9.34..94198.54 rows=936 width=51) (actual time=339.055..703.640 rows=589 loops=1)
Hash Cond: (a.tya_clef = ta.tya_clef)
-> Hash Join (cost=8.20..94184.54 rows=936 width=55) (actual time=338.981..703.093 rows=589 loops=1)
Hash Cond: (a.tyh_clef = hor.tyh_clef)
-> Nested Loop (cost=7.02..94170.49 rows=936 width=46) (actual time=338.963..702.529 rows=589 loops=1)
-> Nested Loop (cost=0.00..3324.35 rows=92 width=32) (actual time=258.724..437.111 rows=215 loops=1)
-> Nested Loop (cost=0.00..2385.81 rows=92 width=24) (actual time=215.383..229.138 rows=215 loops=1)
-> Nested Loop (cost=0.00..25.49 rows=7 width=8) (actual time=155.279..155.291 rows=8 loops=1)
-> Nested Loop (cost=0.00..16.55 rows=1 width=16) (actual time=90.055..90.058 rows=1 loops=1)
-> Index Scan using idx_date_realisation on demande_generation_carte (cost=0.00..8.27 rows=1 width=8) (actual time=49.098..49.099 rows=1 loops=1)
Filter: ((date_demande IS NOT NULL) AND (date_erreur IS NULL))
-> Index Scan using pk_itineraire on itineraire (cost=0.00..8.27 rows=1 width=8) (actual time=40.953..40.955 rows=1 loops=1)
Index Cond: (demande_generation_carte.id_iti = itineraire.id)
-> Index Scan using idx_boucle_itineraire on boucle b (cost=0.00..8.81 rows=10 width=16) (actual time=65.222..65.226 rows=8 loops=1)
Index Cond: (b.id_iti = itineraire.id)
-> Index Scan using idx_facboucle_boucle on facade_boucle fb (cost=0.00..334.06 rows=250 width=24) (actual time=7.516..9.212 rows=27 loops=8)
Index Cond: (fb.id_boucle = b.id)
-> Index Scan using rm_facade_pk on rm_facade f (cost=0.00..10.19 rows=1 width=8) (actual time=0.965..0.966 rows=1 loops=215)
Index Cond: (f.fac_clef = fb.id_facade)
-> Bitmap Heap Scan on rm_adresse a (cost=7.02..984.07 rows=271 width=30) (actual time=0.861..1.227 rows=3 loops=215)
Recheck Cond: (a.fac_clef = f.fac_clef)
-> Bitmap Index Scan on rm_adresse_fk1 (cost=0.00..6.96 rows=271 width=0) (actual time=0.356..0.356 rows=3 loops=215)
Index Cond: (a.fac_clef = f.fac_clef)
-> Hash (cost=1.08..1.08 rows=8 width=13) (actual time=0.008..0.008 rows=8 loops=1)
-> Seq Scan on rm_typ_tranche_horaire hor (cost=0.00..1.08 rows=8 width=13) (actual time=0.002..0.004 rows=8 loops=1)
-> Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.010..0.010 rows=6 loops=1)
-> Seq Scan on rm_typ_acces ta (cost=0.00..1.06 rows=6 width=4) (actual time=0.004..0.006 rows=6 loops=1)
Total runtime: 703.921 ms
Jusqu’au moment où la diversité des valeurs stockées dans ma table de départ (c’est une hypothèse après avoir examiné les résultats de pg_stats de cette table) a fait semble-il basculer le planificateur vers une solution beaucoup moins efficace, à savoir seq_scan sur mes plus grosses tables :
Hash Join (cost=817283.17..1356006.49 rows=517109 width=51) (actual time=56719.181..224144.072 rows=69 loops=1)
Hash Cond: (a.tyh_clef = hor.tyh_clef)
-> Hash Join (cost=817281.99..1348895.06 rows=517109 width=42) (actual time=56719.089..224143.629 rows=69 loops=1)
Hash Cond: (a.fac_clef = f.fac_clef)
-> Hash Join (cost=1.14..495952.70 rows=9450929 width=26) (actual time=0.079..146975.162 rows=9471720 loops=1)
Hash Cond: (a.tya_clef = ta.tya_clef)
-> Seq Scan on rm_adresse a (cost=0.00..366001.29 rows=9450929 width=30) (actual time=0.009..107733.073 rows=9471720 loops=1)
-> Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.042..0.042 rows=6 loops=1)
-> Seq Scan on rm_typ_acces ta (cost=0.00..1.06 rows=6 width=4) (actual time=0.004..0.022 rows=6 loops=1)
-> Hash (cost=816546.83..816546.83 rows=58722 width=32) (actual time=56685.685..56685.685 rows=41 loops=1)
-> Nested Loop (cost=14052.03..816546.83 rows=58722 width=32) (actual time=56516.094..56685.333 rows=41 loops=1)
-> Hash Join (cost=14052.03..130551.40 rows=58722 width=24) (actual time=56484.607..56484.969 rows=41 loops=1)
Hash Cond: (fb.id_boucle = b.id)
-> Seq Scan on facade_boucle fb (cost=0.00..97506.56 rows=4908156 width=24) (actual time=0.011..46986.491 rows=4635750 loops=1)
-> Hash (cost=13998.52..13998.52 rows=4281 width=8) (actual time=61.110..61.110 rows=3 loops=1)
-> Nested Loop (cost=0.00..13998.52 rows=4281 width=8) (actual time=49.566..61.084 rows=3 loops=1)
-> Nested Loop (cost=0.00..3419.05 rows=321 width=16) (actual time=33.200..40.056 rows=1 loops=1)
-> Seq Scan on demande_generation_carte (cost=0.00..901.36 rows=321 width=8) (actual time=1.110..7.952 rows=1 loops=1)
Filter: ((date_demande IS NOT NULL) AND (date_realisation IS NULL) AND (date_erreur IS NULL))
-> Index Scan using pk_itineraire on itineraire (cost=0.00..7.83 rows=1 width=8) (actual time=32.076..32.084 rows=1 loops=1)
Index Cond: (demande_generation_carte.id_iti = itineraire.id)
-> Index Scan using idx_boucle_itineraire on boucle b (cost=0.00..32.80 rows=13 width=16) (actual time=16.355..20.997 rows=3 loops=1)
Index Cond: (b.id_iti = itineraire.id)
-> Index Scan using rm_facade_pk on rm_facade f (cost=0.00..11.67 rows=1 width=8) (actual time=4.868..4.870 rows=1 loops=41)
Index Cond: (f.fac_clef = fb.id_facade)
-> Hash (cost=1.08..1.08 rows=8 width=13) (actual time=0.061..0.061 rows=8 loops=1)
-> Seq Scan on rm_typ_tranche_horaire hor (cost=0.00..1.08 rows=8 width=13) (actual time=0.012..0.032 rows=8 loops=1)
Total runtime: 224144.641 ms
Plusieurs réécritures de mes jointures n’ont rien donné pour retrouver le bon plan. Un index partiel sur le champ date_realisation de ma première table non plus.
En revanche, je vous fais part de 2 solutions qui conduisent à retomber sur le bon plan de requête, mais qui ne sont pas satisfaisantes.
- Ajouter un LIMIT 12 à (SELECT ID_Iti FROM itineraire.demande_generation_carte WHERE Date_Demande is not null AND Date_Realisation IS NULL AND date_erreur is NULL. 12 étant la valeur la plus élévée conditionnant le bon plan (hasard ou lien avec le GEQO_theresold ?
- Appeler une vue équivalente à la requête ci-dessus, en filtrant une deuxième fois en appelant ma vue. Cette méthode tient du bricolage, je trouve.
Je précise que tout cela est réalisé sur la version 8.2.17.
Je vais faire des tests en construisant le même contexte de données avec la version 8.4.4.
Si quelqu'un a une idée ou un début de piste...parce que là je sèche !
Bonne journée,
Christophe
Pages : 1
