Forums PostgreSQL.fr

gom

Membre

Comment identifier précisément l'Index qu'il faut créer ?

Bonjour à tous,

J'ai des requêtes qui prennent beaucoup de temps à s'exécuter et je pense qu'il me manque des Index.

SELECT
  alim.ref_accord.code_gt,
  alim.ref_gtin.code_gtin,
  alim.ref_accord.num_accord,
  alim.ref_accord.adresse_raissoc,
  alim.ref_gtin.lib_prd,
  alim.ref_centrale.nom_centrale,
  alim.ventes.srp,
  sum(alim.ventes.dor_qte),
  alim.calendrier.annee,
  alim.calendrier.semaine,
  alim.ventes.ide_source,
  alim.ventes.srp_modal,
  alim.ventes.srp_theo
FROM
  alim.ref_gtin,
  alim.ref_accord,
  alim.ref_centrale,
  alim.ventes,
  alim.calendrier
WHERE
  ( alim.ref_gtin.aacc_number=alim.ref_accord.num_accord  )
  AND  ( alim.ref_centrale.nom_centrale=alim.ventes.code_sca  )
  AND  ( alim.ref_gtin.code_gtin=alim.ventes.gtin  )
  AND  ( alim.calendrier.cal_anneesem=alim.ventes.anneesem  )
  AND  
  (
   ( alim.ref_accord.code_gt in ('1', '2', 'A') )
   AND
   ( alim.calendrier.cal_anneesem = '2009S36' )
   AND alim.ventes.srp is not null  
  )
GROUP BY
  alim.ref_accord.code_gt, 
  alim.ref_gtin.code_gtin, 
  alim.ref_accord.num_accord, 
  alim.ref_accord.adresse_raissoc, 
  alim.ref_gtin.lib_prd, 
  alim.ref_centrale.nom_centrale, 
  alim.ventes.srp, 
  alim.calendrier.annee, 
  alim.calendrier.semaine, 
  alim.ventes.ide_source, 
  alim.ventes.srp_modal, 
  alim.ventes.srp_theo

HashAggregate  (cost=12079946.56..12083307.93 rows=268910 width=132)
  ->  Hash Join  (cost=53208.81..12071206.98 rows=268910 width=132)
        Hash Cond: ((ref_gtin.aacc_number)::text = (ref_accord.num_accord)::text)
        ->  Hash Join  (cost=48784.97..12060396.53 rows=268910 width=109)
              Hash Cond: ((ventes.code_sca)::text = (ref_centrale.nom_centrale)::text)
              ->  Nested Loop  (cost=48783.10..12056697.14 rows=268910 width=100)
                    ->  Index Scan using calendrier_anneesem_idx on calendrier  (cost=0.00..4.27 rows=1 width=16)
                          Index Cond: ((anneesem)::text = '2010S10'::text)
                    ->  Hash Join  (cost=48783.10..12054003.77 rows=268910 width=100)
                          Hash Cond: ((ventes.gtin)::text = (ref_gtin.code_gtin)::text)
                          ->  Seq Scan on ventes  (cost=0.00..11999170.20 rows=268910 width=44)
                                Filter: ((srp IS NOT NULL) AND ((cal_anneesem)::text = '2009S36'::text))
                          ->  Hash  (cost=41930.82..41930.82 rows=548182 width=70)
                                ->  Seq Scan on ref_gtin  (cost=0.00..41930.82 rows=548182 width=70)
              ->  Hash  (cost=1.39..1.39 rows=39 width=14)
                    ->  Seq Scan on ref_centrale  (cost=0.00..1.39 rows=39 width=14)
        ->  Hash  (cost=3980.04..3980.04 rows=35504 width=44)
              ->  Seq Scan on ref_accord  (cost=0.00..3980.04 rows=35504 width=44)

Faut-il que je crée 3 Index sur la table "ventes", un sur chacun des champs suivants, ou un seul Index regroupant ces 3 champs … ou 4 Index ?

- code_sca
- gtin
- anneesem

