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

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.
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.
[INRA]

Alain BENARD

Administrateur Systèmes d’Information

alain(dot)benard(at)inra(dot)fr<mailto: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>

[INRA]