Le portail francophone de la géomatique

Quelques remarques :
- le st_instersects est redondant avec le premier test de l'opérateur &&. Ce dernier est fait automatiquement

Et quelques questions :
- Quelle est la largeur des données ? Y a t-il beaucoup de champs ?
- quel est la requete exacte envoyée à postgresql du coup ?
- Quel est le nombre d'enregistrements qui sont censés etre renvoyés ?
- Quels sont les disques de la machine ?
- le fichier shape qui est généré l'est il bien sur la machine locale ?

Et pour la suite il faudrait faire un explain de la requete, pour voir si :
- les indexes sont bien utilisés
- les estimations de nombre d'enregistrements renvoyés sont bien corrects

Dans les tests à faire aussi il faudrait vérifier que c'est bien la requete postgresql qui prend du temps en faisant la requete avec un create table plutot que ogr2ogr.

Si le problème à la fin est simplement qu'il faut extraire un grand nombre d'enregistrements, alors la solution c'est de la RAM, de la RAM, un SAN avec le plus de spindle possible.

Oui c'est vrai Pascal à raison , il vaut mieux toujours décomposer dans des tables temporaires le travail ....
vous pouvez aussi mettre ce message sur www.postgresql.org coté postgres/postgis il vous répondront rapidement
Michel

Bonjour, Merci de ces réponses

Voici les compléments aux questions :

- Quelle est la largeur des données ? Y a t-il beaucoup de champs ? 7 champs uniquement
- quel est la requete exacte envoyée à postgresql du coup ? la requête est envoyée en morceau par OGR.
Elle correspond à

 SELECT id,prec_plani,prec_alti,origin_bat,hauteur,z_min,z_max FROM bdtopo_bati_indifferencie_2008_l93_export WHERE the_geom && ST_GeomFromText('POLYGON((799607 6232734, 799607 6315411, 926824 6315411,926824 6232734,799607 6232734))',310024140) AND ST_RELATE (public.bdtopo_bati_indifferencie_2008_l93_export.the_geom,(SELECT the_geom FROM public.extent_decoupe_export WHERE gid = 1),'2********');

pour l'exemple avec le ST_relate à la place du ST_Intersects

- Quel est le nombre d'enregistrements qui sont censés etre renvoyés ? environ 320 000
- Quels sont les disques de la machine ? 2 disques SAS de 73 Go, 10 000 tour/min en raid1
- le fichier shape qui est généré l'est il bien sur la machine locale ? non sur un disque distant partagé entre plusieurs serveurs

En réponse à P. Pluvinet, la géométrie extraite ici par la requete "select the_geom from extent_decoupe where gid=1" est issue d'une table temporaire (il s'agit de l'union de toutes les communes (de BD TOPO) sur un département.
Si je spécifie uniquement extent_decoupe.the_geom sans clause where aucun résultat n'est renvoyé.

Le centroïde du bâti n'est pas non plus optimal car ce dernier bien que situé dans le polygone bati peut être hors de l'intersection alors que le polygone du bâti lui peut s'intersecter. Certes il y a peu de bâti à cheval d'une limite de département mais il y en a quelques uns (du moins géographiquement).

Nous allons persévérer avec les requêtes EXPLAIN

A titre comparatif, Arcgis pour ne citer que lui fait cette intersection en 2 minutes 30 à partir de fichiers shape indexés... :0

Dernière modification par Nicolas Granier (Wed 19 May 2010 10:26)

Je présumais que si on a une clause «where gid = 1» on ne retirera qu'un seul enregistrement. L'index spatial et le vacuum analyze sont donc inutiles dans ce cas. Mais on peut toujours essayer de faire cette table temporaire, j'ai vu des cas ressemblant ou ce genre de sous requete posait des problèmes.

Sans un EXPLAIN de la requete on va pas pouvoir déterminer réellement le souci en tout cas.

