optimisation requête [jointure]

bonjour,

J'ai un problème d'optimisation de mes requêtes.
Mes tables font jusqu'à un million d'enregistrements donc cela devient capital.
J'utilise (depuis peu) postgresql+postgis.

voici mon code:

Code:

---

1 2 3 4 5 6 7 8

SELECT table1.col1,table2.col1, table3.col1,AsSVG( table2.the_geom,1,1) as geom FROM table2 JOIN table3 ON table2.col1=table3.col2, table1 WHERE table2.the_geom && '$box'::box3d AND intersects (table2.the_geom,table1.the_geom) AND table1.col1='ma variable'

---

les explications :
- j'effectue une jointure de la table 3 avec la table 2
- je sélectionne suivant 2 clauses géographiques( sur les table 1 et2) + une clause attributaire (sur la table 1)
- je récupère un champ de la table 3

Cette requête fonctionne mais elle est trop lente.
J'imagine que c'est une hérésie d'effectuer la jointure sur l'ensemble de la table et qu'il faudrait réaliser les sélections puis effectuer ensuite la jointure.
(Il ne restera que qq milliers d'enregistrements.)
Mais là, je ne sais pas faire: il faut peut être créer une table temporaire ?

Si quelqu'un a une idée pour organiser cela autrement...

Le problème de votre requête est qu'il manque la condition de jointure entre table1 et une des deux autres tables. Du coup il y a un produit cartésien entre le résultat de la jointure table2-table3 et la table1.

Cette erreur provient peut-être du fait que vous mélanger la syntaxe normalisée des jointures avec le mot-clé JOIN et l'ancienne syntaxe à virgule obsolète depuis 1992.

Voici votre requête récrite avec mise en évidence de ce qui manque et une mise en forme plus lisible :

Code:

---

1 2 3 4 5 6 7 8

SELECT table1.col1, table2.col1, table3.col1, AsSVG( table2.the_geom,1,1) AS geom FROM table2 JOIN table3 ON table2.col1 = table3.col2 JOIN table1 ON -- manque condition de jointure WHERE table2.the_geom && '$box'::box3d AND intersects (table2.the_geom, table1.the_geom) AND table1.col1 = 'ma variable'

---

J'ai besoin de quelques éclaircissements :

Je ne cherche pas à effectuer une jointure avec ma table 1 car cette table me sert juste pour une requête d'intersection (pour l'objet table1.col1= ma variable).Je n'ai d'ailleurs pas d'identifiants communs...

En fait dans l'idéal, j'ai besoins de réaliser mes requêtes d'intersections puis de joindre ma table 3 au résultat ...:?

Citation:

---

Mais là, je ne sais pas faire: il faut peut être créer une table temporaire ?

---

Pour les requêtes lentes, la première chose à faire, c'est un EXPLAIN ANALYZE de la requête, qui permet d'avoir des informations concrètes sur les index et la manière dont la requête est exécutée par le moteur.

Je ne connais les fonctions géométriques de Postgresql mais peut-être que tu peut mettre l'opération d'intersection dans la condition de jointure ?

Code:

---

1 2 3 4 5 6 7

SELECT table1.col1, table2.col1, table3.col1, AsSVG( table2.the_geom,1,1) AS geom FROM table2 JOIN table3 ON table2.col1 = table3.col2 JOIN table1 ON intersects (table2.the_geom, table1.the_geom) WHERE table2.the_geom && '$box'::box3d AND table1.col1 = 'ma variable'

---

Ou alors utiliser une sous-requête faisant l'intersection :

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT t.t1_col1, t.t2_col1, table3.col1, t.geom FROM ( SELECT table1.col1 AS t1_col1, table2.col1 AS t2_col1, AsSVG( table2.the_geom,1,1) AS geom FROM table2, table3 WHERE intersects (table2.the_geom, table1.the_geom) AND table1.col1 = 'ma variable' ) AS t INNER JOIN table3 ON table3.col2 = t.t2_col1

---

Et bien sûr pour optimiser l'exécution de la requête, tu as pensé à bien indexer les colonnes faisant partie des conditions de jointure ?

Une remarque de vocabulaire: ce que tu appelles "requête d'intersection" devrait être appelé une clause. La requête, c'est l'ensemble du SELECT, alors qu'une clause est une expression testée dans le WHERE, ou dans les conditions de jointure quand on utilise JOIN.
En l'occurrence ici la clause de jointure entre table1 et le reste semble être:

Code:

---

intersects (table2.the_geom,table1.the_geom)

---

Je viens de tester les deux méthodes de CinePhil : ca marche dans les deux cas mais c'est toujours aussi long que ma méthode initiale....

Concernant le remarque sémantique d'estofilo c'est noté je ferai attention aux termes employés.
Je viens aussi de tester explain analyse (je découvre) et voici le résultat (qui ne me parle pas beaucoup) :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

Hash Join (cost=37110.09..50354.80 rows=869 width=1186) (actual time=11468.550..15103.385 rows=1339 loops=1)   Hash Cond: ((parcelle.idu)::text = (pnb10_parcelle.idpar)::text)   -> Nested Loop (cost=84.57..7453.90 rows=869 width=1181) (actual time=3.208..110.473 rows=1434 loops=1)   Join Filter: intersects(parcelle.the_geom, communes.the_geom)   -> Seq Scan on communes (cost=0.00..3.46 rows=1 width=92475) (actual time=0.017..0.040 rows=1 loops=1)   Filter: ((insee)::text = '19003'::text)   -> Bitmap Heap Scan on parcelle (cost=84.57..7417.84 rows=2608 width=1175) (actual time=1.700..3.985 rows=2837 loops=1)   Recheck Cond: (parcelle.the_geom && '0103000000010000000500000024628CC2A421224140E54E789999584124628CC2A42122415BD20DA7459D584119AD3B85764222415BD20DA7459D584119AD3B857642224140E54E789999584124628CC2A421224140E54E7899995841'::geometry)   -> Bitmap Index Scan on n_parcelle_dgi_19_the_geom_gist (cost=0.00..83.92 rows=2608 width=0) (actual time=1.656..1.656 rows=2837 loops=1)   Index Cond: (parcelle.the_geom && '0103000000010000000500000024628CC2A421224140E54E789999584124628CC2A42122415BD20DA7459D584119AD3B85764222415BD20DA7459D584119AD3B857642224140E54E789999584124628CC2A421224140E54E7899995841'::geometry)   -> Hash (cost=19480.23..19480.23 rows=955623 width=20) (actual time=11432.562..11432.562 rows=955623 loops=1)   -> Seq Scan on pnb10_parcelle (cost=0.00..19480.23 rows=955623 width=20) (actual time=0.048..346.559 rows=955623 loops=1)

---

N'ayant pas crée d'index autres que sur la colonne géographie il me faut certainement fouiller dans cette direction...

On peut avoir la structure exacte des tables réelles ainsi que les colonnes indexées ?

Je viens de créer vite fait 2 index sur les colonnes identifiants de mes tables et de suite c'est beaucoup plus rapide. Le problème vient donc en grande partie de là.

Il me reste plus qu'à me documenter sur le sujet.

Merci de votre aide...

Citation:

---

Envoyé par apidou

N'ayant pas crée d'index autres que sur la colonne géographie il me faut certainement fouiller dans cette direction...

---

Oui c'est ce qui me semble aussi, la partie géographique a l'air rapide, mais sur le reste il doit y avoir moyen de gagner.
Ne pas oublier de lancer ANALYZE table juste après avoir ajouté un index pour mettre à jour immédiatement les statistiques.