Forums PostgreSQL.fr

Merwin

Membre

Comment optimiser cette requête

Bonjour à tous,

J'éxécute la requête suivante, qui me renvoi ~280 000 lignes en 5s. C'est trop long pour le besoin, donc je cherche un moyen d'obtenir un meilleur temps de réponse. Je vous joint un screenshot du Explain de PgAdmin, ainsi que la requête et la sortie du explain en texte. Je ne comprends pas très bien la sortie du explain, donc si vous pouviez me donner quelques conseils, ce serait génial. Merci d'avance.

Je suis PostgreSQL 9.1/9.2.

Requête :

SELECT dcc.id "DCC", produit_ligne_name AS "Produit", benef_partner.name AS "Bénéficiaire",
       commission_ttc_prix "Commission", vente_ht_prix "Vente Prix HT",
       vente_tx_prix "Vente Taxe", vente_ttc_prix "Vente Prix TTC", 0.0 "Frais de gestion HT",
       0.0 "Frais de gestion TTC", 0.0 "Commission Apporteur", 0.0 "Geste Com HT", 0.0 "Geste Com Taxe",
       0.0 "Geste Com TTC"
FROM       oa_quittance_detail quittance_detail
LEFT JOIN  oa_contrat_sante_beneficiaire benef ON quittance_detail.contrat_sante_beneficiaire_id = benef.id
LEFT JOIN  res_partner benef_partner ON benef.beneficiaire_partner_id = benef_partner.id
INNER JOIN oa_quittance quittance ON quittance_detail.quittance_id = quittance.id
INNER JOIN oa_debit_credit_detail dcd ON quittance.debit_credit_detail_id = dcd.id
INNER JOIN oa_debit_credit_client dcc ON dcd.debit_credit_client_id = dcc.id

Explain:

"Hash Left Join  (cost=23273.45..53868.33 rows=286233 width=67)"
"  Hash Cond: (quittance_detail.contrat_sante_beneficiaire_id = benef.id)"
"  ->  Hash Join  (cost=10371.73..30313.14 rows=286233 width=53)"
"        Hash Cond: (quittance_detail.quittance_id = quittance.id)"
"        ->  Seq Scan on oa_quittance_detail quittance_detail  (cost=0.00..9202.33 rows=286233 width=53)"
"        ->  Hash  (cost=9794.10..9794.10 rows=35170 width=8)"
"              ->  Hash Join  (cost=3376.83..9794.10 rows=35170 width=8)"
"                    Hash Cond: (dcd.debit_credit_client_id = dcc.id)"
"                    ->  Hash Join  (cost=1477.33..6808.16 rows=35170 width=8)"
"                          Hash Cond: (dcd.id = quittance.debit_credit_detail_id)"
"                          ->  Seq Scan on oa_debit_credit_detail dcd  (cost=0.00..3121.79 rows=112179 width=8)"
"                          ->  Hash  (cost=899.70..899.70 rows=35170 width=8)"
"                                ->  Seq Scan on oa_quittance quittance  (cost=0.00..899.70 rows=35170 width=8)"
"                    ->  Hash  (cost=1267.67..1267.67 rows=38467 width=4)"
"                          ->  Seq Scan on oa_debit_credit_client dcc  (cost=0.00..1267.67 rows=38467 width=4)"
"  ->  Hash  (cost=12335.35..12335.35 rows=30830 width=22)"
"        ->  Hash Left Join  (cost=9346.37..12335.35 rows=30830 width=22)"
"              Hash Cond: (benef.beneficiaire_partner_id = benef_partner.id)"
"              ->  Seq Scan on oa_contrat_sante_beneficiaire benef  (cost=0.00..1276.30 rows=30830 width=8)"
"              ->  Hash  (cost=6913.61..6913.61 rows=132461 width=22)"
"                    ->  Seq Scan on res_partner benef_partner  (cost=0.00..6913.61 rows=132461 width=22)"

Screenshot PgAdmin 3 :

Dernière modification par Merwin (01/10/2012 14:55:34)

flo

Membre

Re : Comment optimiser cette requête

Pourrais-tu donner la définition des tables utilisées dans la requête, et en particulier les clés primaires et les index?
D'autre part, un explain analyze donne plus d'informations qu'un simple explain. Pourrais-tu en fournir un?

Merwin

Membre

Re : Comment optimiser cette requête

Bien sur, voici l'explain analyze :

