Vous n'êtes pas identifié(e).
Bonjour
j'ai la requete suivante basée sur 2 table (en 1-n) dont la structure est également ci-dessous
la requête :
CREATE OR REPLACE VIEW view_ecart AS
SELECT tbl_tournee_trn.trn_esp, tbl_tournee_trn.trn_date_prel, tbl_tournee_trn.trn_tournee, tbl_tournee_trn.trn_chauffeur, tbl_tournee_trn.trn_usine, tbl_valeur_mg.mg_matricule, tbl_valeur_mg.mg_val,
CASE lag(tbl_valeur_mg.mg_val, 1) OVER (PARTITION BY tbl_valeur_mg.mg_matricule ORDER BY tbl_tournee_trn.trn_esp, tbl_tournee_trn.trn_date_prel)
WHEN 0 THEN NULL::numeric
ELSE
CASE tbl_valeur_mg.mg_val
WHEN 0 THEN NULL::numeric
ELSE tbl_valeur_mg.mg_val - lag(tbl_valeur_mg.mg_val, 1) OVER (PARTITION BY tbl_valeur_mg.mg_matricule ORDER BY tbl_tournee_trn.trn_esp, tbl_tournee_trn.trn_date_prel)
END
END AS ecart
FROM tbl_valeur_mg
JOIN tbl_tournee_trn USING (trn_id)
WHERE tbl_tournee_trn.trn_date_prel > 100000;
ALTER TABLE view_ecart OWNER TO postgres;table du coté 1:
CREATE TABLE tbl_tournee_trn
(
trn_date_prel integer NOT NULL,
trn_esp integer NOT NULL,
trn_tournee integer NOT NULL,
trn_usine integer NOT NULL,
trn_id bigserial NOT NULL,
trn_chauffeur integer,
CONSTRAINT "clé_primaire" PRIMARY KEY (trn_id),
CONSTRAINT "clé_2" UNIQUE (trn_date_prel, trn_esp, trn_tournee, trn_usine)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tbl_tournee_trn OWNER TO postgres;table du coté n
CREATE TABLE tbl_valeur_mg
(
mg_id bigserial NOT NULL,
trn_id bigserial NOT NULL,
mg_matricule integer NOT NULL,
mg_val numeric(4,1) NOT NULL,
CONSTRAINT pk PRIMARY KEY (mg_id),
CONSTRAINT fk FOREIGN KEY (trn_id)
REFERENCES tbl_tournee_trn (trn_id) MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE tbl_valeur_mg OWNER TO postgres;
-- Index: index_mg
-- DROP INDEX index_mg;
CREATE INDEX index_mg
ON tbl_valeur_mg
USING btree
(trn_id, mg_matricule);Il y a un index de positionner, mais l'est-il bien?
la requete met une 30aine de seconde pour renvoyer 500000 lignes
je fais souvent appel à cette requête car je travaille beaucoup sur le calcul qui en est issu et je recherche un moyen d'augmenter un peu la rapidité
merci d'avance
Hors ligne
Bonjour,
un explain analyze de la requête aiderait à vous aider, mais à première vue, un index sur tbl_tournee_trn.trn_date_prel pourrait être utile selon la taille de votre table.
Julien.
https://rjuju.github.io/
Hors ligne
taille table tbl_tournee_trn : Lignes estimées 73145
taille table tbl_valeur_mg : Lignes estimées 650283
voilà pour le explain
"WindowAgg (cost=130671.82..151806.02 rows=650283 width=32) (actual time=2739.974..4027.162 rows=496831 loops=1)"
" -> Sort (cost=130671.82..132297.53 rows=650283 width=32) (actual time=2739.920..3132.560 rows=496831 loops=1)"
" Sort Key: tbl_valeur_mg.mg_matricule, tbl_tournee_trn.trn_esp, tbl_tournee_trn.trn_date_prel"
" Sort Method: external merge Disk: 19624kB"
" -> Hash Join (cost=2787.50..36762.70 rows=650283 width=32) (actual time=181.034..1570.746 rows=496831 loops=1)"
" Hash Cond: (tbl_valeur_mg.trn_id = tbl_tournee_trn.trn_id)"
" -> Seq Scan on tbl_valeur_mg (cost=0.00..11284.83 rows=650283 width=20) (actual time=0.052..238.358 rows=650283 loops=1)"
" -> Hash (cost=1551.74..1551.74 rows=63901 width=28) (actual time=58.872..58.872 rows=62807 loops=1)"
" Buckets: 2048 Batches: 4 Memory Usage: 794kB"
" -> Seq Scan on tbl_tournee_trn (cost=0.00..1551.74 rows=63901 width=28) (actual time=2.371..28.166 rows=62807 loops=1)"
" Filter: (trn_date_prel > 100000)"
"Total runtime: 4120.821 ms"Hors ligne
Si vous pouvez vous le permettre, vous pourriez dans un premier temps allouer un peu plus de ram à votre connexion pour éviter un tri sur disque, ce qui devrait faire gagner un peu de temps.
Essayez:
SET work_mem TO '40MB';
EXPLAIN ANALYZE SELECT ...Julien.
https://rjuju.github.io/
Hors ligne
Comme le dit Julien, la durée de la requête dépend principalement de la durée du tri (opération Sort). Elle prend 1,5 secondes à elle seule. La raison vient certainement du fait qu'une partie du tri doit se faire sur disque (20 Mo écrit). Du coup, augmenter work_mem permettrait de gagner sur ce temps de tri.
Vous parliez d'un index au début. Il est clair que la requête ne passe pas par un index. Elle fait un parcours séquentiel de la table tbl_tournee_trn et filtre en même temps sur la colonne trn_date_prel. Coller un index sur cette colonne pourrait faire qu'il utilise un index mais de toute façon, le parcours séquentiel est très rapide (28 ms), donc ça ne permettra pas de gagner grand chose sur une requête qui dure 4 secondes.
Les deux opérations qui prennent du temps sont la jointure (1,2 seconde) et le tri (1,5 secondes). Les statistiques sont plutôt précises, il y a donc peu de chances de gagner autrement qu'en augmentant work_mem. Par contre, je pense qu'il faudra aller un peu au-dessus de 40 Mo.
Guillaume.
Hors ligne
Effectivement en plaçant un index sur la colonne trn_date_prel, et en augmentant la mémoire (50MB), je gagne pas grand chose : 1 à 2 s.
Merci pour ces infos.
Hors ligne
Si je puis me permettre, vous divisez quand même la durée d'exécution par deux.
Guillaume.
Hors ligne
Oui mais au global ça dure toujours une trentaine de sec!
Je pense à autre chose : cette fameuse vue me sert à 4 autres en cascade(la vue 1 sert à la vue2, la vue 2 sert à la vue 3 etc), et dès la première je renvoie 500000lignes avec le
WHERE tbl_tournee_trn.trn_date_prel > 100000Or je n'ai que très rarement besoin de travailler sur tout ça. Il me faut juste des stats sur 3 mois, donc par exemple pour Aout 2012, il faudrait que je récupère juste les données depuis juin 2012
il me faudrait qqc du genre
WHERE to_timestamp(tbl_tournee_trn.trn_date_prel/100, 'yymm') between Moncritère and Moncritère-'3 Month'::interval)Il faudrait que je puisse paramétrer ce critère à chaque fois,.............ce que je ne sais pas faire
et pour avoir tester 'à la main', j'en suis à 2 à 3s d'exécution ce qui nickel pour l'utilisateur
Hors ligne
Je vois 4 secondes sur votre dernier EXPLAIN.
Concernant le coup des vues, il est clair que passer par des groupes de vues qui récupèrent plus d'infos que nécessaire peut être un gros soucis. Il peut être intéressant de ne pas passer par les vues pour construire une requête plus simple à exécuter.
Guillaume.
Hors ligne
Je crois n'avoir pas bien compris le Explain : effectivement je suis à 2.5s d'exécution après les modifs d'index et de mémoire.
mais le select en lui même n'a pas tellement changé : pg Admin renvoie
Je voudrais donc poursuivre sur mon idée d'hier de n'utiliser que les éléments nécessaires et non toute une table.
Faut-il que je crée une fonction qui fasse toutes requêtes d'un seul coup en les supprimant et recréant à chaque fois?
Hors ligne
Supprimer et recréer des requêtes ? désolé, mais ça n'a aucun sens.
Guillaume.
Hors ligne
je suis bien d'accord que supprimer des requêtes puis les recréer n'est pas très ....pro
Le principe que j'ai actuellement :
R1 : calcul des écarts entre les valeurs d'une même colonne en utilisant une fonction WINDOW
R2 : calcul à partir de l'écart calculé en R1 un écart type, avec un regroupement sur 4 colonnes (espèce, usine, tournée, mois)
R3 : calcul à partir de l'écart-type calculé en R2, la moyenne des écart types avec un regroupement sur 2 colonnes (espèce, mois)
R4 : comparaison des écart types obtenus en R2 avec la moyenne R3 pour définir des alertes
R5 : comparaison des alertes identifiées en R4 sur 2 mois consécutifs, afin d'identifier la récurrence éventuelle d'apparition des alertes
J'ai commencé à essayer de rassembler tous mes requêtes en 1 seule mais ça devient rapidement compliqué! J'en suis resté à R2!
J'imagine donc qu'il va falloir que je poursuive mon idée en PL/pgSQL. Est-il possible de créer un genre de requête temporaire en PL/pgSQL? (et est-ce une bonne idée?)
Hors ligne
S'il ne s'agit que d'aider la lisibilité du code et d'éviter des imbrications énormes, vous pouvez vous servir de CTE. ex de syntaxe:
WITH r1 AS (SELECT --calcul des écart avec fonction window
WHERE to_timestamp ...),
r2 AS (SELECT ... FROM r1),
r3 AS (SELECT ... FROM r2)
SELECT * FROM r3 WHERE ....Julien.
https://rjuju.github.io/
Hors ligne
NICKEL!!!
Avec le même volume de données (soit plus de 530 000 lignes), je gagne 20s d'exécution (je passe de 36s à 17s)
Tout en sachant que je ne travaille la plupart du temps que sur 50 000 à 70 000 lignes, la requête finale prend alors moins d'1s. 
Avec tous mes remerciements !
Hors ligne