[Postgis] Performances d'une requête d'intersection entre points et polygônes

[ced]

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 :

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 :

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 :

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.

[xavier-Pierre]

bonjour,
je pense que la fonction within serait plus adaptée, ou même mieux l'opérateur @>, à essayer ... mais sans garantie

[ced]

Merci pour cette piste.

J'ai fait quelques essais. Avec WITHIN, pas grand chose de mieux.
J'ai essayé avec && :

Code :

1
2
3
4
SELECT p.id, s.code
FROM pts p
INNER JOIN polyg s ON s.the_geom && p.the_geom
ORDER BY 1;

Ce qui me renvoie 200 000 lignes très rapidement (3 secondes), là où j'en attends 80 000, vu que les polygones de ma couche ne se superposent pas (testé dans d'autres outils SIG).
Et si j'essaie d'afficher le résultat d'une fonction ST_ dans le SELECT, alors là ça remet des plombes .

Je sens qu'il va falloir que je visualise un peu ce qui résulte de l'import de mon shapefile, parce que c'est curieux, tout ça...

[xavier-Pierre]

et en combinant

Code :

 WHERE s.the_geom && p.the_geom AND St_Intersects(p.the_geom, s.the_geom)

car je crois que l'operateur && utilise l'index gist mais travaille sur les rectangles englobants ce qui explique peut-être les 200000 lignes au lieu des 80000 attendues

[ced]

C'est la première requête que j'ai testée.
Normalement, depuis PostGIS 1.5.0, ST_Intersects est également sensé utiliser l'index GIST. Quand on regarde le plan d'exécution, c'est loin d'être évident.

Quoi qu'il en soit, cette requête ne tourne pas mieux que les autres...

J'avoue ne pas bien comprendre ce qui se passe...

[SQLpro]

Avez vous pensez à optimiser les shared_buffers ? (50 à 75% de la RAM).

A +

[ced]

Bonjour,

Oui, le paramètre shared_buffers est à 2 GB. Soit 50% de la RAM du serveur.

[vmolines]

Des news concernant ton problème ?

[ced]

Pour le moment, aucune.
Je n'ai pas encore pris le temps de remonter la question sur les listes PostGis ou PostgreSQL, mais je compte le faire.

Je me repencherai dessus dès que j'aurais un peu de temps à y consacrer.

[vmolines]

Même si je ne connais pas l'extension GIS de Postgre, j'avais cherché des infos concernant ton problème (mauvaise utilisation des indexes spatiaux). J'ai dû tomber sur les mêmes sujets que toi car tu avais déjà testé des solutions proposées.

Comme ça m'intéresse je suis le sujet.

[estofilo]

En tout cas pour les questions de performance, il faut sortir des EXPLAIN ANALYZE (qui exécutent la requête et sortent les estimations et les vrais temps) au lieu des EXPLAIN simples (qui n'exécutent pas la requête et ne sortent que les estimations).