"Hash Left Join  (cost=23123.80..52130.63 rows=252518 width=64) (actual time=198.798..529.180 rows=284502 loops=1)"
"  Hash Cond: (quittance_detail.contrat_sante_beneficiaire_id = benef.id)"
"  ->  Hash Join  (cost=10222.08..29810.31 rows=252518 width=50) (actual time=105.611..302.826 rows=284502 loops=1)"
"        Hash Cond: (quittance_detail.quittance_id = quittance.id)"
"        ->  Seq Scan on oa_quittance_detail quittance_detail  (cost=0.00..9202.33 rows=286233 width=50) (actual time=0.045..80.117 rows=284502 loops=1)"
"        ->  Hash  (cost=9712.24..9712.24 rows=31027 width=8) (actual time=105.502..105.502 rows=33715 loops=1)"
"              Buckets: 4096  Batches: 2  Memory Usage: 665kB"
"              ->  Hash Join  (cost=3398.36..9712.24 rows=31027 width=8) (actual time=30.081..100.203 rows=33715 loops=1)"
"                    Hash Cond: (dcd.debit_credit_client_id = dcc.id)"
"                    ->  Hash Join  (cost=1477.33..6808.16 rows=35170 width=8) (actual time=10.840..64.173 rows=33715 loops=1)"
"                          Hash Cond: (dcd.id = quittance.debit_credit_detail_id)"
"                          ->  Seq Scan on oa_debit_credit_detail dcd  (cost=0.00..3121.79 rows=112179 width=8) (actual time=0.011..20.195 rows=110691 loops=1)"
"                          ->  Hash  (cost=899.70..899.70 rows=35170 width=8) (actual time=10.694..10.694 rows=33715 loops=1)"
"                                Buckets: 4096  Batches: 2  Memory Usage: 659kB"
"                                ->  Seq Scan on oa_quittance quittance  (cost=0.00..899.70 rows=35170 width=8) (actual time=0.003..5.693 rows=33715 loops=1)"
"                    ->  Hash  (cost=1363.84..1363.84 rows=33936 width=4) (actual time=19.207..19.207 rows=33715 loops=1)"
"                          Buckets: 4096  Batches: 2  Memory Usage: 597kB"
"                          ->  Seq Scan on oa_debit_credit_client dcc  (cost=0.00..1363.84 rows=33936 width=4) (actual time=0.014..13.099 rows=33715 loops=1)"
"                                Filter: ((name_client)::text = 'prime'::text)"
"                                Rows Removed by Filter: 4581"
"  ->  Hash  (cost=12335.35..12335.35 rows=30830 width=22) (actual time=93.097..93.097 rows=30651 loops=1)"
"        Buckets: 2048  Batches: 2  Memory Usage: 824kB"
"        ->  Hash Left Join  (cost=9346.37..12335.35 rows=30830 width=22) (actual time=55.220..87.226 rows=30651 loops=1)"
"              Hash Cond: (benef.beneficiaire_partner_id = benef_partner.id)"
"              ->  Seq Scan on oa_contrat_sante_beneficiaire benef  (cost=0.00..1276.30 rows=30830 width=8) (actual time=0.020..7.226 rows=30651 loops=1)"
"              ->  Hash  (cost=6913.61..6913.61 rows=132461 width=22) (actual time=55.118..55.118 rows=132461 loops=1)"
"                    Buckets: 2048  Batches: 8  Memory Usage: 919kB"
"                    ->  Seq Scan on res_partner benef_partner  (cost=0.00..6913.61 rows=132461 width=22) (actual time=0.008..30.643 rows=132461 loops=1)"
"Total runtime: 537.357 ms"

Pour les tables, les seules clés primaires sont les champs "id".
Pour les index on les voit dans le explain non ?

gleu

Administrateur

Re : Comment optimiser cette requête

C'est un plan assez propre. Le planificateur ne s'est pas trompé dans ses estimations de lignes. Il n'y a pas grand chose à dire d'un tel plan. Vous dites que la requête s'exécute en 5s mais celle-là s'exécute en 500ms. Il faudrait plutôt l'EXPLAIN ANALYZE de la requête qui prend 5 secondes.

---

Guillaume.

Merwin

Membre

Re : Comment optimiser cette requête

Bah c'est ce que je fais, je ne comprends pas. EXPLAIN ANALYZE (ma requete de 5s). Le ANALYZE prend 500ms et la requête ~5s...

gleu

Administrateur

Re : Comment optimiser cette requête

Hmmm, si je comprends bien, quand vous faites un EXPLAIN ANALYZE, ça prend 500ms et quand vous exécutez la requête, ça fait 5 secondes ? vous exécutez la requête avec quel outil ? vous comptez quoi dans la durée d'exécution de la requête ? la récupération des résultats aussi ? l'affichage aussi ? Il est tout à fait possible que le transfert des données et l'affichage des données prennent les cinq secondes. Sur pgAdmin, avec beaucoup de lignes, ça peut même prendre beaucoup plus de temps.

---

Guillaume.

Merwin

Membre

Re : Comment optimiser cette requête

Ça doit certainement venir de là, merci !