Discussion :

[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 :

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.

[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 :

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

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 :

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

 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 +