Forums PostgreSQL.fr

mortimer.pw

Membre

Requête lente sur volume de données réduit

Bonjour,

Je travaille sur une BD 8.4.2 sous Cent-OS 5.2.

J'ai une requête qui est trés lente alors que le volume de données dans ma base est ridicule.
La requête doit me retourner la dernière ligne de travail de la personne '0000123' pour la veille.

Voilà la requête :
    SELECT T.*,A.*,D.libelle
    FROM Travail T LEFT OUTER JOIN Document D ON (CASE WHEN LastAlpha(T.doc)=0 THEN T.doc ELSE SUBSTR(T.doc,1,LENGTH(T.doc)-1) END)=SUBSTR(D.doc,1,LENGTH(D.doc)-1),Vue_Action A
    WHERE T.jour=CURRENT_DATE-1
    AND T.personne='0000123'
    AND T.id_action=A.id_act1||A.id_act2||A.id_act3||A.id_act4
    ORDER BY T.jour DESC,T.heure DESC LIMIT 1;

Je fais une jointure entre travail et action pour récupérer les paramètres liés à une action.
Je fais une jointure entre travail et document pour récupérer le libelle du document

Le volume des tables est :
   travail : 82591 enregistrements
   action : 20325 enregistrements
   document : 34991 enregistrements

J'ai fait un explain mais je ne sais pas l'exploiter :
Limit  (cost=632614.91..632614.92 rows=1 width=367)
   ->  Sort  (cost=632614.91..632620.69 rows=2309 width=367)
         Sort Key: t.heure
         ->  Hash Join  (cost=2102.89..632603.37 rows=2309 width=367)
               Hash Cond: (a.id_act1 = ss1.id)
               ->  Hash Join  (cost=2101.30..632570.04 rows=2309 width=355)
                     Hash Cond: (a.id_act2 = ss2.id)
                     ->  Hash Join  (cost=2093.88..632530.86 rows=2309 width=346)
                           Hash Cond: (a.id_act3 = ss3.id)
                           ->  Nested Loop  (cost=2081.16..632486.40 rows=2309 width=333)
                                 ->  Hash Join  (cost=2081.16..631828.23 rows=2309 width=317)
                                       Hash Cond: ((t.id_action)::text = ((((a.id_act1)::text || (a.id_act2)::text) || (a.id_act3)::text)|| (a.id_act4)::text))
                                       ->  Nested Loop Left Join  (cost=965.91..630170.27 rows=95 width=235)
                                             Join Filter: (CASE WHEN (lastalpha(t.doc) = 0::numeric) THEN (t.doc)::text ELSE substr((t.doc)::text, 1, (length((t.doc)::text) - 1)) END = substr((d.doc)::text, 1, (length((d.doc)::text) - 1)))
                                             ->  Index Scan using travail1 on travail t  (cost=0.01..66.19 rows=62 width=219)
                                                   Index Cond: ((jour = (('now'::text)::date - 1)) AND (personne = '0000781'::bpchar))
                                             ->  Materialize  (cost=965.90..1315.81 rows=34991 width=25)
                                                   ->  Seq Scan on document d  (cost=0.00..930.91 rows=34991 width=25)
                                       ->  Hash  (cost=1054.56..1054.56 rows=4855 width=82)
                                             ->  Seq Scan on action a  (cost=0.00..1054.56 rows=4855 width=82)
                                                   Filter: ((id_type1 = 'G'::bpchar) AND (id_type2 = 1::numeric) AND (to_char((('now'::text
)::date)::timestamp with time zone, 'YYYYMMDD'::text) <= (fin_action)::text))
                                 ->  Index Scan using pk_ss_action4 on ss_action4 ss4  (cost=0.00..0.27 rows=1 width=21)
                                       Index Cond: (ss4.id = a.id_act4)
                           ->  Hash  (cost=7.32..7.32 rows=432 width=17)
                                 ->  Seq Scan on ss_action3 ss3  (cost=0.00..7.32 rows=432 width=17)
                     ->  Hash  (cost=4.41..4.41 rows=241 width=13)
                           ->  Seq Scan on ss_action2 ss2  (cost=0.00..4.41 rows=241 width=13)
               ->  Hash  (cost=1.26..1.26 rows=26 width=16)
                     ->  Seq Scan on ss_action1 ss1 (cost=0.00..1.26 rows=26 width=16)
