[PostGreSQL][Optimisation] Requête des dernières saisies

Version imprimable

2 pièce(s) jointe(s)

Bonjour,

Je pêche sur l'optimisation d'une de mes requêtes. Celle-ci s'execute en 35secondes. C'es beaucoup trop. Je ne parviens pas à l'améliorer.

Vous trouverez une capture d'écran du MPD en pièce jointe.

Informations sur les tables concernées :

Citation:

te_circulationcondition_con : Stocke les conditions de circulation, 4 valeurs existent dans cette table (nickel, neige, verglas, impraticable).
te_troncon_tro : Stocke les différents tronçons de route. 68 valeurs existent dans cette table.
te_direction_dir : ne pas en tenir compte, cette table va être supprimée et la jointure aussi.
ts_utilisateur_uti : Stocke les différents utilisateurs du système. 1 valeur existe pour le moment dans cette table. En production, il devrait y en avoir 8.
tj_etat_eta : Stocke l'état de la route à l'instant t. On note donc la clef du troncon(colonne tro_id), la clef de l'utilisateur (colonne uti_id)qui fait la saisie, l'horodatage de la saisie (colonne eta_ins), la condition de conduite relevée (colonne con_id). Chaque année il y a (troncon * jour) saisies. Là je fais de petits tests avec 9000 valeurs de tests et je suis déjà à 35 secondes !

Je veux créer une vue qui ne contient que la dernière condition de conduite relevée pour chaque tronçon. Donc pour le moment, je crée la vue. Mais ce qui m'inquiète est que la requête met 35 secondes à s'exécuter.

Voici la requête que j'ai construite :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 select eta.eta_id, eta.eta_ins,eta.tro_id,eta.dir_id,eta.con_id, tro.tro_lib,tro.tro_ord,tro.tro_zone_sensible,tro.tro_moins,tro.tro_plus, con.con_ci,con.con_couleur from tj_etat_eta eta inner join tj_etat_eta eta2 on eta.tro_id = eta2.tro_id inner join te_troncon_tro tro on tro.tro_id = eta.tro_id inner join te_conditioncirculation_con con on eta.con_id = con.con_id group by eta.eta_id, eta.eta_ins,eta.tro_id,eta.dir_id,eta.con_id, tro.tro_lib,tro.tro_ord,tro.tro_zone_sensible,tro.tro_moins,tro.tro_plus, con.con_ci,con.con_couleur having max(eta2.eta_ins) = eta.eta_ins order by eta.tro_id, eta.eta_id
Voici ce que me retourne l'explain plan :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 GroupAggregate (cost=463675.41..520191.21 rows=1130316 width=195) Filter: (max((eta_ins)::timestamp without time zone) = (eta_ins)::timestamp without time zone) -> Sort (cost=463675.41..466501.20 rows=1130316 width=195) Sort Key: eta.tro_id, eta.eta_id, eta.eta_ins, eta.dir_id, eta.con_id, tro.tro_lib, tro.tro_ord, tro.tro_zone_sensible, tro.tro_moins, tro.tro_plus, con.con_ci, con.con_couleur -> Hash Join (cost=314.99..13916.28 rows=1130316 width=195) Hash Cond: ((eta.tro_id)::integer = (eta2.tro_id)::integer) -> Hash Join (cost=45.65..445.88 rows=8726 width=191) Hash Cond: ((eta.tro_id)::integer = tro.tro_id) -> Hash Join (cost=23.50..303.74 rows=8726 width=88) Hash Cond: ((eta.con_id)::integer = con.con_id) -> Seq Scan on tj_etat_eta eta (cost=0.00..160.26 rows=8726 width=24) -> Hash (cost=16.00..16.00 rows=600 width=68) -> Seq Scan on te_conditioncirculation_con con (cost=0.00..16.00 rows=600 width=68) -> Hash (cost=15.40..15.40 rows=540 width=103) -> Seq Scan on te_troncon_tro tro (cost=0.00..15.40 rows=540 width=103) -> Hash (cost=160.26..160.26 rows=8726 width=12) -> Seq Scan on tj_etat_eta eta2 (cost=0.00..160.26 rows=8726 width=12)
Petite précision qui a son importance : Le moteur de base de données est PostGreSQL. Par conséquent il existe un index automatiquement créé pour chaque colonne d'une clef étrangère. L'index i1_eta de la table tj_etat_eta porte sur la colonne eta_ins

Seconde précision : Le MPD n'est pas figé.

Comment améliorer le temps d'exécution de ma requête ?
Sachant que la requête va être transformée en vue, ce temps de calcul est-il si problématique ?

L'utilisation d'une sous-requête corrélée sera sans doute plus efficace :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT  eta.eta_id, eta.eta_ins,eta.tro_id,eta.dir_id,eta.con_id,
        tro.tro_lib,tro.tro_ord,tro.tro_zone_sensible,tro.tro_moins,tro.tro_plus,
        con.con_ci,con.con_couleur
