La comparaison features/limites administratives est trop lente

[flacombe]

Il y a visiblement un problème avec la comparaison des géométries des objets d'un projet avec celle des limites administratives.

Plus précisément, lors de l'initialisation d'un projet ayant déjà plusieurs centaines de milliers de changements, comparer ces centaines de milliers de lignes avec 40k géométries non simplifiées prend trop de temps.

J'essaie de tester des astuces pour accélérer tout ça, mais pas évident : quelle que soit la requête, dès qu'il y a le ST_Intersect(pdm_boundary), la requête ne répond pas.

Une piste est de faire la comparaison sur des enveloppes des limites administratives Comparaison :

explain INSERT INTO pdm_features_boundary SELECT fb.project,fb.osmid,fb.geom,b.osm_id AS boundary,fb.start_ts,fb.end_ts
  FROM pdm_features_boundary fb
  JOIN pdm_boundary b ON ST_Intersects(fb.geom, b.geom)
  WHERE fb.project='2021-01_poteaux' AND fb.boundary IS NULL;

----------------------------------------------------------------------------------------------------------------------
 Insert on pdm_features_boundary  (cost=62.60..163.39 rows=53 width=120)
   ->  Nested Loop  (cost=62.60..163.39 rows=53 width=120)
         ->  Bitmap Heap Scan on pdm_features_boundary fb  (cost=62.32..77.96 rows=4 width=112)
               Recheck Cond: ((boundary IS NULL) AND ((project)::text = '2021-01_poteaux'::text))
               ->  BitmapAnd  (cost=62.32..62.32 rows=4 width=0)
                     ->  Bitmap Index Scan on pdm_features_boundary_boundary_idx  (cost=0.00..27.04 rows=882 width=0)
                           Index Cond: (boundary IS NULL)
                     ->  Bitmap Index Scan on pdm_features_boundary_project_idx  (cost=0.00..35.04 rows=882 width=0)
                           Index Cond: ((project)::text = '2021-01_poteaux'::text)
         ->  Index Scan using pdm_boundary_geom_idx on pdm_boundary b  (cost=0.28..21.35 rows=1 width=40)
               Index Cond: (fb.geom && geom)
               Filter: _st_intersects(fb.geom, geom)
(12 lignes)

Maintenant créons une table d'enveloppes

create table pdm_boundary_envelop AS select id, osm_id, type, name, admin_level, tags, ST_Envelope(geom) AS geom from pdm_boundary;
SELECT 40098
create index on pdm_boundary_envelop using gist(geom);
create index on pdm_boundary_envelop using btree(osm_id);

Le explain change de tête, la requête ne prend plus que 3 minutes pour 200k features et 40k limites administratives

--------------------------------------------------------------------------------------------------------------------------------------------------
 Insert on pdm_features_boundary  (cost=4.73..368.80 rows=134 width=120)
   ->  Nested Loop  (cost=4.73..368.80 rows=134 width=120)
         ->  Index Scan using pdm_features_boundary_project_osmid_boundary_key on pdm_features_boundary fb  (cost=0.42..160.17 rows=10 width=112)
               Index Cond: (((project)::text = '2021-01_poteaux'::text) AND (boundary IS NULL))
         ->  Bitmap Heap Scan on pdm_boundary_envelop b  (cost=4.31..20.85 rows=1 width=40)
               Recheck Cond: (fb.geom && geom)
               Filter: _st_intersects(fb.geom, geom)
               ->  Bitmap Index Scan on pdm_boundary_envelop_geom_idx  (cost=0.00..4.31 rows=4 width=0)
                     Index Cond: (fb.geom && geom)
(9 lignes)

Biensur les enveloppes ne sont pas la solution : on est pas aux USA

[PanierAvide]

Il y a plusieurs choses à tester progressivement :

1. Comme recommandé par plusieurs personnes sur Telegram, vérifier que la configuration du PostgreSQL est optimale avec PGTune https://pgtune.leopard.in.ua/ (bien relancer pour prise en compte)
2. Vérifier que les colonnes de géométries ont des index à jour (REINDEX)
3. Utiliser l'opérateur && en complément du ST_Intersects : JOIN pdm_boundary b ON fb.geom && b.geom AND ST_Intersects(fb.geom, b.geom)
4. Si ce n'est pas suffisant, changer la requête pour utiliser ST_Subdivide : https://www.stevencanplan.com/2017/12/the-genius-of-using-st_subdivide-to-speed-up-postgis-intersection-comparisons/
5. Si ce n'est pas encore suffisant, simplifier les géométries des limites administratives avec postgis_topology : https://trac.osgeo.org/postgis/wiki/UsersWikiSimplifyWithTopologyExt

[flacombe]

Merci, voici les résultats obtenus :

1. Fait
2. Fait
3. En fait ce n'est pas nécessaire : ST_Intersects est un alias de a && b AND _st_intersects(a, b) (pas observé d'améliorations non plus)
4. Ca par contre c'est génial : on passe de 20 minutes à 1 minute avec un maxVertices=25
5. pg_topology étant de la sorcellerie, je vais éviter d'y toucher ;)

Un test avec un maxVertices=512 donne un temps de calcul à 47s Donc je vais partir là dessus, ca me semble suffisant (j'attends d'avoir fini les modifs complètes pour fermer ces issues avec la MR)