Le fait que je n'ai aucune condition (rien dans le WHERE) portant sur l'un de ces champs de ma table "ventes" entre-t-il en compte ? Si c'était le cas est-ce que la réponse à ma question ci-dessus aurait changée ?

Si je vous pose toutes ces questions, c'est parce que ma table "ventes" a beaucoup de lignes (count(*) = 319 millions environ) et je ne veux pas perdre du temps à créer des Index pour rien. Surtout qu'il faut surement que je fasse de la maintenance sur cette table après (ANALYSE ou je ne sais quoi), non ?

Gôm

Dernière modification par gom (19/03/2010 10:41:21)

gleu

Administrateur

Re : Comment identifier précisément l'Index qu'il faut créer ?

Un EXPLAIN ANALYZE donne beaucoup plus d'informations et permettrait de répondre à vos questions. Là, on manque d'éléments.

Tout ce qu'on voit là, c'est qu'il estime que toute la charge de la requête se fera sur le Seq Scan de ventes. Donc quelques infos supplémentaires seraient intéressantes :

* EXPLAIN ANALYZE de la requête
* création d'un index sur anneesem de ventes, puis ANALYZE, puis EXPLAIN ANALYZE de la requête
* SELECT anneesem, count(*) FROM ventes GROUP BY anneesem

---

Guillaume.

gom

Membre

Re : Comment identifier précisément l'Index qu'il faut créer ?

* EXPLAIN ANALYZE de la requête
* création d'un index sur anneesem de ventes, puis ANALYZE, puis EXPLAIN ANALYZE de la requête
* SELECT anneesem, count(*) FROM ventes GROUP BY anneesem

1/

Veut pas s'afficher malgré une attente de 10 minutes, ça demande combien de temps environ ?

2/

CREATE INDEX idx_ventes_anneesem ON alim.ventes USING btree (anneesem) TABLESPACE "TBS_FACT_INDEX"

J'ai lancé la création à 10h00 et elle n'était toujours pas finie. J'ai des traitements à 12h00, donc j'ai fait un pg_cancel_backend.

3/

Toujours pas de résultat depuis plusieurs minutes ... normal puisque l'Index n'a pu être créé, non ?

Gôm

gleu

Administrateur

Re : Comment identifier précisément l'Index qu'il faut créer ?

Veut pas s'afficher malgré une attente de 10 minutes, ça demande combien de temps environ ?

Le temps normal d'exécution de la requête (je veux dire, sans le EXPLAIN ANALYZE).

J'ai lancé la création à 10h00 et elle n'était toujours pas finie. J'ai des traitements à 12h00, donc j'ai fait un pg_cancel_backend.

Elle peut avoir été bloquée ailleurs. À mon avis, c'est actuellement, avec les infos que vous nous avez fourni, votre meilleure chance pour de meilleures perfs.

Toujours pas de résultat depuis plusieurs minutes ... normal puisque l'Index n'a pu être créé, non ?

Oui et non. Vous pouvez aussi utiliser les deux requêtes suivantes :

SELECT count(*) FROM ventes;
et
SELECT count(*) FROM ventes WHERE anneesem = '2009S36';

L'idée est d'avoir une estimation de l'intérêt d'un index pour cette colonne.

---

Guillaume.

gom

Membre

Re : Comment identifier précisément l'Index qu'il faut créer ?

OK, je vais relancer la création de l'index sur le temps du midi ... je verrai bien.

Petite extra question, j'ai 3 Index qui existent déjà sur cette table là :

CREATE INDEX idx_ventes_pk
  ON alim.ventes
  USING btree
  (sem, annee, gtin, code_sca)
TABLESPACE "TBS_FACT_INDEX";

CREATE INDEX idx_ventes_gtin
  ON alim.ventes
  USING btree
  (gtin)
TABLESPACE "TBS_FACT_INDEX";

CREATE INDEX idx_ventes_s_a
  ON alim.ventes
  USING btree
  (sem, annee)
TABLESPACE "TBS_FACT_INDEX";