(29 rows)

J'espère fournir suffisamment d'éléments.

Pouvez-vous m'aider ?

Marc Cousin

Membre

Re : Requête lente sur volume de données réduit

FROM Travail T LEFT OUTER JOIN Document D ON (CASE WHEN LastAlpha(T.doc)=0 THEN T.doc ELSE SUBSTR(T.doc,1,LENGTH(T.doc)-1) END)=SUBSTR(D.doc,1,LENGTH(D.doc)-1),Vue_Action A
N'est pas une jointure au sens relationnel (égalité entre les colonnes des deux tables). Comment voulez vous que PostgreSQL effectuel la jointure sans prendre tous les champs de document, et faire le calcul pour chacun d'entre eux.
La jointure sur action ne vaut pas mieux.

Il n'y a aucune solution pour faire fonctionner correctement cette requête, et même si il y en avait une, cela ne ferait que repousser le problème réel : le schéma de la base n'est pas (du tout) bon.

En première lecture, je vous conseille http://fr.wikipedia.org/wiki/Forme_norm … ionnelles)

Si votre base vérifie les critères jusqu'à la troisième forme normale, c'est la plupart du temps suffisant.

Désolé pour la réponse un peu abrupte, mais je pense qu'il ne faut surtout pas que vous continuiez avec ce schéma de base de données.

---

Marc.

mortimer.pw

Membre

Re : Requête lente sur volume de données réduit

Bonjour Marc,

En fait je travaille à la migration d'un système basé sur une BD Oracle vers PostgreSQL.
En Oracle, cela fonctionne trés bien avec des temps de réponse trés acceptables et sur des volumes beaucoup plus importants surtout sur la table travail.

Pourquoi dites-vous "il n'y a pas jointure au sens relationnel" ?
je cherche l'égalité entre le champ T.doc de la table travail et le champ D.doc de la table document.
Idem pour le champ T.id_action qui doit avoir une correspondance dans la table Action en concaténant les champs A.id_act1||A.id_act2||A.id_act3||A.id_act4

Marc Cousin

Membre

Re : Requête lente sur volume de données réduit

Ce n'est pas ce que vous faites :
ON (CASE WHEN LastAlpha(T.doc)=0 THEN T.doc ELSE SUBSTR(T.doc,1,LENGTH(T.doc)-1) END)=SUBSTR(D.doc,1,LENGTH(D.doc)-1)
Est très différent de
ON (doc), qui est vraiment une jointure (on raccorde la foreign key d'une table avec la primary key d'une autre).

Pour la jointure entre T et A, c'est la même chose : la clé de la table T est découpée en 4 clés dans la table A, que vous concaténez. À la rigueur, celle là pourrait fonctionner quand même en créant un index fonctionnel sur la concaténation des 4 colonnes de A.

Peut-être qu'Oracle arrive à faire fonctionner cette requête, mais cela n'en fait pas une requête écrite correctement : une jointure raccorde un enregistrement d'une table à un enregistrement d'une autre table, par le biais de clé (et pas de CASE ou de concaténation de clé, qui ne peuvent pas être optimisés correctement).

Par ailleurs, il semblerait qu'un certain nombre d'éléments de la requête sont des vues, dont vous n'avez pas fourni la définition (le plan ne correspond pas du tout à la jointure de 3 tables)

Dernier point, si vous tenez vraiment à ce qu'on l'optimise en l'état, il faudrait le résultat d'un explain analyze, pas d'un explain simple.

---

Marc.

mortimer.pw

Membre

Re : Requête lente sur volume de données réduit

La vue utilisée est la suivante :
          CREATE VIEW Vue_Action AS
    SELECT a.id_act1,ss1.libelle AS lib1,a.id_act2,ss2.libelle AS lib2,a.id_act3,ss3.libelle AS lib3,a.id_act4,ss4.libelle AS lib4
    FROM action a,ss_action1 ss1,ss_action2 ss2,ss_action3 ss3,ss_action4 ss4
    WHERE a.id_type1='G'
    AND a.id_type2=1
    AND to_char('now'::text::date::timestamp WITH time zone,'YYYYMMDD'::text)<=a.fin_action::text
    AND a.id_act1=ss1.id
    AND a.id_act2=ss2.id
    AND a.id_act3=ss3.id
    AND a.id_act4=ss4.id;