FROM    tj_etat_eta AS eta
    INNER JOIN 
        te_troncon_tro  AS tro              
        ON tro.tro_id = eta.tro_id
    INNER JOIN 
        te_conditioncirculation_con AS con 
        ON eta.con_id = con.con_id
WHERE   EXISTS
        (   SELECT  1   
            FROM    tj_etat_eta AS eta2               
            WHERE   eta.tro_id = eta2.tro_id
            HAVING  MAX(eta2.eta_ins) = eta.eta_ins
        )
ORDER BY eta.tro_id, eta.eta_id
;

29/05/2007, 17h50
Alexandre T
Merci,

Je viens de charger ma table avec 100.000 enregistrements pour que les tests puissent être comparable à ce que j'aurais en "production".

Ma première requête s'execute en plus de 20 minutes (lassez, j'ai stoppé le processus à 1204 secondes )

La requête corelée s'exécute en 5,3 secondes. C'est beaucoup mieux, merci ! Je voulais encore l'améliorer. Alors j'ai poursuivis, et je suis arrivé à une autre question.

Ce qui m'étonne est que la requête suivante s'exécute en 125ms !
Code:

1 2 3 select tro_id,max(eta_ins) from tj_etat_eta group by tro_id
Il existe un index sur tro_id (car pgsql rajoute un index pour chaque clef étrangère), il existe un index i1_eta sur eta_ins.

J'ai rajouté un index i2_eta sur les colonnes tro_id,eta_ins. Le résultat tombe à 1,5 secondes. Je suis surpris car la colonne tro_id ne possède que 68 valeurs distinctes. Aucun doute, cela fonctionne mieux, en regardant le premier plan fait appel à i1_eta, le second à i2_eta.

J'ai supprimé cet index i2_eta.

J'ai rajouté un index i3_eta sur les mêmes colonnes eta_ins,tro_id, mais en inversé. Et là, surprise 1,5 secondes également.

J'ai remis l'index i2_eta.

J'ai relancé la requête. L'optimiseur choisit l'index i2_eta (tro_id puis eta_ins).
Il me semblait qu'il était préférable que la première colonne d'un index soit la plus discriminante possible ! Me trompé-je ? Si je ne me trompe pas, pourquoi pgsql choisit l'index i2_eta ?

PS : Ok on pourrait croire que je pinaille car sur une trentaine d'exécution, les requêtes ont le même temps d'exécution. En réalité, je cherche surtout à comprendre ce point.
29/05/2007, 18h01
Alexandre T

3 pièce(s) jointe(s)

Au fait, histoire de montrer le plan d'exécution de la requête corrélée de e al1_24

Je vous joins les trois captures d'écran du plan d'exécution.

(Capture de gauche) Ce plan (utilisant i1_eta) a été élaboré par pgsql quand il n'y avait que l'index i1_eta (eta_ins)

(Capture au centre) Ce plan (utilisant i1_eta) a été élaboré par pgsql quand il y avait les index i1_eta (eta_ins) et i3_eta(eta_ins,tro_id)

(Capture de droite) Ce plan a été élaboré par pgsql quand il y avait les index i1_eta (eta_ins) , i2_eta(tro_id,eta_ins). Que i3_eta(eta_ins,tro_id) soit présent ou non, cela ne change rien au plan.

Je rappelle ma question :) Pourquoi PGSQL choisit-il i2_eta (alors que je pensais que la première colonne d'un index devait-être celle qui était la plus discriminante, bref celle qui a le plus de valeurs distinctes)
29/05/2007, 18h01
al1_24

Il n'y a pas que l'existence des index qui compte pour le choix de l'index par l'optimiseur mais aussi, et surtout, les statistiques de répartition des valeurs dans ces index... qui doivent être à jour !
29/05/2007, 18h24
Alexandre T

Citation:

Envoyé par al1_24

Il n'y a pas que l'existence des index qui compte pour le choix de l'index par l'optimiseur mais aussi, et surtout, les statistiques de répartition des valeurs dans ces index... qui doivent être à jour !

C'est justement ce qui me turlupine !

Nous avons les index i1_eta, i2_eta et i3_eta.
Vu la répartition des valeurs, j'aurai pensé qu'il aurait pris i3_eta.

Mais bon de toute façon selon moi, sur le point suivant rien ne change :

Moralité : Le mieux est de charger les bases de données de tests avec autant de données que la base de production. Dans les plans de tests, il faut prévoir tous les cas et tester "chaque requête possible" puis filtrer les trop longues requêtes pour les optimiser une à une...

Merci de ton aide. Je ne sais vraiment pas pourquoi je ne suis pas parti direct sur la requête corrélé :)