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 +

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

[ced]

Bonjour,

Je reviens vers vous après un certain temps.
J'ai pu avancer sur le sujet. La première des choses, c'est que j'ai migré sous PostgreSQL 9.1.4 et PostGIS 2.0.1.

Désormais, le croisement se fait correctement, en environ 4 minutes.
Toutefois, je pense sincèrement que Postgres peut faire mieux que ça, mais il s'entête à me faire un scan séquentiel de table contenant mes polygones (qui s'appelle SER désormais).
J'essaie pourtant de forcer le planificateur à passer l'index spatial, avec SET enable_seqscan TO off;, mais rien n'y fait.

Voici le résultat du EXPLAIN ANALYZE :

Sort (cost=20000007825.61..20000007961.28 rows=54268 width=20) (actual time=242635.380..242646.944 rows=80930 loops=1)
Sort Key: p.npp
Sort Method: quicksort Memory: 9395kB
-> Nested Loop (cost=20000000000.00..20000003558.03 rows=54268 width=20) (actual time=59.128..242072.261 rows=80930 loops=1)
Join Filter: _st_intersects(p.geom, s.geom)
-> Seq Scan on ser s (cost=10000000000.00..10000000111.73 rows=473 width=142276) (actual time=0.013..0.979 rows=473 loops=1)
-> Index Scan using pts7_geom_gist on pts7 p (cost=0.00..7.02 rows=1 width=209) (actual time=0.042..0.671 rows=422 loops=473)
Index Cond: (geom && s.geom)
Total runtime: 242661.902 ms

Comment faire pour forcer l'index spatial sur la table SER ?

[estofilo]

Cette table SER a très peu de lignes, elle est lue en 0.1ms, ce n'est pas vraiment là qu'est consommé le temps d'exécution.

L'opérateur d'intersection pour la jointure est exécuté environ 80000x573 fois. Peut-être que les index spatiaux accélèrent le calcul d'intersection, mais apparemment pas au point que 45 millions de ces calculs passent rapidement.

N'utilisant pas postgis personnellement, je ne sais pas si c'est une problématique typique qui a une solution connue, mais du point de vue du plan d'exécution uniquement, je ne vois pas trop de potentiel d'amélioration.

[ced]

Merci de cette réponse.
Mais justement, de le cas de requêtes spatiales, il me semblait avoir compris que l'index spatial était utilisé pour la jointure, et pas forcément pour le parcours de la table...

[SQLpro]

Pesonnellement j'ai fait des tests en vrai grandeur en comparant PostGreSQL 9 avec PostGis 2 et SQL Server 2008 R2 sur une même base géographique avec de très nombreuses requêtes (base contenant départements et communes de France).
Résultat des courses, SQL Server bât à plate couture PG/PostGIS.
En effet, ce qui fait la différence c'est la méthode d’indexation.
En fait, PG ne propose que des index de type GIST qui dans bien des cas ne peuvent être utilisés par certaines méthodes géométrique, contrrairement à SQL Server qui utilise des QuadTree (équilibrés) associé à des courbes de Hilbert pour ce faire.

D'où l'inutilisation des index GiST dans bien des cas...

J'ai l'intention de publier un article sur le sujet (benchmark d'explotation PostGIS/ SQL Server sur le GIS) vraisemblablement à la rentrée.

A +

[ced]

Les seules comparaisons que j'avais menées se faisaient entre Oracle et Postgres sur les fonctions spatiales et, il y a de cela environ 2 ans, l'écart n'était pas aussi tranché que ce que tu annonces sur SQL Server (que je n'ai pas testé).
Oracle était plus rapide pour certains traitements, moins pour d'autres, et équivalent sur d'autres encore... Mais pas de tendance nette entre les 2.

En tout cas, mon problème semble relever de ce qui est décrit ici : http://postgis.org/documentation/man...html#id2940621
Mais je ne parviens pas à empêcher le parcours séquentiel de la table des 80 polygones...