La clé primaire sur A est : ALTER TABLE action ADD CONSTRAINT pk_action PRIMARY KEY(id_act1,id_act2,id_act3,id_act4,fin_action);

Il ya a également un index sur travail : CREATE INDEX travail1 ON travail(jour,personne);

Et voici le explain analyze :
Limit  (cost=643219.80..643219.81 rows=1 width=367) (actual time=30633.571..30633.572 rows=1 loops=1)
   ->  Sort  (cost=643219.80..643878.07 rows=263307 width=367) (actual time=30633.569..30633.569 rows=1 loops=1)
         Sort Key: t.heure
         Sort Method:  top-N heapsort  Memory: 25kB
         ->  Merge Join  (cost=632566.01..641903.27 rows=263307 width=367) (actual time=30633.309..30633.462 rows=43 loops=1)
               Merge Cond: ((((((a.id_act1)::text || (a.id_act2)::text) || (a.id_act3)::text) || (a.id_act4)::text)) = ((t.id_action)::text))
               ->  Sort  (cost=1668.72..1680.86 rows=4855 width=132) (actual time=364.533..364.702 rows=726 loops=1)
                     Sort Key: (((((a.id_act1)::text || (a.id_act2)::text) || (a.id_act3)::text) || (a.id_act4)::text))
                     Sort Method:  quicksort  Memory: 4222kB
                     ->  Hash Join  (cost=49.88..1371.46 rows=4855 width=132) (actual time=1.566..234.961 rows=14447 loops=1)
                           Hash Cond: (a.id_act1 = ss1.id)
                           ->  Hash Join  (cost=48.29..1303.12 rows=4855 width=120) (actual time=1.500..194.138 rows=14447 loops=1)
                                 Hash Cond: (a.id_act3 = ss3.id)
                                 ->  Hash Join  (cost=35.57..1223.65 rows=4855 width=107) (actual time=1.115..177.392 rows=14447 loops=1)
                                       Hash Cond: (a.id_act4 = ss4.id)
                                       ->  Hash Join  (cost=7.42..1128.74 rows=4855 width=91) (actual time=0.299..159.552 rows=14447 loops=1)
                                             Hash Cond: (a.id_act2 = ss2.id)
                                             ->  Seq Scan on action a  (cost=0.00..1054.56 rows=4855 width=82) (actual time=0.065..132.866 rows=14447 loops=1)
                                                   Filter: ((id_type1 = 'G'::bpchar) AND (id_type2 = 1::numeric) AND (to_char((('now'::text)::date)::timestamp with time zone, 'YYYYMMDD'::text) <= (fin_action)::text))
                                             ->  Hash  (cost=4.41..4.41 rows=241 width=13) (actual time=0.223..0.223 rows=241 loops=1)
                                                   ->  Seq Scan on ss_action2 ss2  (cost=0.00..4.41 rows=241 width=13) (actual time=0.007..0.091 rows=241 loops=1)
                                       ->  Hash  (cost=16.40..16.40 rows=940 width=21) (actual time=0.803..0.803 rows=940 loops=1)
                                             ->  Seq Scan on ss_action4 ss4  (cost=0.00..16.40 rows=940 width=21) (actual time=0.009..0.347 rows=940 loops=1)
                                 ->  Hash  (cost=7.32..7.32 rows=432 width=17) (actual time=0.374..0.374 rows=432 loops=1)
                                       ->  Seq Scan on ss_action3 ss3  (cost=0.00..7.32 rows=432 width=17) (actual time=0.008..0.172 rows=432 loops=1)
                           ->  Hash  (cost=1.26..1.26 rows=26 width=16) (actual time=0.046..0.046 rows=26 loops=1)
                                 ->  Seq Scan on ss_action1 ss1 (cost=0.00..1.26 rows=26 width=16) (actual time=0.014..0.031 rows=26 loops=1)
               ->  Sort  (cost=630897.29..630924.41 rows=10847 width=235) (actual time=30268.120..30268.133 rows=43 loops=1)
                     Sort Key: ((t.id_action)::text)
                     Sort Method:  quicksort  Memory: 31kB
                     ->  Nested Loop Left Join  (cost=965.91..630170.27 rows=10847 width=235) (actual time=768.841..30267.651 rows=43 loops=1)
                           Join Filter: (CASE WHEN (lastalpha(t.doc) = 0::numeric) THEN (t.doc)::text ELSE substr((t.doc)::text, 1, (length((t.doc)::text) - 1)) END = substr((d.doc)::text, 1, (length((d.doc)::text) - 1)))
                           ->  Index Scan using travail1 on travail t  (cost=0.01..66.19 rows=62 width=219) (actual time=0.068..0.420 rows=43 loops=1)
                                 Index Cond: ((jour = (('now'::text)::date - 1)) AND (personne = '0000123'::bpchar))
                           ->  Materialize  (cost=965.90..1315.81 rows=34991 width=25) (actual time=0.001..13.739 rows=34991 loops=43)
                                 ->  Seq Scan on document d  (cost=0.00..930.91 rows=34991 width=25) (actual time=0.010..31.729 rows=34991 loops=1)
