Vous n'êtes pas identifié(e).
Bonjour à tous,
Pourriez-vous m'expliquer pourquoi ma requête est si longue, alors que des Index sont utilisés ?
Ma requête :
SELECT
alim_dwh.dwh_acc_isis.RGTP_CODE,
alim_dwh.dwh_codelec.LIBELLE_ACTIVITE,
alim_dwh.dwh_codelec.LIBELLE_GROUPE,
alim_dwh.dwh_calendrier.dwh_mois_affectation,
alim_dwh.dwh_calendrier.DWH_annee,
dwh_mois_glissant_1.sem_deb,
dwh_mois_glissant_1.sem_fin,
sum(alim_dwh.dwh_vte_hbd.DWH_Marge_val_hbd),
sum(alim_dwh.dwh_vte_hbd.dwh_ca_ht_avec_droit_taxes)
FROM
alim_dwh.dwh_acc_isis INNER JOIN alim_dwh.dwh_gtin ON (alim_dwh.dwh_gtin.aacc_number=alim_dwh.dwh_acc_isis.aacc_numero)
INNER JOIN alim_dwh.dwh_vte_hbd ON (alim_dwh.dwh_vte_hbd.dwh_gtin=alim_dwh.dwh_gtin.gtin)
INNER JOIN alim_dwh.dwh_calendrier ON (alim_dwh.dwh_vte_hbd.dwh_anneesem=alim_dwh.dwh_calendrier.dwh_anneesem)
INNER JOIN (
select alim_dwh.f_mois_glissant_moins_n_deb('2010S13' , 1) as sem_deb, alim_dwh.f_mois_glissant_moins_n_fin('2010S13', 1) as sem_fin
) dwh_mois_glissant_1 ON (alim_dwh.dwh_vte_hbd.dwh_anneesem >= dwh_mois_glissant_1.sem_deb and alim_dwh.dwh_vte_hbd.dwh_anneesem <= dwh_mois_glissant_1.sem_fin)
INNER JOIN alim_dwh.dwh_codelec ON (alim_dwh.dwh_codelec.activite=alim_dwh.dwh_gtin.activite and alim_dwh.dwh_codelec.groupe=alim_dwh.dwh_gtin.groupe and alim_dwh.dwh_codelec.secteur=alim_dwh.dwh_gtin.secteur and
alim_dwh.dwh_codelec.sous_secteur=alim_dwh.dwh_gtin.sous_secteur)
WHERE
alim_dwh.dwh_acc_isis.RGTP_CODE in ('3')
GROUP BY
alim_dwh.dwh_acc_isis.RGTP_CODE,
alim_dwh.dwh_codelec.LIBELLE_ACTIVITE,
alim_dwh.dwh_codelec.LIBELLE_GROUPE,
alim_dwh.dwh_calendrier.dwh_mois_affectation,
alim_dwh.dwh_calendrier.DWH_annee,
dwh_mois_glissant_1.sem_deb,
dwh_mois_glissant_1.sem_finLe plan d'exécution :
"HashAggregate (cost=865580.19..868477.45 rows=193151 width=122) (actual time=2107489.872..2107490.682 rows=123 loops=1)"
" -> Hash Join (cost=643.09..861234.29 rows=193151 width=122) (actual time=313.614..2107215.109 rows=115085 loops=1)"
" Hash Cond: ((dwh_vte_hbd.dwh_anneesem)::text = (dwh_calendrier.dwh_anneesem)::text)"
" -> Nested Loop (cost=564.74..858231.43 rows=198599 width=119) (actual time=311.272..2107054.459 rows=115085 loops=1)"
" Join Filter: (((dwh_vte_hbd.dwh_anneesem)::text >= ((alim_dwh.f_mois_glissant_moins_n_deb('2010S13'::character varying, 1)))::text) AND ((dwh_vte_hbd.dwh_anneesem)::text <= ((alim_dwh.f_mois_glissant_moins_n_fin('2010S13'::character varying, 1)))::text))"
" -> Result (cost=0.00..0.51 rows=1 width=0) (actual time=2.266..2.267 rows=1 loops=1)"
" -> Nested Loop (cost=564.74..831420.08 rows=1787389 width=55) (actual time=173.747..2103929.348 rows=4378669 loops=1)"
" -> Hash Join (cost=564.74..49998.47 rows=12179 width=53) (actual time=4.946..3314.409 rows=44047 loops=1)"
" Hash Cond: ((dwh_gtin.aacc_number)::text = (dwh_acc_isis.aacc_numero)::text)"
" -> Nested Loop (cost=0.00..49143.61 rows=44888 width=72) (actual time=0.157..3065.498 rows=786443 loops=1)"
" -> Seq Scan on dwh_codelec (cost=0.00..979.54 rows=45154 width=50) (actual time=0.006..76.303 rows=45154 loops=1)"
" -> Index Scan using idx_dwh_gtin_codelec on dwh_gtin (cost=0.00..1.05 rows=1 width=47) (actual time=0.021..0.059 rows=17 loops=45154)"
" Index Cond: (((dwh_gtin.activite)::text = (dwh_codelec.activite)::text) AND ((dwh_gtin.groupe)::text = (dwh_codelec.groupe)::text) AND ((dwh_gtin.secteur)::text = (dwh_codelec.secteur)::text) AND ((dwh_gtin.sous_secteur)::text = (dwh_codelec.sous_secteur)::text))"
" -> Hash (cost=546.97..546.97 rows=1422 width=24) (actual time=1.799..1.799 rows=1406 loops=1)"
" -> Index Scan using dwh_acc_isis_rgtp_code on dwh_acc_isis (cost=0.00..546.97 rows=1422 width=24) (actual time=0.024..1.286 rows=1406 loops=1)"
" Index Cond: ((rgtp_code)::text = '3'::text)"
" -> Index Scan using "IDX_GTIN" on dwh_vte_hbd (cost=0.00..62.32 rows=147 width=30) (actual time=3.422..47.492 rows=99 loops=44047)"
" Index Cond: ((dwh_vte_hbd.dwh_gtin)::text = (dwh_gtin.gtin)::text)"
" -> Hash (cost=48.99..48.99 rows=2349 width=19) (actual time=2.316..2.316 rows=2349 loops=1)"
" -> Seq Scan on dwh_calendrier (cost=0.00..48.99 rows=2349 width=19) (actual time=0.016..1.412 rows=2349 loops=1)"
"Total runtime: 2107493.094 ms"
Total runtime = 35 minutes ! 
Vous m'aviez conseillé une fois précédente de m'aider de ce site : http://explain.depesz.com/s/lYY mais je n'arrive pas à savoir quoi faire à partir des indications fournies.
Gôm
Hors ligne
Ce qui coûte:
Index Scan using IDX_GTIN on dwh_vte_hbd (cost=0.00..62.32 rows=147 width=30) (actual time=3.422..47.492 rows=99 loops=44047)
Index Cond: ((dwh_vte_hbd.dwh_gtin)::text = (dwh_gtin.gtin)::text)
Il fait 44000 parcours de l'index, chacune rapportant une centaine d'enregistrement, qui n'ont pas l'air en cache, et très fragmentés (45ms de temps d'accès pour ces 100 enregistrements, en moyenne).
Marc.
Hors ligne
Oui c'est bizarre, non ?
Je croyais que PostgreSQL était capable de mettre en cache et donc de ne pas avoir à reparcourir l'Index autant de fois ?
Gôm
Hors ligne
Il met en cache les blocs qu'il a utilisé dernièrement. Il ne peut pas mettre en cache plus que ce que le serveur a de mémoire. Et les pages de cet index rentrent évidemment en concurrence avec tous les autres index et tables du système…
Marc.
Hors ligne
Voici les statistiques de cet Index selon PgAdmin :
Parcours d'index : 2007658
Lignes d'index lues : 125076152
Lignes d'index récupérées : 125023331
Lecture des blocs d'index : 2588147
Accès aux blocs d'index : 16206391
Taille Index : 3842 MB
-- Index: alim_dwh."IDX_GTIN"
-- DROP INDEX alim_dwh."IDX_GTIN";
CREATE INDEX "IDX_GTIN"
ON alim_dwh.dwh_vte_hbd
USING btree
(dwh_gtin);N'ai-je pas un moyen d'optimiser cet Index et donc ma requête ? Pour info, elle met 35 minutes à s'exécuter et j'ai un timeout à 30 minutes dans mon application ce qui fait que mes utilisateurs n'ont plus leurs données !
Avant ça devait passer juste juste et maintenant, suite à un accroissement conséquent de la volumétrie, ça ne passe plus !
Gôm
PS : La table sur laquelle pointe cet Index contient 127.713.732 lignes pour 116 colonnes (soit 22 GB).
Dernière modification par gom (27/04/2011 15:41:05)
Hors ligne
À part vous dire que votre système disque a l'air un peu lent, et que la table a l'air fragmentée, je ne vois pas comment vous pourriez optimiser ça de façon simple. À part réorganiser alim_dwh sur dwh_gtin (avec une commande cluster).
Vous pouvez essayer de faire fonctionner cette requête avec une valeur élevée de work_mem (disons 100Mo pour commencer), pour voir s'il ne bascule pas sur un plan n'utilisant plus cet index.
Marc.
Hors ligne
Le work_mem est déjà à 300 MB.
Gôm
Hors ligne
Ah. Alors c'est vraisemblablement qu'il n'y a pas mieux comme plan, à priori.
Essayez sinon d'augmenter le random_page_cost, vu que les lectures aléatoires sont apparemment assez mauvaises.
Marc.
Hors ligne
Il est actuellement à 2, alors que la valeur par défaut est 4.0 (je crois).
Que dois-je faire ?
Gôm
Hors ligne
Essaye de le passer à 4 pour cette requête («set random_page_cost to 4» avant d'exécuter la requête).
Marc.
Hors ligne
Je viens de le faire et la requête est passée en 48 secondes !!!
Plan d'exécution :
http://explain.depesz.com/s/yZH
HashAggregate (cost=2195131.19..2198028.46 rows=193151 width=122) (actual time=48528.780..48529.558 rows=123 loops=1)
-> Hash Join (cost=352971.88..2190785.29 rows=193151 width=122) (actual time=5137.624..48351.471 rows=115085 loops=1)
Hash Cond: ((dwh_vte_hbd.dwh_anneesem)::text = (dwh_calendrier.dwh_anneesem)::text)
-> Hash Join (cost=352893.52..2187782.44 rows=198599 width=119) (actual time=5118.187..48242.015 rows=115085 loops=1)
Hash Cond: ((dwh_vte_hbd.dwh_gtin)::text = (dwh_gtin.gtin)::text)
-> Nested Loop (cost=303286.20..2082975.07 rows=14190414 width=94) (actual time=3485.537..39236.326 rows=3758060 loops=1)
-> Result (cost=0.00..0.51 rows=1 width=0) (actual time=13.882..13.884 rows=1 loops=1)
-> Bitmap Heap Scan on dwh_vte_hbd (cost=303286.20..1870118.34 rows=14190414 width=30) (actual time=3471.640..33985.951 rows=3758060 loops=1)
Recheck Cond: (((dwh_vte_hbd.dwh_anneesem)::text >= ((alim_dwh.f_mois_glissant_moins_n_deb('2010S13'::character varying, 1)))::text) AND ((dwh_vte_hbd.dwh_anneesem)::text <= ((alim_dwh.f_mois_glissant_moins_n_fin('2010S13'::character varying, 1)))::text))
-> Bitmap Index Scan on dwh_vte_hbd_a_s_idx (cost=0.00..299738.59 rows=14190414 width=0) (actual time=3409.026..3409.026 rows=3758060 loops=1)
Index Cond: (((dwh_vte_hbd.dwh_anneesem)::text >= ((alim_dwh.f_mois_glissant_moins_n_deb('2010S13'::character varying, 1)))::text) AND ((dwh_vte_hbd.dwh_anneesem)::text <= ((alim_dwh.f_mois_glissant_moins_n_fin('2010S13'::character varying, 1)))::text))
-> Hash (cost=49455.09..49455.09 rows=12179 width=53) (actual time=1632.401..1632.401 rows=44047 loops=1)
-> Merge Join (cost=46146.67..49455.09 rows=12179 width=53) (actual time=1420.420..1611.648 rows=44047 loops=1)
Merge Cond: (((dwh_gtin.sous_secteur)::text = (dwh_codelec.sous_secteur)::text) AND ((dwh_gtin.activite)::text = (dwh_codelec.activite)::text) AND ((dwh_gtin.groupe)::text = (dwh_codelec.groupe)::text) AND ((dwh_gtin.secteur)::text = (dwh_codelec.secteur)::text))
-> Sort (cost=41676.08..42221.70 rows=218250 width=28) (actual time=887.971..897.210 rows=45509 loops=1)
Sort Key: dwh_gtin.sous_secteur, dwh_gtin.activite, dwh_gtin.groupe, dwh_gtin.secteur
Sort Method: quicksort Memory: 5447kB
-> Hash Join (cost=1452.68..22322.08 rows=218250 width=28) (actual time=131.435..580.238 rows=45509 loops=1)
Hash Cond: ((dwh_gtin.aacc_number)::text = (dwh_acc_isis.aacc_numero)::text)
-> Seq Scan on dwh_gtin (cost=0.00..15670.43 rows=804393 width=47) (actual time=0.007..213.441 rows=804393 loops=1)
-> Hash (cost=1434.90..1434.90 rows=1422 width=24) (actual time=131.378..131.378 rows=1406 loops=1)
-> Seq Scan on dwh_acc_isis (cost=0.00..1434.90 rows=1422 width=24) (actual time=9.394..130.611 rows=1406 loops=1)
Filter: ((rgtp_code)::text = '3'::text)
-> Sort (cost=4470.52..4583.41 rows=45154 width=50) (actual time=531.890..543.423 rows=79387 loops=1)
Sort Key: dwh_codelec.sous_secteur, dwh_codelec.activite, dwh_codelec.groupe, dwh_codelec.secteur
Sort Method: quicksort Memory: 7148kB
-> Seq Scan on dwh_codelec (cost=0.00..979.54 rows=45154 width=50) (actual time=0.379..59.800 rows=45154 loops=1)
-> Hash (cost=48.99..48.99 rows=2349 width=19) (actual time=19.407..19.407 rows=2349 loops=1)
-> Seq Scan on dwh_calendrier (cost=0.00..48.99 rows=2349 width=19) (actual time=4.588..18.417 rows=2349 loops=1)
Total runtime: 48553.438 ms
A croire que ce n'est plus la même requête alors que si !!!
Gôm
Hors ligne
Donc c'est que les temps d'accès des disques est assez mauvais. En tout cas pour cette requête. La base de données, en grossissant, doit être moins en cache que précédemment, donc les temps d'accès aléatoires redeviennent plus importants qu'à la période où le random_page_cost a été abaissé.
Marc.
Hors ligne
Quels pourrait être les risques de passer le random_page_cost de 2 à 4 pour mes autres requêtes ?
Gôm
Dernière modification par gom (27/04/2011 16:56:09)
Hors ligne
Une régression massive sur l'ensemble de l'application quand tu vas modifier le paramètre ? Je ne peux pas garantir qu'il y aura régression, mais c'est toujours le risque. Si c'est acceptable de le tester, et que les utilisateurs sont prêts à le supporter, ce n'est pas vraiment un gros risque en soit, puisque le retour en arrière est très rapide.
Marc.
Hors ligne
Dans PgAdmin lorsque je clique sur mon serveur dont je veux modifier le postgresql.conf et que je vais dans Menu "Outils" -> "Configuration du serveur" : "Configuration du serveur" est grisé, alors que sur mes autres serveurs ce n'est pas le cas. Sais-tu pourquoi ?
Gôm
Dernière modification par gom (27/04/2011 17:12:12)
Hors ligne
Parce que le contrib «adminpack» n'est pas installé dans cette instance.
Marc.
Hors ligne
Installation faite et effectivement ... c'est mieux ! 
Merci merci et merci encore pour tout ! 
Gôm
Hors ligne