Peut-on en être sûr? J'avoue que les index spatiaux c'est un peu une boite noire pour moi. J'ai tendance à en créer sur toutes mes colonnes geometry car j'ai de la place sur mon disque dur. Quelqu'un aurait un lien ou une doc qui explique réellement ce qu'est un index  et particulièrement un index GIST sous Postgis? Merci d'avance.


Pour Nicolas Granier, je peux dire qu'avec un ordi similaire, je fais des requêtes bien plus complexes qui prennent beaucoup moins de temps. (ce genre de requête ne devrait pas prendre plus d'1 quart d'heure, voire 1/2 heure!) Par contre, je décompose souvent mes requêtes: c'est souvent plus lisibles et ca va souvent plus vite.
Pour l'export j'utilise pgsql2shp, c'est relativement rapide. Je connais moins ogr2ogr pour les import/export avec postgis. Je l'utilise plutôt pour des conversions MIF/MID en shape.

Dernière modification par ppluvinet (Wed 19 May 2010 13:35)

Un index d'une base de données, spatial ou pas, peut etre comparé à l'index d'une encyclopédie par exemple. Cela permet d'aller plus rapidement à l'article concerné. Mais si on a qu'un seul article dans l'encyclopédie, ou un très petit nombre, il sera plus rapide de parcourir tous les articles, que de lire l'index, puis aller à la page de l'article recherché.

Suivant les estimations de nombres d'enregistrement, qui sont faites à partir des statistiques calculées sur les données, les requetes seront effectuées en utilisant ou pas les indexes. Par exemple, faites une requete pouvant utiliser un index sur une table de 10 enregistrements, vous verrez que le planner postgresql va faire un scan séquentiel de toute la table plutot qu'utiliser l'index. Ça peut etre le cas sur des tables bien plus grosse suivant la selectivité de la condition et la distribution des valeurs dans la table.

Pour les indexes géométriques PostGIS, ils sont basés sur GIST, qui est une structure d'indexes généralistes de PostgreSQL. L'implémentation des indexes géométriques est un RTREE.
Pour plus d'info sur les RTREE et leur fonctionnement, on peut commencer par là :

http://en.wikipedia.org/wiki/R-tree
La page traduite en français n'existe pas (encore), mais les dessins donnent déjà une bonne indication du fonctionnement.

Pour les indexes au sens plus général, on peut commencer par ici :
http://fr.wikipedia.org/wiki/Index_%28b … C3%A9es%29
et
http://fr.wikipedia.org/wiki/Arbre_B

Hope this helps, mais on s'éloigne du sujet initial, qu'on ne résoudra pas à l'aveuglette sans plus d'info cependant.

Je ne comprend pas le Gid = 1
Perso, j'aurais fait une requête qui ressemble à :

 
 CREATE TABLE AS
 SELECT id,prec_plani,prec_alti,origin_bat,hauteur,z_min,z_max 
 FROM bdtopo_bati_indifferencie_2008_l93_export a,
 departement b 
 WHERE   
 ST_INTERSECTS (a.the_geom,b.the_geom);

Pour ton explain analyse tu peux réduire la taille de ta requete . Par exemple :

explain analyse
 SELECT id,prec_plani,prec_alti,origin_bat,hauteur,z_min,z_max 
 FROM bdtopo_bati_indifferencie_2008_l93_export a,
 departement b 
 WHERE   
 ST_INTERSECTS (a.the_geom,b.the_geom)
LIMIT 10000;

Ok merci pour le tuyau

Voici le résultat d 'EXPLAIN

"Seq Scan on _ng_differentiel_bdtopo_bati_indifferencie_2008_l93_2  (cost=1.01..73880.16 rows=126028 width=67)"
"  Filter: ((the_geom && '0103000020CC977A12010000000500000000000000EE66284100000080A7C6574100000000EE662841000000C06417584100000000D0482C41000000C06417584100000000D0482C4100000080A7C6574100000000EE66284100000080A7C65741'::geometry) AND st_relate(the_geom, $0, '2********'::text))"
"  InitPlan"
"    ->  Seq Scan on _ng_differentiel_extent_decoupe  (cost=0.00..1.01 rows=1 width=32)"
"          Filter: (gid = 1)"