Total runtime: 30636.099 ms
(37 rows)

Merci pour votre aide Marc.

Marc Cousin

Membre

Re : Requête lente sur volume de données réduit

Tout le coût de la requête est là :
                     ->  Nested Loop Left Join  (cost=965.91..630170.27 rows=10847 width=235) (actual time=768.841..30267.651 rows=43 loops=1)
                           Join Filter: (CASE WHEN (lastalpha(t.doc) = 0::numeric) THEN (t.doc)::text ELSE substr((t.doc)::text, 1, (length((t.doc)::text) - 1)) END = substr((d.doc)::text, 1, (length((d.doc)::text) - 1)))
                           ->  Index Scan using travail1 on travail t  (cost=0.01..66.19 rows=62 width=219) (actual time=0.068..0.420 rows=43 loops=1)
                                 Index Cond: ((jour = (('now'::text)::date - 1)) AND (personne = '0000123'::bpchar))
                           ->  Materialize  (cost=965.90..1315.81 rows=34991 width=25) (actual time=0.001..13.739 rows=34991 loops=43)

On fait 43 passes complètes sur la table documents (une par travail), parce que le moteur ne sait pas interpréter

Travail T LEFT OUTER JOIN Document D ON (CASE WHEN LastAlpha(T.doc)=0 THEN T.doc ELSE SUBSTR(T.doc,1,LENGTH(T.doc)-1) END)=SUBSTR(D.doc,1,LENGTH(D.doc)-1)

Je ne vois pas par quel miracle Oracle se sort de celle là, mais le problème est que ça n'est vraiment pas une jointure.

Pouvez vous m'expliquer ce que vous voulez vraiment faire (avec des mots), que nous voyons comment écrire cela en relationnel si c'est possible (j'ai une idée de ce que vous voulez faire, mais j'ai besoin du détail). Donnez moi des exemples si vous pouvez de comment les tables sont raccordées (avec des exemples de champ doc).

---

Marc.

mortimer.pw

Membre

Re : Requête lente sur volume de données réduit

Dans la table document, j'ai la liste des documents sur 7 chiffres + 1 lettre de contrôle

Exemple :
    1111111A
    4444444K
    2222222R
    6666666C

Alors que dans la table Travail, dans le champ doc, je peux recevoir soit :
    - 7 chiffres + 1 lettre de contrôle
    - 7 chiffres
    - X chiffres

Exemple :
    1111111A
    4444444K
    2222222
    6666666
    6487
    4474

Dans le CASE WHEN, je fais donc :

     SI  le dernier caractère de T.doc n'est pas une lettre  ALORS  je cherche T.doc = 7 premiers caractères de D.doc

     SINON  je cherche 7 premiers caractères de T.doc = 7 premiers caractères de D.doc

Je fais un  LEFT OUTER JOIN  pour récupérer les lignes de Travail même si le document n'existe pas dans Document.

J'espère être clair.

Marc Cousin

Membre

Re : Requête lente sur volume de données réduit

