PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 05/10/2021 17:57:38

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"

query-1.png


=> 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

query-2.png


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

Merci!

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

Hors ligne

#2 05/10/2021 18:37:13

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.

Hors ligne

#3 05/10/2021 21:01:40

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 ?

Hors ligne

#4 05/10/2021 22:14:36

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.

Hors ligne

Pied de page des forums