[Postgis] Performances d'une requête d'intersection entre points et polygônes
Bonjour,
J'ai une table pts contenant 80000 points, avec les informations suivantes :
Citation:
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 :
Citation:
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:
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:
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 :
Citation:
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 8O...
J'ai tenté de bouger les 2 paramètres suivants :
Code:
1
2
| set enable_seqscan=false;
set enable_nestloop=false; |
L'explain donne alors les informations suivantes :
Citation:
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 :
- si ma requête est correcte ?
- 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.
