Forums PostgreSQL.fr

flo

Membre

Question sur une requête

Bonjour,

j'ai besoin d'un avis extérieur. J'ai une requête qui doit s'exécuter en un temps raisonnable, c'est à dire quelques secondes maximum (pour de l'affichage sur une application web)
Mon problème, c'est que les tables actuellement en production ou en test sont peu remplies, la requête est rapide.
Mais je pense que ce ne sera plus le cas lorsque les tables seront remplies, en particulier parcours_extranet.

Voilà la bête :

SELECT   'A' AS TYPE, s.ser_a_libelle AS libelle, NULL AS noffre,
         sr.cre_a_code AS cr, sr.sre_d_debut AS datedebut,
         NULL AS daterealisation, sr.sre_d_fin AS datefin,
         sr.sre_n_duree_h_prest AS duree, r.res_a_libelle AS resultat,
         NULL AS motifdepart, sr.sre_n_nordre_service AS nordre,
         min_pe.min_date,
         min_pe.bef_a_id
    FROM
         service_rendu sr
          LEFT OUTER JOIN resultat r
         ON (sr.rpo_a_codet = r.res_a_id)
         LEFT OUTER JOIN service s ON (s.ser_a_id = sr.ser_a_id)
         LEFT OUTER JOIN
          (
              -- jointure avec le premier parcours_extranet pour éliminer les éléments de service dont la date correspond à une date dans parcours_extranet
              SELECT MIN (prs_d_debut) as min_date, bef_a_id
              FROM parcours_extranet
              WHERE bef_a_id = '00000012'
              group by bef_a_id
         ) as min_pe
         ON   (min_pe.bef_a_id = sr.bef_a_id)
         WHERE sr.bef_a_id = '00000012'
        AND s.cas_a_id IN ('S3', 'S4', 'S5', 'S6', 'S7')
            AND (min_pe.min_date is null or sr.sre_d_debut < min_pe.min_date ) --supprimer les services suivant la date
    ORDER BY datedebut DESC

et le plan d'exécution :
Sort  (cost=101.92..101.97 rows=21 width=119) (actual time=0.427..0.428 rows=1 loops=1)
  Sort Key: sr.sre_d_debut
  ->  Hash Left Join  (cost=100.54..101.46 rows=21 width=119) (actual time=0.402..0.418 rows=1 loops=1)
        Hash Cond: ("outer".bef_a_id = "inner".bef_a_id)
        Filter: (("inner".min_date IS NULL) OR ("outer".sre_d_debut < "inner".min_date))
        ->  Merge Left Join  (cost=99.28..100.08 rows=21 width=115) (actual time=0.354..0.368 rows=2 loops=1)
              Merge Cond: ("outer".ser_a_id = "inner".ser_a_id)
              Filter: (("inner".cas_a_id = 'S3'::bpchar) OR ("inner".cas_a_id = 'S4'::bpchar) OR ("inner".cas_a_id = 'S5'::bpchar) OR ("inner".cas_a_id = 'S6'::bpchar) OR ("inner".cas_a_id = 'S7'::bpchar))
              ->  Sort  (cost=95.55..95.60 rows=21 width=83) (actual time=0.144..0.148 rows=4 loops=1)
                    Sort Key: sr.ser_a_id
                    ->  Merge Right Join  (cost=84.67..95.09 rows=21 width=83) (actual time=0.114..0.131 rows=4 loops=1)
                          Merge Cond: ("outer".res_a_id = "inner".rpo_a_codet)
                          ->  Index Scan using pk_resultat on resultat r  (cost=0.00..9.79 rows=162 width=35) (actual time=0.012..0.032 rows=21 loops=1)
                          ->  Sort  (cost=84.67..84.73 rows=21 width=60) (actual time=0.057..0.066 rows=4 loops=1)
                                Sort Key: sr.rpo_a_codet
                                ->  Index Scan using index_sre_benef_parc on service_rendu sr  (cost=0.00..84.21 rows=21 width=60) (actual time=0.030..0.042 rows=4 loops=1)
                                      Index Cond: (bef_a_id = '00000012'::bpchar)
              ->  Sort  (cost=3.73..3.85 rows=46 width=53) (actual time=0.155..0.176 rows=32 loops=1)
                    Sort Key: s.ser_a_id
                    ->  Seq Scan on service s  (cost=0.00..2.46 rows=46 width=53) (actual time=0.004..0.051 rows=46 loops=1)
        ->  Hash  (cost=1.25..1.25 rows=1 width=16) (actual time=0.027..0.027 rows=1 loops=1)
              ->  HashAggregate  (cost=1.23..1.24 rows=1 width=16) (actual time=0.022..0.022 rows=1 loops=1)
                    ->  Seq Scan on parcours_extranet  (cost=0.00..1.23 rows=1 width=16) (actual time=0.006..0.012 rows=1 loops=1)
                          Filter: ((bef_a_id = '00000012'::bpchar) AND (bef_a_id = '00000012'::bpchar))
Total runtime: 0.688 ms

Volumes actuels :
resultat               162  table paramètre, ça ne va pas beaucoup bouger
service_rendu    6 000 000
service                 46  (table parametre)
parcours_extranet       17 (celle-là va se remplir)

Il n'y a pas de clé étrangère entre service_rendu et parcours_extranet.

Je pense que pour avoir une idée de ce que cela va donner en production, il faut en test charger la table parcours_extranet avec un bon volume (de l'ordre de 500 000 lignes ), mais mon chef n'est pas chaud (trop long à faire car on n'a aucun outil pour le moment...).
Puis-je déduire quelque chose du plan, sachant que je n'ai pas les bons volumes?
Si je fais ce qu'il faut pour remplir les tables, y a-t-il un point sur lequel je doive faire attention afin que cela soit significatif (répartition des données...). Y a-t-il des spécificités de Postgres que  je doive prendre en compte (on est en 8.1)?

Avez-vous un conseil à me donner?

Marc Cousin

Membre

Re : Question sur une requête

Bonjour Flo

Le temps de la requête va surtout dépendre du nombre d'enregistrement répondant au critère bef_a_id, dans sr.

À l'heure actuelle, il y a un scan sequentiel sur parcours_extranet, mais la table étant toute petite, il utilisera un index quand elle sera plus grosse. Vérifie bien que bef_a_id est bien indexé, l'idéal étant un index composé sur bef_a_id et prs_d_debut : l'optimiseur ira directement chercher LE bon enregistrement en réécrivant
SELECT MIN (prs_d_debut) as min_date, bef_a_id
              FROM parcours_extranet
              WHERE bef_a_id = '00000012'
              group by bef_a_id

en

SELECT prs_d_debut as min_date, bef_a_id
              FROM parcours_extranet
              WHERE bef_a_id = '00000012'
              order by bef_a_id
              LIMIT 1

Donc un coût quasi nul pour la sous requête.

Dernière modification par Marc Cousin (03/03/2010 15:29:58)

---

Marc.

flo

Membre

Re : Question sur une requête

Normalement, peu d'enregistrements vont répondre au critère bef_a_id dans sr (de l'ordre de quelques dizaines maximum). Sur cette table (service_rendu alias sr), il y a des index (13 index !!!), dont 3  ont bef_a_id comme première colonne.

Sur parcours_extranet, la clé primaire a comme première colonne  bef_a_id, mais il n'y a pas d'index sur bef_a_id et prs_d_debut...

Sinon, tu voulais écrire :
order by prs_d_debut
              LIMIT 1
je suppose?

Dernière modification par flo (03/03/2010 16:18:55)

Marc Cousin

Membre

Re : Question sur une requête

Oui, évidemment.

---

Marc.