Ne faut-il pas créer le même que le 1er Index avec "anneesem" à la place de "sem, annee" ? Au lieu de créer un Index que pour "anneesem" ?

Gôm

gleu

Administrateur

Re : Comment identifier précisément l'Index qu'il faut créer ?

Vous pouvez tenter le coup mais rien ne dit que ce sera mieux.

---

Guillaume.

gom

Membre

Re : Comment identifier précisément l'Index qu'il faut créer ?

Un EXPLAIN ANALYZE donne beaucoup plus d'informations et permettrait de répondre à vos questions. Là, on manque d'éléments.

Tout ce qu'on voit là, c'est qu'il estime que toute la charge de la requête se fera sur le Seq Scan de ventes. Donc quelques infos supplémentaires seraient intéressantes :

* EXPLAIN ANALYZE de la requête
* création d'un index sur anneesem de ventes, puis ANALYZE, puis EXPLAIN ANALYZE de la requête
* SELECT anneesem, count(*) FROM ventes GROUP BY anneesem

Donc ...

Création de l'Index et ANALYSE :

CREATE INDEX idx_ventes_as ON alim.ventes USING btree (anneesem) TABLESPACE "TBS_FACT_INDEX";

ANALYSE alim.ventes;

EXPLAIN ANALYSE (avec nouvel Index) :

HashAggregate  (cost=4111903.79..4116085.58 rows=334543 width=132) (actual time=22123.576..22705.905 rows=436143 loops=1)
  ->  Hash Join  (cost=97220.23..4101031.15 rows=334543 width=132) (actual time=6751.880..20976.364 rows=436143 loops=1)
        Hash Cond: ((ref_gtin.aacc_number)::text = (ref_accord.num_accord)::text)
        ->  Hash Join  (cost=92796.39..4088661.91 rows=334543 width=109) (actual time=6387.703..20033.292 rows=471410 loops=1)
              Hash Cond: ((ventes.code_sca)::text = (ref_centrale.nom_centrale)::text)
              ->  Nested Loop  (cost=92794.52..4084060.07 rows=334543 width=100) (actual time=6387.106..19556.842 rows=471410 loops=1)
                    ->  Index Scan using calendrier_anneesem_idx on calendrier  (cost=0.00..4.27 rows=1 width=16) (actual time=12.553..12.557 rows=1 loops=1)
                          Index Cond: ((cal_anneesem)::text = '2010S10'::text)
                    ->  Hash Join  (cost=92794.52..4080710.37 rows=334543 width=100) (actual time=6374.536..19225.554 rows=471410 loops=1)
                          Hash Cond: ((ventes.gtin)::text = (ref_gtin.code_gtin)::text)
                          ->  Bitmap Heap Scan on ventes  (cost=44011.42..4024400.05 rows=334543 width=44) (actual time=551.201..12674.660 rows=476267 loops=1)
                                Recheck Cond: ((cal_anneesem)::text = '2010S10'::text)
                                Filter: (srp IS NOT NULL)
                                ->  Bitmap Index Scan on idx_ventes_as  (cost=0.00..43927.78 rows=3387203 width=0) (actual time=526.177..526.177 rows=2814044 loops=1)
                                      Index Cond: ((cal_anneesem)::text = '2010S10'::text)
                          ->  Hash  (cost=41930.82..41930.82 rows=548182 width=70) (actual time=5823.159..5823.159 rows=548954 loops=1)
                                ->  Seq Scan on ref_gtin  (cost=0.00..41930.82 rows=548182 width=70) (actual time=3.932..5397.315 rows=548954 loops=1)
              ->  Hash  (cost=1.39..1.39 rows=39 width=14) (actual time=0.566..0.566 rows=39 loops=1)
                    ->  Seq Scan on ref_centrale  (cost=0.00..1.39 rows=39 width=14) (actual time=0.524..0.540 rows=39 loops=1)
        ->  Hash  (cost=3980.04..3980.04 rows=35504 width=44) (actual time=364.155..364.155 rows=35504 loops=1)
              ->  Seq Scan on ref_accord  (cost=0.00..3980.04 rows=35504 width=44) (actual time=6.399..338.508 rows=35504 loops=1)
