Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
J'utilise un base postgreSQL 8.4 sur un serveur Linux redhat 4.1.2.
Je dois exécuter en production une requête 50 fois avec des paramètres différents. Le problème est que cette requêtes dure 3h à chaque fois :
update table_cible aa
set indicateur_moyen_mensuel = (select sum(b.indicateur)/12
from table_source a
join table_source b on (a.code = b.code and '2011S02' <= b.annee_semaine and b.annee_semaine <= a.annee_semaine)
where a.annee_semaine = '2012S01'
and aa.code = a.code
and aa.annee_semaine = a.annee_semaine
group by a.code,a.annee_semaine)
where annee_semaine = '2012S01';
Présentation de la requête : Calcul de table_cible.indicateur_moyen_mensuel qui est la moyenne de table_source.indicateur sur les douze derniers mois. On fait donc une auto-jointure.
La clé fonctionnelle en source et en cible est le code et l'annee_semaine.
La table_source fait 35 Go et contient 200M de lignes. Pour une annee_semaine donnée, le nombre de lignes sur les 12 derniers mois est de 42M. C'est donc le nombre de lignes lues dans table_source.
La table_cible fait 530 Mo et contient 6,5M de lignes.
Le plan d'exécution utilise un index btree sur table_source(code, annee_semaine) (nommé index_source).
Le plan était le suivant :
"Seq Scan on table_cible aa (cost=0.00..11236640.54 rows=122839 width=60)"
" Filter: ((annee_semaine)::text = '2012S01'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..90.25 rows=1 width=26)"
" -> Result (cost=0.00..90.07 rows=21 width=26)"
" One-Time Filter: (($1)::text = '2012S01'::text)"
" -> Nested Loop (cost=0.00..90.07 rows=21 width=26)"
" -> Index Scan using index_source on table_source a (cost=0.00..24.63 rows=1 width=22)"
" Index Cond: (((code)::text = ($0)::text) AND ((annee_semaine)::text = '2012S01'::text))"
" -> Index Scan using index_source on table_source b (cost=0.00..65.18 rows=21 width=26)"
" Index Cond: (((b.code)::text = ($0)::text) AND ('2011S02'::text <= (b.annee_semaine)::text) AND ((b.annee_semaine)::text <= (a.annee_semaine)::text))"
En recette, elle dure entre 20mn et 1h30 selon les semaines (alors que la volumétrie est la même sur toutes les semaines et similaire à celle de production).
La seule piste d'optimisation que j'ai trouvé est de créer un index btree sur table_cible.annee_semaine (nommé index_cible).
Le plan d'exécution devient le suivant :
"Bitmap Heap Scan on table_cible aa (cost=1587.86..10889632.89 rows=119904 width=60)"
" Recheck Cond: ((annee_semaine)::text = '2012S01'::text)"
" -> Bitmap Index Scan on index_cible (cost=0.00..1557.89 rows=119904 width=0)"
" Index Cond: ((annee_semaine)::text = '2012S01'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..90.25 rows=1 width=26)"
" -> Result (cost=0.00..90.07 rows=21 width=26)"
" One-Time Filter: (($1)::text = '2012S01'::text)"
" -> Nested Loop (cost=0.00..90.07 rows=21 width=26)"
" -> Index Scan using index_source on agr_produit a (cost=0.00..24.63 rows=1 width=22)"
" Index Cond: (((code)::text = ($0)::text) AND ((annee_semaine)::text = '2012S01'::text))"
" -> Index Scan using index_source on agr_produit b (cost=0.00..65.18 rows=21 width=26)"
" Index Cond: (((b.code)::text = ($0)::text) AND ('2011S02'::text <= (b.annee_semaine)::text) AND ((b.annee_semaine)::text <= (a.annee_semaine)::text))"
En, recette, la requête a duré 45mn. C'est toujours beauoup trop.
Je suis à court d'idée. Quelqu'un pourrait-il m'aider ?
Merci d'avance
Jibc
Hors ligne
Comme vous ne donnez que le EXPLAIN, et pas EXPLAIN ANALYZE, on va jouer aux devinettes. À priori, il vous manque un index sur la colonne annee_semaine de la table table_cible.
Si ce n'est pas ça, merci d'inclure la définition des tables et un EXPLAIN ANALYZE de la requête.
Guillaume.
Hors ligne
Merci de votre réponse.
Ce que j'ai omis de préciser avant de poster mon commentaire, c'est que j'avais déjà créé l'index sur table_cible en production, et que c'était en cours.
Ca dure entre 1h30 et 3h par semaine avec cet index sur table_cible. Trop long pour 50 semaines à calculer...
Mon avis pour expliquer la longueur de ce traitement est que la requête parcours 1/5 de table_source (42M de ligne sur 200M) pour sommer l'indicateur sur les 52 dernières semaines. Le moteur Postgre doit donc sûrement considérer qu'il ne sert à rien d'utiliser index_source_4 qui est juste sur annee_semaine.
Le explain analyse en production est en cours.
Le explain analyse en recette donne ceci :
"Bitmap Heap Scan on table_cible aa (cost=1513.65..23612788.82 rows=80654 width=60) (actual time=10.061..4533599.672 rows=80317 loops=1)"
" Recheck Cond: ((annee_semaine)::text = '2011S20'::text)"
" -> Bitmap Index Scan on index_cible (cost=0.00..1493.49 rows=80654 width=0) (actual time=9.766..9.766 rows=80435 loops=1)"
" Index Cond: ((annee_semaine)::text = '2011S20'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..292.50 rows=1 width=26) (actual time=56.440..56.440 rows=1 loops=80317)"
" -> Result (cost=0.00..292.04 rows=59 width=26) (actual time=4.691..56.410 rows=44 loops=80317)"
" One-Time Filter: (($1)::text = '2011S20'::text)"
" -> Nested Loop (cost=0.00..292.04 rows=59 width=26) (actual time=4.690..56.392 rows=44 loops=80317)"
" -> Index Scan using index_source on table_source a (cost=0.00..24.68 rows=2 width=22) (actual time=3.139..3.139 rows=1 loops=80317)"
" Index Cond: (((code)::text = ($0)::text) AND ((annee_semaine)::text = '2011S20'::text))"
" -> Index Scan using index_source on table_source b (cost=0.00..133.32 rows=29 width=26) (actual time=1.568..53.914 rows=44 loops=79292)"
" Index Cond: (((b.code)::text = ($0)::text) AND ('2010S21'::text <= (b.annee_semaine)::text) AND ((b.annee_semaine)::text <= (a.annee_semaine)::text))"
"Total runtime: 4538565.077 ms"
Le même explain analyse est en cours en production.
Définition des tables :
-- Table: table_source
CREATE TABLE table_source
(
col_seq integer NOT NULL DEFAULT nextval('sequence'::regclass),
code character varying(13), <--- colonne utilisée dans la jointure
dimension1 integer,
dimension2 character varying(3),
indicateur integer, <--- indicateur sommé dans la requête
indicateur1 real,
indicateur2 real,
indicateur3 real,
indicateur4 integer,
indicateur5 real,
indicateur6 real,
indicateur7 real,
indicateur8 real,
indicateur9 real,
indicateur10 real,
indicateur11 real,
indicateur12 real,
indicateur13 real,
dimension3 character varying(8),
indicateur14 real,
indicateur15 real,
indicateur16 real,
indicateur17 real,
indicateur18 real,
indicateur19 real,
indicateur20 real,
indicateur21 real,
indicateur22 real,
dimension4 character varying(2),
dimension5 character varying(10),
indicateur23 real,
indicateur24 integer,
indicateur25 integer,
indicateur26 real,
indicateur27 real,
indicateur28 real,
indicateur29 real,
indicateur30 real,
indicateur31 real,
indicateur32 real,
dimension6 character varying(4),
debut_valeur date,
fin_valeur date,
date_maj date,
dimension7 integer,
indicateur33 real,
indicateur34 real,
indicateur35 integer,
annee_semaine character varying(7), <--- colonne utilisée dans la jointure
indicateur36 real
);
-- Index: index_source_1
CREATE INDEX "index_source_1"
ON table_source
USING btree
(code);
-- Index: index_source_2
CREATE INDEX "index_source_2"
ON table_source
USING btree
(dimension2);
-- Index: index_source_3
CREATE INDEX "index_source_3"
ON table_source
USING btree
(dimension2, dimension1);
-- Index: index_source_4
CREATE INDEX "index_source_4"
ON table_source
USING btree
(annee_semaine);
-- Index: index_source
CREATE INDEX "index_source"
ON table_source
USING btree
(code, annee_semaine); <--- index source utilisé dans la requête
-- Index: index_source_5
CREATE INDEX "index_source_5"
ON table_source
USING btree
(code, dimension1, dimension2);-- Table: table_cible
CREATE TABLE table_cible
(
annee_semaine character varying(7) NOT NULL,
code character varying(13) NOT NULL,
indicateur1 numeric(15,4),
indicateur2 numeric(15,5),
indicateur3 real,
indicateur4 real,
indicateur5 integer,
indicateur_moyen_mensuel integer, <--- indicateur calculé
indicateur6 real
);
-- Index: index_cible
CREATE INDEX index_cible
ON table_cible
USING btree
(annee_semaine); <--- index cible utilisé dans la requêteMerci d'avance
Dernière modification par Jibc (14/12/2012 10:55:29)
Hors ligne
Explain analyse en production :
"Bitmap Heap Scan on table_cible aa (cost=1705.16..11308019.02 rows=124449 width=60) (actual time=150.471..6904607.438 rows=119066 loops=1)"
" Recheck Cond: ((annee_semaine)::text = '2012S01'::text)"
" -> Bitmap Index Scan on index_cible (cost=0.00..1674.05 rows=124449 width=0) (actual time=146.611..146.611 rows=122400 loops=1)"
" Index Cond: ((annee_semaine)::text = '2012S01'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..90.25 rows=1 width=26) (actual time=57.981..57.982 rows=1 loops=119066)"
" -> Result (cost=0.00..90.07 rows=21 width=26) (actual time=6.759..57.941 rows=39 loops=119066)"
" One-Time Filter: (($1)::text = '2012S01'::text)"
" -> Nested Loop (cost=0.00..90.07 rows=21 width=26) (actual time=6.757..57.891 rows=39 loops=119066)"
" -> Index Scan using index_source on table_source a (cost=0.00..24.63 rows=1 width=22) (actual time=3.993..3.994 rows=1 loops=119066)"
" Index Cond: (((dwh_gtin)::text = ($0)::text) AND ((annee_semaine)::text = '2012S01'::text))"
" -> Index Scan using index_source on table_source b (cost=0.00..65.18 rows=21 width=26) (actual time=3.009..58.705 rows=42 loops=109205)"
" Index Cond: (((b.dwh_gtin)::text = ($0)::text) AND ('2011S02'::text <= (b.annee_semaine)::text) AND ((b.annee_semaine)::text <= (a.annee_semaine)::text))"
"Total runtime: 6908342.499 ms"
Hors ligne
PostgreSQL n'essaye pas de déduire des informations dues à la transitivité des opérateurs, pour les inégalités. Il ne le fait que pour les égalités.
Il ne va pas déduire de
join table_source b on (a.code = b.code and '2011S02' <= b.annee_semaine and b.annee_semaine <= a.annee_semaine)
where a.annee_semaine = '2012S01'
que
b.annee_semaine <= '2012S01'
Je ne suis pas sûr que ça puisse avoir un impact énorme sur la requête, mais pouvez-vous essayer de remplacer a.annee_semaine par '2012S01' dans la clause de jointure, et réessayer (et nous montrer le nouveau plan) ?
Marc.
Hors ligne
Bonjour,
PostgreSQL n'essaye pas de déduire des informations dues à la transitivité des opérateurs, pour les inégalités. Il ne le fait que pour les égalités.
Il ne va pas déduire de
join table_source b on (a.code = b.code and '2011S02' <= b.annee_semaine and b.annee_semaine <= a.annee_semaine)
where a.annee_semaine = '2012S01'que
b.annee_semaine <= '2012S01'
Je ne suis pas sûr que ça puisse avoir un impact énorme sur la requête, mais pouvez-vous essayer de remplacer a.annee_semaine par '2012S01' dans la clause de jointure, et réessayer (et nous montrer le nouveau plan) ?
Non cela ne change rien ... malheureusement !
Voici 2 explain (les explain analyze sont en cours) de la même requête mais avec une condition différente.
Temps de réponse catastrophiques ! 
"Bitmap Heap Scan on table_cible aa (cost=2231.66..7442511.43 rows=143138 width=60)"
" Recheck Cond: ((anneesem)::text = '2012S44'::text)"
" -> Bitmap Index Scan on index_cible (cost=0.00..2195.88 rows=143138 width=0)"
" Index Cond: ((anneesem)::text = '2012S44'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..51.87 rows=1 width=26)"
" -> Result (cost=0.00..51.84 rows=2 width=26)"
" One-Time Filter: (($1)::text = '2012S44'::text)"
" -> Nested Loop (cost=0.00..51.84 rows=2 width=26)"
" -> Index Scan using index_source on table_source a (cost=0.00..24.89 rows=1 width=22)"
" Index Cond: (((dwh_gtin)::text = ($0)::text) AND ((dwh_anneesem)::text = '2012S44'::text))"
" -> Index Scan using index_source on table_source b (cost=0.00..26.93 rows=2 width=26)"
" Index Cond: (((b.dwh_gtin)::text = ($0)::text) AND ('2012S45'::text <= (b.dwh_anneesem)::text) AND ((b.dwh_anneesem)::text <= (a.dwh_anneesem)::text))"Temps de réponse exceptionnels !! 
Mais pourquoi ?! 
"Index Scan using index_cible on table_cible aa (cost=0.00..56.24 rows=1 width=60)"
" Index Cond: ((anneesem)::text = '2012S43'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..51.87 rows=1 width=26)"
" -> Result (cost=0.00..51.84 rows=2 width=26)"
" One-Time Filter: (($1)::text = '2012S43'::text)"
" -> Nested Loop (cost=0.00..51.84 rows=2 width=26)"
" -> Index Scan using index_source on table_source a (cost=0.00..24.89 rows=1 width=22)"
" Index Cond: (((dwh_gtin)::text = ($0)::text) AND ((dwh_anneesem)::text = '2012S43'::text))"
" -> Index Scan using index_source on table_source b (cost=0.00..26.93 rows=2 width=26)"
" Index Cond: (((b.dwh_gtin)::text = ($0)::text) AND ('2012S44'::text <= (b.dwh_anneesem)::text) AND ((b.dwh_anneesem)::text <= (a.dwh_anneesem)::text))"La seule différence est que la deuxième requête a comme condition '2012S43' sur "table_cible". J'obtiens le même plan d'exécution pour toutes les semaines antérieures à '2012S43' jusqu'à '2012S01'.
Le passage à la semaine '2012S44' serait la goutte qui fait déborder le vase imploser PostgreSQL ?!
Gôm
Dernière modification par gom (28/12/2012 18:40:23)
Hors ligne
Ça donne l'impression que les estimations sont totalement fausses avec 2012S44. Les stats sont à jour ? Parce que ça donne l'impression que les stats son correctes pour les valeurs précédentes mais pas pour celle-ci (si par exemple les données sont injectées semaine par semaine, et que les stats ont été calculées avant l'injection de cette semaine là, elle sera hors histogramme, et pas non plus dans les most common values. As-tu essayé de repasser les statistiques ?
Marc.
Hors ligne
Non je n'ai pas essayé.
Faire un "VACUUM ANALYZE table_cible" ?
Hors ligne
J'ai fait ça :
DROP INDEX index_cible;
VACUUM FULL ANALYZE table_cible;
CREATE INDEX index_cible
ON table_cible
USING btree
(anneesem);Malheureusement, j'ai peur que cela ne change rien, parce qu'en fait si le plan d'exécution change c'est simplement par que les semaines dont je parlais n'existent pas dans la table !
anneesem;count(*)
"2012S51";145345
"2012S50";146918
"2012S49";145560
"2012S48";143669
"2012S47";142365
"2012S46";142066
"2012S45";142442
"2012S44";140548
Hors ligne
Désolé pour le temps de réponse…
Juste «analyze».
Marc.
Hors ligne
Désolé moi aussi pour le temps de réponse !
Le problème est résolu grâce à un changement fonctionnel. Techniquement rien n'a pu être fait pour améliorer les choses !
Hors ligne
Pages : 1