Forums PostgreSQL.fr

andarius

Membre

Optimisation filtre géo distance avec PostGIS

Optimisation filtre géo distance avec PostGIS

Bonjour!
Je suis en train d'essayer d'optimiser une requête PostGIS et j'aurais voulu avoir quelques conseils/indications sur comment améliorer encore les performances.
Le but est de filtrer une table avec environ 800k items, afin d'obtenir les éléments à moins de X km d'un point (tous en France), et de calculer leur distance par rapport à ce point.

La table ressemble à ça:

CREATE TABLE IF NOT EXISTS general.worker
(
    id        varchar(20) PRIMARY KEY,
    location  point
    ...
);

Dans ce cas d'exemple, j'essaie de récupérer tous les éléments à moins de 5km d'un point donné.

=> 1ère requête:

    select 
    	id,
    	ST_Distance(location::geometry, ST_Point(2.34, 48.85)) as distance
	from general.worker
	where ST_DWithin(
		ST_Transform(ST_SetSRID(location::geometry, 4326), 2154),
		ST_Transform(ST_SetSRID(ST_Point(2.34, 48.85), 4326), 2154),
		5000) is True

Ce qui me donne l'EXPLAIN suivant

"Gather  (cost=1000.00..10204378.61 rows=88 width=8) (actual time=199.272..952.684 rows=32183 loops=1)"
"  Output: (st_distance((location)::geometry, '0101000000B81E85EB51B80240CDCCCCCCCC6C4840'::geometry))"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  JIT for worker 0:"
"    Functions: 4"
"    Options: Inlining true, Optimization true, Expressions true, Deforming true"
"    Timing: Generation 1.469 ms, Inlining 155.731 ms, Optimization 110.002 ms, Emission 67.503 ms, Total 334.705 ms"
"  JIT for worker 1:"
"    Functions: 4"
"    Options: Inlining true, Optimization true, Expressions true, Deforming true"
"    Timing: Generation 1.506 ms, Inlining 143.655 ms, Optimization 109.081 ms, Emission 49.523 ms, Total 303.766 ms"
"  Buffers: shared hit=791 read=13298"
"  ->  Parallel Seq Scan on general.worker  (cost=0.00..10203369.81 rows=37 width=8) (actual time=317.516..861.207 rows=10728 loops=3)"
"        Output: st_distance((location)::geometry, '0101000000B81E85EB51B80240CDCCCCCCCC6C4840'::geometry)"
"        Filter: (st_dwithin(st_transform(st_setsrid((worker.location)::geometry, 4326), 2154), '01010000206A08000021C9F8845FE223416E6108347F2C5A41'::geometry, '5000'::double precision) IS TRUE)"
"        Rows Removed by Filter: 283335"
"        Buffers: shared hit=791 read=13298"
"        Worker 0: actual time=415.216..832.513 rows=8414 loops=1"
"          Buffers: shared hit=385 read=3708"
"        Worker 1: actual time=338.313..828.633 rows=9486 loops=1"
"          Buffers: shared hit=352 read=3946"
"Planning Time: 19.797 ms"
"JIT:"
"  Functions: 12"
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 4.590 ms, Inlining 320.388 ms, Optimization 330.759 ms, Emission 164.167 ms, Total 819.903 ms"
"Execution Time: 956.960 ms"

=> 2e requête:

Pour optimiser j'ai tenté de créer une materialized view avec des indexes sur la location et de CLUSTER le tout.

CREATE MATERIALIZED VIEW general.worker_geo AS
(
 select
    id,
    location,
    ST_Transform(ST_SetSRID(location::geometry, 4326), 2154) as location_geom
    from general.worker
 );

CREATE INDEX worker_geo_location_geom_index
    ON general.worker_geo USING GIST(location_geom);

CREATE UNIQUE INDEX worker_geo_worker_id_index
    ON general.worker_geo (id);

CLUSTER general.worker_geo USING worker_geo_location_geom_index;

Pour ensuite faire

select 
ST_Distance(location_geom, ST_Transform(ST_SetSRID(ST_Point(2.34, 48.85), 4326), 2154))
from general.worker_geo w
where ST_DWithin(
	location_geom,
	ST_Transform(ST_SetSRID(ST_Point(2.34, 48.85), 4326), 2154),
	5000) is True

Ce qui me donne l'EXPLAIN suivant

Gather  (cost=1000.00..8740426.28 rows=84 width=8) (actual time=153.547..505.123 rows=29478 loops=1)
"  Output: (st_distance(location_geom, '01010000206A08000021C9F8845FE223416E6108347F2C5A41'::geometry))"
  Workers Planned: 2
  Workers Launched: 2
  JIT for worker 0:
    Functions: 4
"    Options: Inlining true, Optimization true, Expressions true, Deforming true"
"    Timing: Generation 1.156 ms, Inlining 118.552 ms, Optimization 94.700 ms, Emission 43.580 ms, Total 257.987 ms"
  JIT for worker 1:
    Functions: 4
"    Options: Inlining true, Optimization true, Expressions true, Deforming true"
"    Timing: Generation 1.289 ms, Inlining 117.147 ms, Optimization 90.090 ms, Emission 42.265 ms, Total 250.791 ms"
  Buffers: shared read=9115 written=1
  ->  Parallel Seq Scan on general.worker_geo w  (cost=0.00..8739417.88 rows=35 width=8) (actual time=220.390..403.979 rows=9826 loops=3)
"        Output: st_distance(location_geom, '01010000206A08000021C9F8845FE223416E6108347F2C5A41'::geometry)"
"        Filter: (st_dwithin(w.location_geom, '01010000206A08000021C9F8845FE223416E6108347F2C5A41'::geometry, '5000'::double precision) IS TRUE)"
        Rows Removed by Filter: 269404
        Buffers: shared read=9115 written=1
        Worker 0: actual time=257.416..364.960 rows=8246 loops=1
          Buffers: shared read=2163
        Worker 1: actual time=250.417..363.704 rows=8226 loops=1
          Buffers: shared read=2272
Planning Time: 40.821 ms
JIT:
  Functions: 12
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 3.848 ms, Inlining 248.722 ms, Optimization 269.845 ms, Emission 140.212 ms, Total 662.626 ms"
Execution Time: 508.428 ms

Est-il possible d'améliorer encore les performances ?

Merci!

Dernière modification par andarius (05/10/2021 18:09:59)

gleu

Administrateur

Re : Optimisation filtre géo distance avec PostGIS

Ce qui vous coûte sur la première requête, c'est l'exécution de JIT. Il prend 819,9 ms pour une requête de 956,9 ms. Désactivez-le (jit=off) et ça devrait déjà aller mieux.

Et du coup, oubliez la vue matérialisée.

---

Guillaume.

andarius

Membre

Re : Optimisation filtre géo distance avec PostGIS

Effectivement le jit=off améliore beaucoup les performances merci.

Dans quel contexte la vue matérialisée aurait-elle eu du sens ?

gleu

Administrateur

Re : Optimisation filtre géo distance avec PostGIS

Une vue matérialisée a un sens quand on veut précalculer certaines données, sans avoir besoin de les rafraichir fréquemment.

---

Guillaume.