Annonce
L'appel à participation est ouvert jusqu'au 19 janvier 2025!
#1 Tue 02 June 2009 12:26
- anonymous
- Participant actif
- Date d'inscription: 1 Jul 2013
- Messages: 109
PGIS Jointure spatiale: normal que ça soit aussi lent ?
Dans ma base, j'ai 3 couches, par ordre de grandeur:
- Une première qui se constitue d'une quinzaine de terroirs
- Une deuxième d'ilots (regroupant des parcelles)
- Une troisième de parcelles (une quinzaine de milliers)
Lorsque j'effectue une jointure spatiale entre 2 couches avec n'importe quel opérateur spatial, la requête met moins de 5 secondes à s'exécuter. Mais lorsqu'il s'agit de joindre spatialement les 3 couches, je peux attendre bien 5 minutes parfois avant d'avoir un résultat.
Voici la requête (Shape correspondant à la colonne géométrique) :
SELECT distinct p.codeparcelle, i.exploitant, t.codeterroir from parcelles p, ilots i, terroirs t WHERE i.typeculture = '05'
AND intersects(p.Shape,i.Shape) AND p.Shape && i.Shape AND intersects(t.Shape,i.Shape) ;
En théorie, les parcelles sont contenues entièrement dans un ilot mais ce n'est pas toujours le cas, d'où le intersects au lieu du contains)
En considérant que les 3 tables sont toutes indexées spatialement (GIST sur la colonne géométrique) et indexées de façon "classique" :
- Est ce normal que le fait de joindre spatialement plus de 2 tables ralentisse à ce point la requête ? Et donc il serait préférable d'effectuer une jointure non spatiale entre 2 des 3 tables)
- Ou est ce que vous voyez une façon de mieux construire la requête ?
Dernière modification par Bruno (Mon 02 May 2011 21:53)
Hors ligne
#2 Tue 02 June 2009 13:18
Re: PGIS Jointure spatiale: normal que ça soit aussi lent ?
Bonjour,
As tu créé desindex sur tes champs ?
Y.
Yves Jacolin, bénévole de l'association GeoRezo.net, agit au nom et pour le compte de l'association - Partageons ce qui nous départage !! - GeoRezo vous aide ? Aidez GeoRezo !
Hors ligne
#3 Tue 02 June 2009 13:23
- anonymous
- Participant actif
- Date d'inscription: 1 Jul 2013
- Messages: 109
Re: PGIS Jointure spatiale: normal que ça soit aussi lent ?
Bonjour Yves,
J'ai 2 index sur chacune de mes tables:
- un Index spatial GIST sur la colonne géométrique
- un Index Btree sur la colonne ObjectID
Faut-il que je crée d'autres index sur chacun des champs ?
Hors ligne
#4 Tue 02 June 2009 13:40
Re: PGIS Jointure spatiale: normal que ça soit aussi lent ?
Bonjour,
Dans ta requête tu utilises les fonctions intersects pour lesquelles on doit activer l'utilisation des index, comme tu l'as fait avec p.Shape && i.Shape. Or tu ne les actives pas pour le deuxième intersects, rajoute AND t.Shape && i.Shape :
Code:
intersects(p.Shape,i.Shape) AND p.Shape && i.Shape AND intersects(t.Shape,i.Shape) ;
Autres solutions, utiliser les fonctions ST_Intersects
Y.
Dernière modification par Yves (Tue 02 June 2009 13:41)
Yves Jacolin, bénévole de l'association GeoRezo.net, agit au nom et pour le compte de l'association - Partageons ce qui nous départage !! - GeoRezo vous aide ? Aidez GeoRezo !
Hors ligne
#5 Tue 02 June 2009 13:59
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: PGIS Jointure spatiale: normal que ça soit aussi lent ?
Dans ma base, j'ai 3 couches, par ordre de grandeur:
- Une première qui se constitue d'une quinzaine de terroirs
- Une deuxième d'ilots (regroupant des parcelles)
- Une troisième de parcelles (une quinzaine de milliers)
Lorsque j'effectue une jointure spatiale entre 2 couches avec n'importe quel opérateur spatial, la requête met moins de 5 secondes à s'exécuter. Mais lorsqu'il s'agit de joindre spatialement les 3 couches, je peux attendre bien 5 minutes parfois avant d'avoir un résultat.
Voici la requête (Shape correspondant à la colonne géométrique) :
SELECT distinct p.codeparcelle, i.exploitant, t.codeterroir from parcelles p, ilots i, terroirs t WHERE i.typeculture = '05'
AND intersects(p.Shape,i.Shape) AND p.Shape && i.Shape AND intersects(t.Shape,i.Shape) ;
En théorie, les parcelles sont contenues entièrement dans un ilot mais ce n'est pas toujours le cas, d'où le intersects au lieu du contains)
En considérant que les 3 tables sont toutes indexées spatialement (GIST sur la colonne géométrique) et indexées de façon "classique" :
- Est ce normal que le fait de joindre spatialement plus de 2 tables ralentisse à ce point la requête ? Et donc il serait préférable d'effectuer une jointure non spatiale entre 2 des 3 tables)
- Ou est ce que vous voyez une façon de mieux construire la requête ?
Bonjour
Pouvez-vous indiquer les versions de postgis et postgresql (select postgis_full_version() )
et nous indiquer le plan de requete tel que vu par PG: rajouter la commande 'explain' devant le select.
Ca permettra de voir si les index sont bien utilisés.
Nicolas
Dernière modification par Bruno (Mon 02 May 2011 21:53)
Hors ligne
#6 Tue 02 June 2009 14:14
- anonymous
- Participant actif
- Date d'inscription: 1 Jul 2013
- Messages: 109
Re: PGIS Jointure spatiale: normal que ça soit aussi lent ?
Yves: j'avais bien testé votre solution en rajoutant "AND t.Shape && i.Shape" mais cela ralentit: 30 secondes au lieu de 10.
Il en va de même pour l'utilisation de st_intersects, 30 secondes...
Ma version de PostGis: "POSTGIS="1.3.5" GEOS="3.0.3-CAPI-1.4.2" PROJ="Rel. 4.6.1, 21 August 2008" USE_STATS"
Voici l'Explain de ma requête:
"Unique (cost=2964.62..2978.41 rows=919 width=83)"
" -> Sort (cost=2964.62..2966.92 rows=919 width=83)"
" Sort Key: p.codeparcelle, i.exploitant, p.bassin_lib, i.typeculture, t.codeterroir"
" -> Nested Loop (cost=1.15..2919.39 rows=919 width=83)"
" Join Filter: intersects(t.shape, i.shape)"
" -> Nested Loop (cost=0.00..2856.18 rows=197 width=2334)"
" Join Filter: intersects(p.shape, i.shape)"
" -> Seq Scan on ilots i (cost=0.00..2124.15 rows=96 width=2321)"
" Filter: ((typeculture)::text = '05'::text)"
" -> Index Scan using idx_parcelles_unq_gist on parcelles p (cost=0.00..7.61 rows=1 width=765)"
" Index Cond: (p.shape && i.shape)"
" Filter: (p.shape && i.shape)"
" -> Materialize (cost=1.15..1.29 rows=14 width=77)"
" -> Seq Scan on terroirs t (cost=0.00..1.14 rows=14 width=77)"
Dernière modification par Bruno (Mon 02 May 2011 21:53)
Hors ligne
#7 Tue 02 June 2009 16:16
Re: PGIS Jointure spatiale: normal que ça soit aussi lent ?
Bonjour,
voici une syntaxe équivalente :
Code:
SELECT distinct p.codeparcelle, i.exploitant, t.codeterroir FROM terroirs t JOIN ilots i ON st_intersects(t.Shape,i.Shape) JOIN parcelles p ON st_intersects(p.Shape,i.Shape) WHERE i.typeculture = '05'
Mais je ne pense pas que ça change grand chose...
Mathieu Bossaert
Mathieu BOSSAERT
Association GeoRezo
Hors ligne
#8 Tue 02 June 2009 17:09
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: PGIS Jointure spatiale: normal que ça soit aussi lent ?
Yves: j'avais bien testé votre solution en rajoutant "AND t.Shape && i.Shape" mais cela ralentit: 30 secondes au lieu de 10.
Il en va de même pour l'utilisation de st_intersects, 30 secondes...
Ma version de PostGis: "POSTGIS="1.3.5" GEOS="3.0.3-CAPI-1.4.2" PROJ="Rel. 4.6.1, 21 August 2008" USE_STATS"
Voici l'Explain de ma requête:
"Unique (cost=2964.62..2978.41 rows=919 width=83)"
" -> Sort (cost=2964.62..2966.92 rows=919 width=83)"
" Sort Key: p.codeparcelle, i.exploitant, p.bassin_lib, i.typeculture, t.codeterroir"
" -> Nested Loop (cost=1.15..2919.39 rows=919 width=83)"
" Join Filter: intersects(t.shape, i.shape)"
" -> Nested Loop (cost=0.00..2856.18 rows=197 width=2334)"
" Join Filter: intersects(p.shape, i.shape)"
" -> Seq Scan on ilots i (cost=0.00..2124.15 rows=96 width=2321)"
" Filter: ((typeculture)::text = '05'::text)"
" -> Index Scan using idx_parcelles_unq_gist on parcelles p (cost=0.00..7.61 rows=1 width=765)"
" Index Cond: (p.shape && i.shape)"
" Filter: (p.shape && i.shape)"
" -> Materialize (cost=1.15..1.29 rows=14 width=77)"
" -> Seq Scan on terroirs t (cost=0.00..1.14 rows=14 width=77)"
Se peut-il que la table des terroirs contienne de gros objets (beaucoup de sommets formant le polygon) ou s'etende sur une vaste zone ?
Je ne vois pas dans le plan d'utilisation d'index sur la table terroir, mais un scan sequentiel: seq_scan.
Comme il y a peu de lignes, le planner choisit le scan sequentiel, plus rapide qu'un acces a l'index.
Si les objets sont gros, il peut neanmoins que l'utilisation de l'index soit plus pertinente.
cf. ce chapitre dans la doc postgis:
http://postgis.refractions.net/document … #id3016057
pas mal de conseils pour organiser ses données et voir si les resultats sont meilleurs.
Nicolas
Dernière modification par Bruno (Mon 02 May 2011 21:54)
Hors ligne
#9 Wed 03 June 2009 12:20
- anonymous
- Participant actif
- Date d'inscription: 1 Jul 2013
- Messages: 109
Re: PGIS Jointure spatiale: normal que ça soit aussi lent ?
Effectivement, la table des terroirs contient des objets constitués de beaucoup de points et s'étendant sur de très vastes zones.
La solution " SET enable_seqscan TO off; ", tirée du site, m'a permis d'avoir un temps d'exécution de la requête 4 fois plus court.
(version française du document: http://www.postgis.fr/node/214)
Voici donc la requête:
SELECT distinct p.codeparcelle, i.exploitant, t.codeterroir from parcelles p, ilots i, terroirs t WHERE i.typeculture = '05'
AND ST_Contains(i.Shape,ST_Centroid(p.Shape)) AND ST_Contains(t.Shape,ST_Centroid(i.Shape));
Merci à tous.
Dernière modification par Bruno (Mon 02 May 2011 21:54)
Hors ligne
