Re: Problème de temps de réaction

Bonjour,

On Sunday 01 May 2005 06:26, Philippe Dalléas wrote:
>
> Le problème que je rencontre et auquel je ne comprends rien est l'extrême
> lenteur d'obtention des résultats après requête
>
> Le fichier concerné est celui des écritures. Il est ainsi construit à
> l'image exacte de celui dont il provient.
>
> CREATE TABLE ecritu (
> ecritu_societe DECIMAL(2,0) ,
> ecritu_periode VARCHAR(4),
> ecritu_date DATE ,
> ecritu_ligne VARCHAR(8) ,
> ecritu_piece VARCHAR(8) ,
> ecritu_code_piece VARCHAR(2),
> ecritu_ref_piece VARCHAR(12) ,
> ecritu_compte VARCHAR(8) ,
> ecritu_collectif VARCHAR(8),
> ecritu_journal VARCHAR(8) ,
> ecritu_central VARCHAR(1),
> ecritu_libelle VARCHAR(30),
> ecritu_montant DECIMAL(10,2),
> ecritu_sens VARCHAR(1) ,
> ecritu_lettre VARCHAR(2) ,
> ecritu_echeance DATE,
> ecritu_etat VARCHAR(2),
> ecritu_operateur DECIMAL(2,0),
> ecritu_paye DECIMAL(10,2),
> ecritu_trop_percu DECIMAL(10,2),
> ecritu_code_tiers VARCHAR(8),
> ecritu_no_tiers DECIMAL(6,0),
> ecritu_dev_compta VARCHAR(4),
> ecritu_dev_piece VARCHAR(4),
> ecritu_numerateur DECIMAL(8,4),
> ecritu_denominateur DECIMAL(8,4),
> ecritu_mnt_piece DECIMAL(10,2),
> PRIMARY KEY
> (ecritu_societe,ecritu_journal,ecritu_date,ecritu_piece,ecritu_ligne)
> );
> CREATE INDEX ecritu_index_2 ON ecritu
> (ecritu_societe,ecritu_compte,ecritu_date,ecritu_piece,ecritu_ligne);
> CREATE INDEX ecritu_index_3 ON ecritu
> (ecritu_societe,ecritu_compte,ecritu_lettre,ecritu_sens,ecritu_ref_piece);

Déjà il faut impérativement changer les types des champs ... et passer en
FLOAT et en INT un maximum de chose. Tu utilises des VARCHAR par choix ou par
obligation ? Le type TEXT peut-être bien plus efficace sur les index ... à
étudier ...

