Bonjour,

J'ai une table pts contenant 80000 points, avec les informations suivantes :
id char(16) => clé primaire identifiant le point
x int => coordonnée X en Lambert 2 étendu
y int => coordonnée Y en Lambert 2 étendu
the_geom geometry => la géométrie associée, avec le SRID du Lambert 2 étendu (320002120 pour tenir compte de la grille de redressement IGN)
J'ai une table contenant 473 polygones, remplie par import de shapefile avec les informations suivantes :
gid serial => l'identifiant du polygone
code varchar(3) => code du polygone
libelle varchar(255) => un libellé associé au polygone
the_geom geometry => la géométrie associée, avec le SRID du Lambert 2 étendu (320002120 pour tenir compte de la grille de redressement IGN)
Mon but : savoir dans quel polygone tombe chacun de mes 80000 points.
Pour ça, j'ai ajouter des index GIST comme suit :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
CREATE INDEX idx_pts ON pts
USING gist (the_geom)
WITH (FILLFACTOR=90);
 
CREATE INDEX idx_polyg ON polyg
USING gist (the_geom)
WITH (FILLFACTOR=90);
J'ai fait un VACUUM ANALYZE sur chaque table à l'issue de la création des 2 index.

J'essaie un EXPLAIN de la requête suivante :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
EXPLAIN SELECT p.id, s.code
FROM pts p
INNER JOIN polyg s
    ON St_Intersects(p.the_geom, s.the_geom)
ORDER BY 1;
En effet, la requête, lancée directement tourne des heures sans s'arrêter...

J'obtiens la sortie suivante :
Sort (cost=7479.14..7614.81 rows=54268 width=20)
Sort Key: p.id
-> Nested Loop (cost=0.00..3211.56 rows=54268 width=20)
Join Filter: _st_intersects(p.the_geom, s.the_geom)
-> Seq Scan on polyg s (cost=0.00..55.73 rows=473 width=142236)
-> Index Scan using idx_pts on pts p (cost=0.00..6.41 rows=1 width=117)
Index Cond: (p.the_geom && s.the_geom)
Déjà, il y a un truc très bizarre : le nombre de lignes pour le scan de la table pts indique 1 seule ligne ...

J'ai tenté de bouger les 2 paramètres suivants :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
set enable_seqscan=false;
set enable_nestloop=false;
L'explain donne alors les informations suivantes :
Nested Loop (cost=10000000000.00..10000050605.10 rows=54268 width=20)
Join Filter: _st_intersects(p.the_geom, s.the_geom)
-> Index Scan using pts_pk on pts p (cost=0.00..7479.25 rows=80930 width=117)
-> Index Scan using idx_polyg on polyg s (cost=0.00..0.27 rows=1 width=142236)
Index Cond: (p.the_geom && s.the_geom)
Cette fois, c'est polyg qui n'a plus qu'une seule ligne... Mais surtout, le coût de départ du nested loop final est hallucinant ! D'ailleurs, l'optimiseur ne semble pas trop tenir compte du enable_nestloop...

Bref, je n'y comprends vraiment plus rien.
Et comme je n'ai pas trop l'habitude de Postgis et de l'analyse du explain, je me demande :
  1. si ma requête est correcte ?
  2. comment améliorer les performances ?


Merci d'avance pour votre aide,

ced

[EDIT] : petite précision, je suis en PostgreSQL 9.0.4 et Postgis 1.5.3 sous Ubuntu 10.04.