Discussion :

[medsine]

Bonjour,
En lisant cette discussion, je vous soumet ce problème de performance auquel je suis confronté sous Postgres sur une très grosse base. Faut il privilégier JOIN ou WHERE pour une relation reflexive ?
J'ai une table de stations météos (près de 500 000 stations réparties dans l'espace) qui enregistrent une valeur chaque jour. Je veux trouver les plus proches voisins de chaque station, pas en terme de distance mais en terme de valeurs. Je calcule donc l'écart moyen entre chaque station pour chaque jour (sur un an)... en 2 étapes
Mes performances sont très mauvaises, particulièrement lors de la 1ere étape où je fais une relation réflexive sur ma table (plus de 150 millions de lignes) que j'appelle dans une boucle (i):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT stations.date, stations.station_id, station.valeur, s2.station_id AS station_id2, s2.valeur AS valeur2
   FROM stations
   JOIN stations s2 ON stations.date = s2.date
  WHERE station.station_id = i;

date | station_id | valeur | station_id2 | valeur2
--------------------------------------------------
01/01| 1 | 10| 2 | 09
02/01| 1 | 09 | 3 | 09
03/01| 1 | 07 | 4 | 08
etc...

Sous Postgres, pour traiter 1 station, il faut près de 30 min ...
Comment optimiser ce traitement (j'ai mis un index sur station_id) ?
Lors de l'étape 2 j'utilise la table temporaire générée dans 1 pour calculer les stations les plus "proches".

Merci de votre aide précieuse.

[al1_24]

Un index sur date améliorerait très certainement les performances

[medsine]

Oui, j'en ai mis un.
J'ai amélioré pas mal en utilisant une sous requête que j'enregistre dans une 1ere vue.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE OR REPLACE VIEW qry_1 AS 
SELECT stations.date, 59 as station_id, stations.station_id as station_id2, stations.valeur as valeur2
   FROM stations
  WHERE stations.station_id IN (SELECT stations.station_id FROM stations WHERE stations.station_id=59);

Dans une deuxième vue j'ajoute la valeur de la station 1 à la date donnée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT qry_1.*, stations.valeur
FROM stations INNER JOIN qry_1 ON station.date = qry_1.date
WHERE stations.station_id = 59;

Maintenant, je voudrais faire une boucle sur ces deux requêtes et stocker le résultat final dans une table. Mais je ne sais pas comment procéder... si je fais une procédure stockée, comment l'appeller ? Faut il faire un programme en C++ ou VB ??