Nombre d'enregistrements dans la table bati au total => 1 118 610
Nombre d'enregistrements devant être retournés => environ 320 000

A ce que je crois comprendre les index ne sont pas utilisés, (ce qui serait normal sur la couche extend_decoupe) mais pas normal sur la couche de bâti.

Effectivement pas d'index utilisé. La sélectivité est faible ceci dit, on renvoie beaucoup d'enregistrements comparé à la taille totale. Dans l'absolu l'utilisation des indexes pourrait etre pénalisante dans le cas ou le filtre sur chaque ligne est faible, et que les données sont séquentielles. Mais dans le cas présent je pense qu'on gagnerait à les utiliser.

Les pistes à suivre :
- voir le résultat du explain analyze pour avoir les temps détaillés de la requete
- tester une requete avec st_intersects plutot que st_relate et vérifier par explain l'utilisation indexes
- relancer la requete avec set «enable_seqscan = off;» avant pour forcer l'utilisation des indexes (à ne faire QUE pour des tests de debug du planner !) et voir le temps d'exécution
- mettre la requete dans un create table pour etre bien sur que ce n'est pas l'envoi des données qui est long

Si ça n'avance rien, il peut y avoir :
- des soucis liés à la comparaison d'une grosse géométrie avec beaucoup d'autres
- des problèmes de config de postgresql
- des problèmes de disques pas assez rapide ou mal configurés

Re bonjour,

je rebondis par rapport à la réponse de Vincent (au passage, merci pour les retours détaillés) :

