Forums PostgreSQL.fr

ced

Membre

Performances et plans de requêtes spatiales

Bonjour,
Je dispose de deux tables contenant des géométries :
* table inv_exp_nm.e1point : contient des points, la géométrie a pour SRID un code de référence spatial "utilisateur" (c'est du Lambert 93 tenant compte de la grille de reprojection lors du changement de référentiel spatial entre Lambert 2 étendu et Lambert 93). Il s'agit donc du SRID 910001 dans ma table spatial_ref_sys ;
* table contours.contour_utilisateur_geom : contient des polygones, la géométrie a pour SRID le code EPSG du Lambert 93 (qui ne tient pas compte de la grille de repojection). C'est le SRID 2154.
Les géométries des deux tables ont des index spatiaux.
Lorsqu'on croise un polygone issu de la table des contours avec la table des points, la solution qui vient de suite consiste à faire une jointure spatiale entre points et polygones avec ST_INTERSECTS, en reprojetant à la volée le polygone en Lambert 93 "maison" (code SRID 910001).
Voici la requête :

SELECT npp
FROM inv_exp_nm.e1point e1
JOIN contours.contour_utilisateur_geom c ON ST_Intersects(ST_Transform(e1.geom, 2154), c.the_geom) 
WHERE id_contour = '299';

Elle met 5 minutes à s'exécuter... Voici son plan d'exécution:

Nested Loop  (cost=5.08..363.93 rows=285 width=17) (actual time=457.446..309617.455 rows=12824 loops=1)
  Buffers: shared hit=221431
  ->  Index Scan using pk_contour_utilisateur_geom on contour_utilisateur_geom c  (cost=0.14..8.16 rows=1 width=70362) (actual time=0.021..0.029 rows=1 loops=1)
        Index Cond: ((id_contour)::text = '299'::text)
        Buffers: shared hit=2
  ->  Bitmap Heap Scan on e1point e1  (cost=4.94..355.48 rows=29 width=49) (actual time=457.420..309610.839 rows=12824 loops=1)
        Recheck Cond: (geom && st_transform(c.the_geom, 910001))
        Filter: _st_intersects(geom, st_transform(c.the_geom, 910001))
        Rows Removed by Filter: 8892
        Heap Blocks: exact=4074
        Buffers: shared hit=221429
        ->  Bitmap Index Scan on idx_e1point_geom  (cost=0.00..4.93 rows=86 width=0) (actual time=2.678..2.678 rows=21716 loops=1)
              Index Cond: (geom && st_transform(c.the_geom, 910001))
              Buffers: shared hit=173
Planning time: 0.205 ms
Execution time: 309621.410 ms

Si on réécrit la requête en isolant la reprojection du polygone dans une CTE, le temps d'exécution (pour un résultat identique) descend à 250 ms !
Voici la nouvelle requête :

EXPLAIN (ANALYZE, buffers, timing) 
WITH contour AS (SELECT ST_Transform(the_geom, 910001) as geom
    FROM contours.contour_utilisateur_geom
    WHERE   id_contour = '299'
) 
SELECT npp
FROM inv_exp_nm.e1point e1 
INNER JOIN  contour ON (ST_Intersects(contour.geom, e1.geom));

Et son plan d'exécution :

Nested Loop  (cost=13.10..363.52 rows=285 width=17) (actual time=19.643..252.563 rows=12824 loops=1)
  Buffers: shared hit=4265
  CTE contour
    ->  Index Scan using pk_contour_utilisateur_geom on contour_utilisateur_geom  (cost=0.14..8.16 rows=1 width=32) (actual time=14.454..14.456 rows=1 loops=1)
          Index Cond: ((id_contour)::text = '299'::text)
          Buffers: shared hit=18
  ->  CTE Scan on contour  (cost=0.00..0.02 rows=1 width=32) (actual time=14.508..14.511 rows=1 loops=1)
        Buffers: shared hit=18
  ->  Bitmap Heap Scan on e1point e1  (cost=4.94..355.04 rows=29 width=49) (actual time=5.130..235.789 rows=12824 loops=1)
        Recheck Cond: (contour.geom && geom)
        Filter: _st_intersects(contour.geom, geom)
        Rows Removed by Filter: 8892
        Heap Blocks: exact=4074
        Buffers: shared hit=4247
        ->  Bitmap Index Scan on idx_e1point_geom  (cost=0.00..4.93 rows=86 width=0) (actual time=2.683..2.683 rows=21716 loops=1)
              Index Cond: (contour.geom && geom)
              Buffers: shared hit=173
Planning time: 0.311 ms
Execution time: 253.495 ms

Quand je regarde les plans d'exécution, j'interprète la source du problème dans la première requête sur le filtre qui réaliser la reprojection à chaque bitmap heap scan sur la table des points (c'est là que le temps explose). Je me trompe ?
Du coup, comme les deux projections sont du Lambert 93 (et qu'il n'y a pas, dans cette requête du moins, d'autre couche spatiale en jeu avec du Lambert 2 étendu), j'ai mis à jour le SRID de la couche des contours (les polygones) en Lambert 93 "utilisateur" (le code SRID 910001) comme ça :

SELECT updategeometrysrid('contours', 'contour_utilisateur_geom', 'the_geom', 910001);

La première requête spatiale devient alors :

SELECT npp
FROM inv_exp_nm.e1point e1
JOIN contours.contour_utilisateur_geom c ON ST_Intersects(e1.geom, c.the_geom) 
WHERE id_contour = '299';

Elle met 11 secondes à s'exécuter.
Voici le plan d'exécution :

Nested Loop  (cost=5.08..363.49 rows=18830 width=17) (actual time=15.856..7647.523 rows=12824 loops=1)
  Buffers: shared hit=221419
  ->  Index Scan using pk_contour_utilisateur_geom on contour_utilisateur_geom c  (cost=0.14..8.16 rows=1 width=70362) (actual time=0.013..0.014 rows=1 loops=1)
        Index Cond: ((id_contour)::text = '299'::text)
        Buffers: shared hit=2
  ->  Bitmap Heap Scan on e1point e1  (cost=4.94..355.04 rows=29 width=49) (actual time=15.840..7643.820 rows=12824 loops=1)
        Recheck Cond: (geom && c.the_geom)
        Filter: _st_intersects(geom, c.the_geom)
        Rows Removed by Filter: 8892
        Heap Blocks: exact=4074
        Buffers: shared hit=221417
        ->  Bitmap Index Scan on idx_e1point_geom  (cost=0.00..4.93 rows=86 width=0) (actual time=2.637..2.637 rows=21716 loops=1)
              Index Cond: (geom && c.the_geom)
              Buffers: shared hit=173
Planning time: 0.530 ms
Execution time: 7649.239 ms

Là encore, le nœud critique semble être le Bitmap Heap Scan sur la table des points. La différence avec le plan d'exécution de la deuxième requête semble être la taille des buffers lus en mémoire, qui est plus élevée. Est-ce que c'est ça qui augmente le temps d'exécution ? Si oui, pourquoi cette taille est supérieure avec cette dernière requête ?
Merci d'avance pour votre aide,
Cédric

gleu

Administrateur

Re : Performances et plans de requêtes spatiales

À mon sens, l'exécution est plus lente sur la première requête parce que le ST_Transform sur e1.geom sera calculé pour chaque ligne de contour_utilisateur_geom à chaque tentative de jointure avec contours.contour_utilisateur_geom (ie, un produit cartésien). Dans la seconde requête, la CTE précalcule une seule fois le ST_Transform, ce qui permet de gagner en exécution. Enfin, il est possible que la durée d'exécution de la troisième requête soit due au plus grand nombre de blocs lus.

---

Guillaume.

ced

Membre

Re : Performances et plans de requêtes spatiales

Bonjour Guillaume,

Merci pour ta réponse.
Est-ce que ça peut être la CTE et la fonction ST_Transform qui entraînent un si petit nombre de blocs lus dans la deuxième requête par rapport à la troisième ?

gleu

Administrateur

Re : Performances et plans de requêtes spatiales

Ça peut en effet être la CTE en précalculant et en "stockant" ce résultat intermédiaire.

---

Guillaume.

ced

Membre

Re : Performances et plans de requêtes spatiales

OK. Merci pour ton aide.