Pourquoi ne pas stocker directement l'identifiant du document dans chaque table ? à quoi sert ce caractère de contrôle ?

---

Marc.

mortimer.pw

Membre

Re : Requête lente sur volume de données réduit

La table Document est alimentée grâce à une liste de documents à produire fournie par le client (la lettre de contrôle est ajoutée pendant cette alimentation).

La table Travail est alimentée par la production qui ne connait pas forcément le caractère de contrôle et qui parfois traite des documents qui ne sont pas dans la liste.

Marc Cousin

Membre

Re : Requête lente sur volume de données réduit

L'idéal serait d'avoir le caractère de contrôle dans un champ distinct : ce n'est pas l'identifiant du document. Le champ contient à la fois l'identifiant du document et ce caractère de contrôle, et n'est donc pas atomique (la base n'est donc pas en première forme normale).

Si c'est insurmontable de corriger le problème, calculez au moins un autre champ dans la table document sans le caractère de contrôle, qui sera lui le vrai identifiant du document.

Une fois que vous aurez ce champ, vous pourrez écrire
Travail T LEFT OUTER JOIN Document D ON (T.Doc=D.Realdoc)

Autre chose au passage (je viens de le voir) :
CASE WHEN LastAlpha(T.doc)=0 THEN T.doc ELSE SUBSTR(T.doc,1,LENGTH(T.doc)-1) END)=SUBSTR(D.doc,1,LENGTH(D.doc)-1
ne devrait il pas plutôt être
CASE WHEN LastAlpha(T.doc)=0 THEN T.doc=D.doc ELSE SUBSTR(T.doc,1,LENGTH(T.doc)-1) END)=SUBSTR(D.doc,1,LENGTH(D.doc)-1 ?

---

Marc.

mortimer.pw

Membre

Re : Requête lente sur volume de données réduit

Bonjour Marc,

Le fait d'avoir cette nouvelle colonne dans la table Document ne réglerai pas le problème des Documents qui ne sont pas dans la liste.

Concernant le CASE WHEN, je teste si le dernier caractère est alphanumérique.
     Si non Alors je prends la valeur entière de T.Doc
     Si oui Alors je prends la valeur de T.doc - le dernier caractère
Ensuite je compare avec la valeur de D.doc - le dernier caractère

Marc Cousin

Membre

Re : Requête lente sur volume de données réduit

Vous n'avez pas l'air de vouloir comprendre :
Si vous n'arrivez pas à vous ramener à une requête écrite correctement (LEFT OUTER JOIN Document D ON (T.Doc=D.Realdoc)), les performances resteront mauvaises.
Ce n'est pas le fait que ça soit un LEFT JOIN qui pose problème (cas des documents qui ne sont pas dans la liste). C'est que ce case n'est pas une condition de jointure, et oblige postgres à faire quelque chose qui s'approche d'un cross join en filtrant à posteriori les enregistrements.

---

Marc.

Marc Cousin

Membre

Re : Requête lente sur volume de données réduit

Au passage, votre algorithme ne pourrait il déjà pas être un peu simplifié en testant (toujours) les 7 premiers caractères de T avec les 7 premiers caractères de D ?

---

Marc.

mortimer.pw

Membre

Re : Requête lente sur volume de données réduit

Bonjour Marc,
Ce n'est pas que je ne veux pas comprendre mais je dois migrer une base Oracle en production.
Les changements de structure ne peuvent pas se faire facilement.
Je vais travailler dans ce sens.
Merci pour votre analyse.

Marc Cousin

Membre

Re : Requête lente sur volume de données réduit

Ok. Désolé pour la formulation un peu abrupte. En tout cas, cette modification là est justifiable, étant donné qu'il s'agit d'une erreur de conception flagrante au départ.

Commencez par expliquer que le caractère de contrôle ne fait pas partie de la clé (il n'identifie pas l'enregistrement), et doit donc être stocké dans une colonne séparée. Celà devrait bien simplifier les choses pour la suite : vous aurez normalement juste l'identifiant du document dans les deux tables.

Sinon pour revenir à l'autre piste possible : est-ce que comparer les 7 premiers caractères des deux tables de façon inconditionnelle serait une solution ? Si oui, on peut résoudre le problème assez simplement.

---

Marc.