Re: utilisation d'un index ou pas par le planificateur.

Bonjour,

Le ven. 6 août 2021 à 16:50, Alain Benard <alain(dot)benard(at)inrae(dot)fr> a écrit :

> Merci Cedric des pistes évoquées. Je vais regarder ça après une petite
> semaine de congés (tuilage + fin et matérialisation des geom en amont).
> Coté opérateurs je ne suis pas certains d’être éligible avec des opérations
> entre raster et geométries.
>
> Petite précision : sur le serveur d’origine (postgis 2.5.5 / postgres 12)
> l‘lindex est bien utilisé sans besoin d’une clause limit.
>

Précision de ma part. Un EXPLAIN seul ne sert pratiquement jamais à rien si
on cherche à améliorer les performances d'une requête. Avec un EXPLAIN
ANALYZE, on commence à avoir suffisamment d'informations pour échafauder
des hypothèses. Donc la prochaine fois, merci de fournir un "EXPLAIN
ANALYZE" :) (voire mieux un "EXPLAIN (ANALYZE,BUFFERS)".

Bon weekend.
>
>
>
> *De :* Cedric Duprez <Cedric(dot)Duprez(at)ign(dot)fr>
> *Envoyé :* vendredi 6 août 2021 14:51
> *À :* pgsql-fr-generale(at)lists(dot)postgresql(dot)org
> *Objet :* Re: utilisation d'un index ou pas par le planificateur.
>
>
>
> Bonjour Alain,
>
> La gestion des requêtes spatiales est souvent complexe et dépend beaucoup
> des données en jeu.
> Plusieurs pistes toutefois à explorer pour tenter d’améliorer la
> planification et utiliser l’index, à la lecture des différents éléments :
>
> 1. Dans la mesure où il s’agit d’un croisement, visiblement, entre
> une couche raster tuilée et des points, plus les tuiles sont petites et
> plus la requête sera performante (l'idée, c'est que la taille moyenne des
> tuiles se rapproche de la taille moyenne des objets avec lesquels on les
> croise, or là ce sont des points donc il faut de petites tuiles) ;
>
> 2. Essayer de passer au préalable par la création des géométries de
> type POINT pour les points, et d’indexer cette géométrie, puis faire le
> croisement avec le raster (là, la transformation à la volée des coordonnées
> X et Y des points, avec ajout d’un SRID, ne doit pas aider le
> planificateur). Et donc, ajouter préalablement ces étapes (création de la
> géométrie puis indexation) dans une table temporaire par exemple, avant de
> faire la jointure spatiale ;
>
> 3. De mémoire, il est souvent conseillé, pour favoriser l’emploi
> des index spatiaux dans les requêtes, d’écrire les jointures d’intersection
> sous la forme : (geom1 && geom2) AND ST_Intersects(geom1, geom2). Il y a un
> post dans le blog de Paul Ramsay, qui date un peu, sur cette écriture des
> jointures spatiales.
>
> En espérant que ça puisse aider à résoudre le problème…
> Bon courage,
>
> --
> Cédric DUPREZ
> ___
>
> Expert bases de données
>
> IGN - DIRECTION DES OPÉRATIONS ET DES TERRITOIRES
>
> T + 33 (0)2 38 28 18 19
> CHÂTEAU DES BARRES, 45290 NOGENT-SUR-VERNISSON
> ign.fr - geoportail.fr
>
> Le 06/08/2021 à 14:03, Alain Benard a écrit :
>
> Bonjour,
>
> Nous sommes en postgres 12 / postgis 3 : POSTGIS="3.0.3 6660953"
> [EXTENSION] PGSQL="120" GEOS="3.9.1-CAPI-1.14.2" PROJ="8.0.1" GDAL="GDAL
> 3.2.3, released 2021/04/27" LIBXML="2.9.7" LIBJSON="0.13.1"
> LIBPROTOBUF="1.3.0" WAGYU="0.4.3 (Internal)" RASTER
>
> Mon souci viens d’une utilisation de la fonction st_value qui permet de
> ramener les informations du pixel d’un raster qui correspond à un point
> (geom). J’aurais pu a poser sur un forum postgis mais le questionnement
> viens au niveau du *planificateur qui refuse d’utiliser un index présent
> sauf si je mets une clause limit* (même avec une très grande valeur) :
>
>
>
> *Requete 1 (sans clause limit) :*
>
> explain
>
> with point_emprise as
>
> (select x,y, ST_SetSRID(st_makepoint(x,y),2154) as geom from
> extract_complet ),
>
> donnee_source as
>
> (select x,y, st_value(rast,geom)as valeur from
> geo_raster_france_90m_clc,point_emprise
>
> where
> st_intersects(rast,geom))
>
> select * from donnee_source
>
>
>
> *Retour planificateur : *
>
> "QUERY PLAN"
>
> "Nested Loop (cost=0.00..161026468635.83 rows=16427665024 width=24)"
>
> " Join Filter: st_intersects(geo_raster_france_90m_clc.rast,
> st_setsrid(st_makepoint(extract_complet.x, extract_complet.y), 2154),
> NULL::integer)"
>
> " -> Seq Scan on geo_raster_france_90m_clc (cost=0.00..2350.83
> rows=49283 width=264)"
>
> " -> Materialize (cost=0.00..38511.00 rows=1000000 width=16)"
>
> " -> Seq Scan on extract_complet (cost=0.00..28628.00
> rows=1000000 width=16)"
>
>
>
> *Requête 2 (la même avec une clause limit) :*
>
> explain
>
> with point_emprise as
>
> (select x,y, ST_SetSRID(st_makepoint(x,y),2154) as geom from
> extract_complet limit 10000000 ),
>
> donnee_source as
>
> (select x,y, st_value(rast,geom)as valeur from
> geo_raster_france_90m_clc,point_emprise
>
> where
> st_intersects(rast,geom))
>
> select * from donnee_source
>
>
>
> *Retour planificateur : *
>
> "QUERY PLAN"
>
> "Nested Loop (cost=0.28..17539324.25 rows=16427665 width=24)"
>
> " -> Limit (cost=0.00..528628.00 rows=1000000 width=48)"
>
> " -> Seq Scan on extract_complet (cost=0.00..528628.00
> rows=1000000 width=48)"
>
> " -> Index Scan using convexhull_idx_foretv2 on
> geo_raster_france_90m_clc (cost=0.28..12.87 rows=2 width=264)"
>
> " Index Cond: ((rast)::geometry &&
> (st_setsrid(st_makepoint(extract_complet.x, extract_complet.y), 2154)))"
>
> " Filter:
> _st_intersects((st_setsrid(st_makepoint(extract_complet.x,
> extract_complet.y), 2154)), rast, NULL::integer)"
>
>
>
> Le raster est tuilé (près de 50 000 tuiles) et son utilisation est donc la
> logique même si on ne veut pas parcourir 50000 plus petit rasters au lieu
> d’un seul (sa non utilisation rend la requête inexploitable).
>
> J’ai bien sûr effectué un vacuum analyze et je ne sais pas quoi faire pour
> que la requête utilise bien cet index.
>
> Précisions :
>
> 1. La table extract_tmp est une table temporaire (j’ai aussi essayé
> d’y passer un vacuum analyze sans succès)
>
> 2. Le raster geo_raster_france_90m_clc proviens d’une restauration
> depuis une base postgis 2.5.5
>
> Merci pour vos réponses /conseils.
>
> [image: INRA]
>
>
>
> *Alain BENARD*
>
> *Administrateur Systèmes d’Information*
>
> *alain(dot)benard(at)inra(dot)fr <alain(dot)benard(at)inra(dot)fr>*
>
> *Unité SILVA*
>
> Tél. : +33 3 83 39 40 84
>
> Route d’amance
>
> 54 280 Champenoux
>
> inra.fr <http://www.inra.fr>
>
>
>
> [image: INRA]
>
>
>
>
>
>
>

--
Guillaume.