** on va essayer d'avoir le retour d'EXPLAIN ANALYZE (mais pas d'ici demain matin vu la lenteur....) et on le communiquera dès que possible.

** la requête avec ST_INTERSECTS n'est pas plus rapide. De plus, ST_INTERSECTS nous récupère également les polygones extérieurs aux polygones d'extraction et qui ont une limite commune (que nous ne voulons pas extraire), d'où le ST_RELATE ('2********').

** J'ai fait le test avec ENABLE_SEQSCAN=OFF et/ou random_page_cost=1 sans plus de succès.

** J'ai également fait le test de lancer la requête dans un create table hier soir. Au bout de 7h00 de traitement toujours pas de résultat...

** Pour les problèmes de config de postgres, même si c'était le cas, ça ne devrait pas être aussi long, à mon avis. De plus, teste les requêtes sur notre serveur et sur une install locale en parallèle, sans plus de succès...

** Concernant les soucis liés à la comparaison d'une grosse géométrie avec beaucoup d'autres, j'avais pensé à cela au début. En effet, le polygone qui doit servir de base à l'extraction est un agrégat de communes issues de la BD TOPO de l'IGN, ce qui veut dire le nombre de vertex de ce polygone est assez conséquent. Les polygônes du bati ont quant à eux beaucoup moins de points. Si c'est le cas, je ne vois pas comment résoudre le problème, malheureusement.

Encore merci pour tous vos retours. Je continue à creuser avec Nicolas. A ce rythme nous arriverons bientôt au pays du soleil levant .

Sylvain

Dans la table geometry_columns nous avons les valeurs suivantes :

---- bdtopo_bati_indifferencie_2008_l93 :
   *** coord_dimension : 4
   *** srid : 310024140 (qui correspond au lambert 93 du registre IGNF)
   *** type : multipolygon

---- extent_decoupe_export
   *** coord_dimension : 2
   *** srid : 310024140 (qui correspond au lambert 93 du registre IGNF)
   *** type : multipolygon

Les dimensions ne sont pas les mêmes mais ça ne pose pas de problèmes, normalement.

L'extrait de PGAdminIII nous donne bien :

CREATE INDEX bdtopo_bati_indifferencie_2008_l93_the_geom_gist
  ON bdtopo_bati_indifferencie_2008_l93
  USING gist
  (the_geom);

En effet, nous utilisons le && car la fonction ST_RELATE ne l'utilise pas de manière implicite (tout du moins dans la version 1.4.1).

Pourquoi la table bdtopo_bati_indifferencie_2008_l93 a-t-elle pour dimension 4 ?

si cela n'est pas indispensable :
      passer la en dimension 2 :
      - en faisant sauter la contrainte enforce_dims...  sur la table,
      - en mettant a jour la table geometry_columns,
      - mettre a jour la table
                        UPDATE  bdtopo_bati_indifferencie_2008_l93  SET the_geom = force_2d(the_geom);
                         VACUUM FULL ANALYZE  bdtopo_bati_indifferencie_2008_l93 ;

      - remettre la contrainte enforce_dims...

      et alors relancer l'export.



Jérôme

Je suis tout a fait d'accord avec vous pour :

Encore d'autres questions :

- Quels paramètres avez vous modifiés dans le fichier postgresql.conf après tuning ?

- Avez vous vérifié la géométrie stockée dans la table extent_decoupe ?
  select ST_IsSimple(the_geom),ST_IsValidReason(the_geom) from extent_decoupe where gid=1
 
 

Jérôme

Bonsoir,

quelques tests sur la table bati_indifferencie (telle que fournie dans la bd_topo) en Languedoc-Roussillon (745000 objets environ) et le département du GARD (353 communes).

Je n'avais jamais testé jusqu'à aujourd'hui mais en effet, il semble bien plus efficace de ne pas agréger les objets avant l'intersection, quit à faire un distinct si des géométries se trouvaient à cheval sur 2 communes.

intersection avec le département : Total runtime: 243869.209 ms

SELECT ign_bd_topo.bati_indifferencie.*
  FROM ign_bd_topo.bati_indifferencie JOIN departement ON st_intersects(departement.geometrie, ign_bd_topo.bati_indifferencie.geometrie)
  WHERE departement.num_insee='30';

intersection avec les communes du département (where num_insee like '30%') : Total runtime: 16039.480 ms

SELECT ign_bd_topo.bati_indifferencie.*
  FROM ign_bd_topo.bati_indifferencie JOIN commune ON st_intersects(commune.geometrie, ign_bd_topo.bati_indifferencie.geometrie)
  WHERE commune.num_insee like '30%';

intersection avec les communes du département (sous-requête) : Total runtime: 15228.998 ms (18484 ms avec DISTINCT)

SELECT ign_bd_topo.bati_indifferencie.*
  FROM ign_bd_topo.bati_indifferencie JOIN commune ON st_intersects(commune.geometrie, ign_bd_topo.bati_indifferencie.geometrie)
  WHERE commune.num_insee IN (select num_insee from commune where num_insee like '30%');

Dernière modification par Mathieu BOSSAERT (Thu 20 May 2010 09:28)

Re bonjour,

j'ai fait quelques tests sur la base des remarques de Mathieu BOSSAERT. Avant de vous faire part des résultats, j'aurais une question pour Mathieu. La couche commune sur laquelle vous avez fait les tests est-elle la couche commune de la BD TOPO ?

D'autre part, vos tests très intéressants portent sur la fonction st_intersects. Avez-vous fait des tests avec la fonction st_relate qui nous intéresse plus spécifiquement ?

Pour mes tests, je me suis basé dans un premier temps sur une extraction de la table communes de la bd_topo pour les département du 13 (qui compte 119 communes). Voici mes résultats :

Premier test :

SELECT DISTINCT bdtopo_commune_2010_l93.* FROM bdtopo_commune_2010_l93 JOIN extract_coms_bdtopo_2010 ON st_intersects(extract_coms_bdtopo_2010.the_geom,bdtopo_commune_2010_l93.the_geom) WHERE extract_coms_bdtopo_2010.code_insee like '13%';

Résultat : 155 communes extraites en 17605 ms. Cependant la sélection donne 155 communes alors qu'il n'en faudrait que 119 (problème du st_intersects dans certains cas d'où notre volonté d'utliser un st_relate qui permet des requêtes plus fines).
Un EXPLAIN ANALYZE de cette requête donne :

"  ->  Sort  (cost=121.04..121.45 rows=163 width=75076) (actual time=17486.460..17487.632 rows=801 loops=1)"
"        Sort Key: bdtopo_commune_2010_l93.gid, bdtopo_commune_2010_l93.arrondisst, bdtopo_commune_2010_l93.canton, bdtopo_commune_2010_l93.code_insee, bdtopo_commune_2010_l93.depart, bdtopo_commune_2010_l93.id, bdtopo_commune_2010_l93.multican, bdtopo_commune_2010_l93.nom, bdtopo_commune_2010_l93.popul, bdtopo_commune_2010_l93.prec_plani, bdtopo_commune_2010_l93.region, bdtopo_commune_2010_l93.statut, bdtopo_commune_2010_l93.the_geom"
"        Sort Method:  quicksort  Memory: 228kB"
"        ->  Nested Loop  (cost=0.00..115.06 rows=163 width=75076) (actual time=74.385..17179.119 rows=801 loops=1)"
"              Join Filter: _st_intersects(extract_coms_bdtopo_2010.the_geom, bdtopo_commune_2010_l93.the_geom)"
"              ->  Seq Scan on extract_coms_bdtopo_2010  (cost=0.00..22.04 rows=117 width=76575) (actual time=0.100..12.254 rows=119 loops=1)"
"                    Filter: ((code_insee)::text ~~ '13%'::text)"
"              ->  Index Scan using bdtopo_commune_2010_l93_the_geom_gist on bdtopo_commune_2010_l93  (cost=0.00..0.53 rows=1 width=75076) (actual time=0.072..0.202 rows=8 loops=119)"
"                    Index Cond: (extract_coms_bdtopo_2010.the_geom && bdtopo_commune_2010_l93.the_geom)"
"Total runtime: 17814.884 ms"

Les index spatiaux sont bien utilisés.


Deuxième test : utilisation de st_relate sans index spatiaux

SELECT DISTINCT bdtopo_commune_2010_l93.* FROM bdtopo_commune_2010_l93 JOIN extract_coms_bdtopo_2010 ON st_relate(bdtopo_commune_2010_l93.the_geom, extract_coms_bdtopo_2010.the_geom,'2********') WHERE extract_coms_bdtopo_2010.code_insee like '13%';

Dans ce cas, pas d'utlisation des index spatiaux car pas d'utilisation du &&.
Résultat : 119 communes extraites en 209441 ms

Troisième test :utilisation de st_relate avec index spatiaux

SELECT DISTINCT bdtopo_commune_2010_l93.* FROM bdtopo_commune_2010_l93 JOIN extract_coms_bdtopo_2010 ON st_relate(bdtopo_commune_2010_l93.the_geom, extract_coms_bdtopo_2010.the_geom,'2********') WHERE bdtopo_commune_2010_l93.the_geom && ST_GeomFromText('POLYGON((799607 6232734, 799607 6315411, 926824 6315411,926824 6232734,799607 6232734))',310024140)AND extract_coms_bdtopo_2010.code_insee like '13%';

Résultat : 119 communes extraites en 80977ms
Un EXPLAIN ANALYZE de cette requête donne :

"Unique  (cost=157075.01..157372.58 rows=218 width=75076) (actual time=66888.704..66924.207 rows=119 loops=1)"
"  ->  Sort  (cost=157075.01..157096.27 rows=8502 width=75076) (actual time=66888.697..66888.845 rows=119 loops=1)"
"        Sort Key: bdtopo_commune_2010_l93.gid, bdtopo_commune_2010_l93.arrondisst, bdtopo_commune_2010_l93.canton, bdtopo_commune_2010_l93.code_insee, bdtopo_commune_2010_l93.depart, bdtopo_commune_2010_l93.id, bdtopo_commune_2010_l93.multican, bdtopo_commune_2010_l93.nom, bdtopo_commune_2010_l93.popul, bdtopo_commune_2010_l93.prec_plani, bdtopo_commune_2010_l93.region, bdtopo_commune_2010_l93.statut, bdtopo_commune_2010_l93.the_geom"
"        Sort Method:  quicksort  Memory: 49kB"
"        ->  Nested Loop  (cost=22.15..626.10 rows=8502 width=75076) (actual time=149.132..66880.523 rows=119 loops=1)"
"              Join Filter: st_relate(bdtopo_commune_2010_l93.the_geom, extract_coms_bdtopo_2010.the_geom, '2********'::text)"
"              ->  Index Scan using bdtopo_commune_2010_l93_the_geom_gist on bdtopo_commune_2010_l93  (cost=0.00..30.07 rows=218 width=75076) (actual time=0.100..4.685 rows=226 loops=1)"
"                    Index Cond: (the_geom && '0103000020CC977A12010000000500000000000000EE66284100000080A7C6574100000000EE662841000000C06417584100000000D0482C41000000C06417584100000000D0482C4100000080A7C6574100000000EE66284100000080A7C65741'::geometry)"
"              ->  Materialize  (cost=22.15..23.32 rows=117 width=76575) (actual time=0.002..0.410 rows=119 loops=226)"
"                    ->  Seq Scan on extract_coms_bdtopo_2010  (cost=0.00..22.04 rows=117 width=76575) (actual time=0.025..1.696 rows=119 loops=1)""                          Filter: ((code_insee)::text ~~ '13%'::text)"
"Total runtime: 66924.572 ms"

Quatrième test :utilisation de st_relate avec croisement avec un polygone assemblé des communes (notre méthode initiale)

SELECT * FROM bdtopo_commune_2010_l93 WHERE the_geom && ST_GeomFromText('POLYGON((799607 6232734, 799607 6315411, 926824 6315411,926824 6232734,799607 6232734))',310024140) AND ST_RELATE (public.bdtopo_commune_2010_l93.the_geom,(SELECT the_geom FROM public.extent_decoupe_polygone WHERE gid = 1),'2********');

Résultat : 155 communes extraites en 68057 ms. A en croire les résultats, cette méthode est la plus rapide dans ce cas de figure.
Un EXPLAIN ANALYZE donne :

"Index Scan using bdtopo_commune_2010_l93_the_geom_gist on bdtopo_commune_2010_l93  (cost=1.01..31.62 rows=73 width=75076) (actual time=354.028..59181.370 rows=119 loops=1)"
"  Index Cond: (the_geom && '0103000020CC977A12010000000500000000000000EE66284100000080A7C6574100000000EE662841000000C06417584100000000D0482C41000000C06417584100000000D0482C4100000080A7C6574100000000EE66284100000080A7C65741'::geometry)"
"  Filter: st_relate(the_geom, $0, '2********'::text)"
"  InitPlan 1 (returns $0)"
"    ->  Seq Scan on extent_decoupe_polygone  (cost=0.00..1.01 rows=1 width=3684968) (actual time=0.079..0.081 rows=1 loops=1)"
"          Filter: (gid = 1)"
"Total runtime: 59182.008 ms"

Les index spatiaux sont bien utilisés.

Pour conclure, toutes les méthodes sont bonnes mais concernant notre cas précis l'utilisation du st_relate sur de gros ensembles d'objets ne semble pas bien être géré par Postgis, malheureusement. Quant au fait d'utiliser le croisement avec les communes plutôt qu'avec un polygone unique constitué de l'agrégat des communes souhaitées, je ne sais pas que dire pour trancher compte tenue des résultats mentionnés ci-dessus.

De ce que je comprends, PostGIS n'est pas si performant que cela sur des fonctions de requêtes topologiques "avancées" telles que st_relate.

Dans tous les cas, si vous avez des remarques et suggestions nous sommes preneurs car nous bloquons un peu malgré les tests que nous avons pu mener et vos remarques pertinentes.