> En suite, pour "goûter", la requête :
>
> SELECT ecritu_societe,ecritu_compte,ecritu_date,ecritu_piece,ecritu_ligne
> FROM ecritu WHERE ecritu_societe=1 ORDER BY ecritu_societe, ecritu_compte,
> ecritu_date, ecritu_piece, ecritu_ligne USING > ;
>
> Les premières lignes de résultat :
>  ecritu_societe | ecritu_compte | ecritu_date | ecritu_piece | ecritu_ligne
> ----------------+---------------+-------------+--------------+-------------
>- 1 | 10100000      | 2001-05-01  | 00000000     | 00000001 1 | 10100000    
>  | 2002-05-01  | 00000000     | 00000001 1 | 10610000      | 2000-10-31  |
> 00032864     | 00000003 1 | 10610000      | 2001-05-01  | 00000000     |
> 00000002 1 | 10610000      | 2002-05-01  | 00000000     | 00000002 1 |
> 10682000      | 2000-10-31  | 00032864     | 00000002 1 | 10682000      |
> 2001-05-01  | 00000000     | 00000003 1 | 10682000      | 2002-04-30  |
> 00047636     | 00000002 1 | 10682000      | 2002-05-01  | 00000000     |
> 00000003 1 | 10688000      | 2000-10-31  | 00032864     | 00000004 1 |
> 10688000      | 2001-04-30  | 00044994     | 00000001 1 | 10688000      |
> 2001-05-01  | 00000000     | 00000004 1 | 10688000      | 2002-04-30  |
> 00047636     | 00000003 1 | 10688000      | 2002-05-01  | 00000000     |
> 00000004 1 | 12000000      | 2000-10-31  | 00032864     | 00000001 1 |
> 12000000      | 2001-04-30  | 00045004     | 00000131 1 | 12000000      |
> 2001-05-01  | 00000000     | 00000005 1 | 12000000      | 2002-04-30  |
> 00047636     | 00000001 1 | 12000000      | 2002-04-30  | 00047688     |
> 00000130 1 | 12000000      | 2002-05-01  | 00000000     | 00000005
>
> (à l'écran, les caractères sont de pas constant)
>
> Délai d'obtention de la première ligne : 2 mn 20 s
> Délai de cloture de la lecture en touchant la lettre Q pour quitter
> (attente bas de page affichée). : 10 secondes.
>
> J'ai d'abord cru avoir mal formulé ma requête et, celle-ci vérifiée et non
> amendée, je l'ai envoyée de nouveau et, là, le délai d'obtention a été,
> certes, meilleur mais inacceptable : environ 55 secondes.

Deux questions émanent de ton analyse :
- Quelle configuration as-tu au niveau hardware ?
- Quelle version de PostgreSQL ?
- Quels sont tes réglages au niveau de postgresql.conf ?
- Quelle est la valeur de statistic par défaut de ton postgresql.conf ?
- Quel volume de données est censé te retourner cette requête ? 4000 lignes ou
plus ? (cf l'analyze pas fait plus bas)
- As-tu fais un vacuum full verbose analyze après l'import des données ?
Merci de nous indiquer les dernières lignes de cette commande au passage pour
regarder tes paramètres du postgresql.conf

Le coup des 55 secondes semble normal ta table est en mémoire et donc le
résultat est beaucoup plus facile à obtenir ... maintenant 55 secondes pour
800 000 enregistrements je comprends que tu sois pas content ! ;o)

> Toutes ces actions ont été faites sous l'interpréteur psql
>
> Ces résultats me laisse perplexe...
>
> La requête explicative EXPLAIN ANALYZE me donne le résultat suivant :
>
> EXPLAIN ANALYZE SELECT ecritu_societe, ecritu_compte, ecritu_date,
> ecritu_piece, ecritu_ligne FROM ecritu WHERE ecritu_societe=1 ORDER BY
> ecritu_societe, ecritu_compte, ecritu_date, ecritu_piece, ecritu_ligne
> USING
>
> > ;
>
>                                                                   QUERY
> PLAN
> ---------------------------------------------------------------------------
>------------------------------------------------------------------- Sort
>  (cost=16280.70..16290.92 rows=4091 width=49) (actual
> time=51259.830..52246.911 rows=329640 loops=1)
>    Sort Key: ecritu_societe, ecritu_compte, ecritu_date, ecritu_piece,
> ecritu_ligne
>    ->  Index Scan using ecritu_index_3 on ecritu  (cost=0.00..16035.27
> rows=4091 width=49) (actual time=0.411..11878.919 rows=329640 loops=1)
>          Index Cond: (ecritu_societe = 1::numeric)
>  Total runtime: 53683.584 ms
> (5 rows)
>
> Je vois que le système a bien considéré que l'index à utiliser est le
> second (sort key) c'est pourquoi je ne comprends pas pourquoi il semble
> faire son balayage via le 3ème index, qui n'est pas concerné par cette
> requête.

Vu que tu ne passes qu'un paramètre à ta requête ... l'optimiseur estime que
l'index le plus court à parcourir et qui comporte la clé ecritu_societe est
l'index 3 ... ce qui me laisse penser si tu fais souvent ce type de requête
mono paramètre qu'il vaudrait mieux créer un index sur ce seul paramètre
plutôt que de faire des index à rallonge ... mis à part pour les tris ...

Cordialement,
--
Hervé Piedvache

NOUVELLE ADRESSE - NEW ADDRESS :
Elma Ingénierie Informatique
3 rue d'Uzès
F-75002 - Paris - France
Pho. 33-144949901
Fax. 33-144882747