Forums PostgreSQL.fr

damalaan

Membre

Recherche d'optimisation sur calcul de requete

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

rjuju

Administrateur

Re : Recherche d'optimisation sur calcul de requete

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/

damalaan

Membre

Re : Recherche d'optimisation sur calcul de requete

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"

rjuju

Administrateur

Re : Recherche d'optimisation sur calcul de requete

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/

gleu

Administrateur

Re : Recherche d'optimisation sur calcul de requete

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.

damalaan

Membre

Re : Recherche d'optimisation sur calcul de requete

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.

gleu

Administrateur

Re : Recherche d'optimisation sur calcul de requete

Si je puis me permettre, vous divisez quand même la durée d'exécution par deux.

---

Guillaume.

damalaan

Membre

Re : Recherche d'optimisation sur calcul de requete

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 > 100000

Or 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

gleu

Administrateur

Re : Recherche d'optimisation sur calcul de requete

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.

damalaan

Membre

Re : Recherche d'optimisation sur calcul de requete

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?

gleu

Administrateur

Re : Recherche d'optimisation sur calcul de requete

Supprimer et recréer des requêtes ? désolé, mais ça n'a aucun sens.

---

Guillaume.

damalaan

Membre

Re : Recherche d'optimisation sur calcul de requete

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?)

rjuju

Administrateur

Re : Recherche d'optimisation sur calcul de requete

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/

damalaan

Membre

Re : Recherche d'optimisation sur calcul de requete

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 !