Discussion :

[apidou]

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 :

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

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

[CinePhil]

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 :

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

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'

[apidou]

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

[estofilo]

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.

[CinePhil]

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 :

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

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 :

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

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 ?

[estofilo]

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 :

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

intersects (table2.the_geom,table1.the_geom)

[apidou]

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 :

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

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

[CinePhil]

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

[apidou]

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

[estofilo]

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.