Total runtime: 22832.695 ms

Gôm

Dernière modification par gom (19/03/2010 16:28:13)

gleu

Administrateur

Re : Comment identifier précisément l'Index qu'il faut créer ?

Donc l'index est mieux pour les perfs ou pas ? on ne peut pas se fier au premier EXPLAIN vu que le filtre a changé (2010S10 au lieu de 2009S36).

---

Guillaume.

Marc Cousin

Membre

Re : Comment identifier précisément l'Index qu'il faut créer ?

Donc l'index était intéressant (postgres a décidé de l'utiliser). Un index composé sur cal_annesem et srp serait encore meilleur : vous êtes allés chercher 3 millions d'enregistrements au lieu de 450000 si vous aviez eu l'index composé.

Ou si votre index n'est là que pour cette requête, et que le critère srp is not null est toujours vérifié, vous pouvez faire un index conditionnel :
create index idx_ventes_as_srpnull on alim.ventes (anneesem) where srp is not null.

Votre index sera à vue de nez 5 fois plus petit que l'index actuel, et capable de répondre directement à la question posée.

Dernière modification par Marc Cousin (19/03/2010 16:44:32)

---

Marc.

gom

Membre

Re : Comment identifier précisément l'Index qu'il faut créer ?

Le rapport BO s'affiche en 1 min 40 secondes au pire ... il mettait entre 10 et 15 minutes d'après les utilisateurs !

Donc pour répondre à gleu ... oui l'Index est mieux pour les perfs !

Pour info, la requête seule depuis PgAdmin III dure 23 secondes ... BO a encore du boulot à faire pour la vitesse de rendu ! Enfin, y a tout de même 150 pages à sortir au format A3 !

Gôm

gleu

Administrateur

Re : Comment identifier précisément l'Index qu'il faut créer ?

Et pourtant, pgAdmin n'est pas connu pour sa grande célérité dans l'affichage du grid

---

Guillaume.

Marc Cousin

Membre

Re : Comment identifier précisément l'Index qu'il faut créer ?

En optimisation supplémentaire, rajouter la clause where dans l'index devrait encore fournir une accélération supplémentaire (encore 5 fois moins de données à lire).

---

Marc.

gom

Membre

Re : Comment identifier précisément l'Index qu'il faut créer ?

OK Marc, sauf que cet Index est utile pour d'autres requêtes où les valeurs null de "srp" ne sont PAS à exclure.

Je crée 2 Index, 1 avec le where et 1 sans  ?

Gôm

Dernière modification par gom (19/03/2010 18:40:00)

gleu

Administrateur

Re : Comment identifier précisément l'Index qu'il faut créer ?

C'est à tester. PostgreSQL peut les combiner en mémoire s'il trouve cela intéressant pour les performances globales de la requête.

---

Guillaume.

gom

Membre

Re : Comment identifier précisément l'Index qu'il faut créer ?

"Les combiner" ?! J'abuse un peu mais est-il possible d'avoir une petite explication ?

"C'est à tester" : est-ce que ça veut dire que 2 Index peut poser un quelconque problème ou que simplement un des 2 risque de ne servir à rien ?

Gôm

gleu

Administrateur

Re : Comment identifier précisément l'Index qu'il faut créer ?

Je voulais simplement dire qu'un seul des deux index pourrait être utilisé, sauf si PostgreSQL estime qu'il est plus intéressant d'utiliser les deux.

Quant à la combinaison, PostgreSQL est capable de calculer un bitmap index en mémoire pour chaque index. Ce qui fait que si vous avez « col1=X AND col2=Y », après avoir créé les deux index bitmap, il n'a plus qu'à faire un AND sur les deux bitmaps et il obtient extrêmement rapidement le résultat du filtre (à condition qu'il y ait peu de lignes dans lesindex bitmap).

---

Guillaume.