Requête SAMER
EXPLAIN ANALYZE SELECT a.id AS id_a, b.id AS id_b
FROM habit a
JOIN habit b ON ST_Intersects(a.geom, b.geom)
WHERE a.id <> b.id
LIMIT 100;
Sans index
"Limit (cost=0.00..17143.87 rows=61 width=48) (actual time=1.783..51.746 rows=86 loops=1)"
" -> Nested Loop (cost=0.00..17143.87 rows=61 width=48) (actual time=1.780..51.672 rows=86 loops=1)"
" Join Filter: (((a.id)::text <> (b.id)::text) AND st_intersects(a.geom, b.geom))"
" Rows Removed by Join Filter: 1283"
" -> Seq Scan on habit a (cost=0.00..5.37 rows=37 width=1216) (actual time=0.021..0.099 rows=37 loops=1)"
" -> Materialize (cost=0.00..5.55 rows=37 width=1216) (actual time=0.001..0.016 rows=37 loops=37)"
" -> Seq Scan on habit b (cost=0.00..5.37 rows=37 width=1216) (actual time=0.010..0.074 rows=37 loops=1)"
"Planning Time: 0.614 ms"
"Execution Time: 51.959 ms"
Avec index
CREATE INDEX geo_index ON habit USING GIST (geom);
"Limit (cost=0.14..497.98 rows=61 width=48) (actual time=1.121..25.407 rows=86 loops=1)"
" -> Nested Loop (cost=0.14..497.98 rows=61 width=48) (actual time=1.118..25.359 rows=86 loops=1)"
" -> Seq Scan on habit a (cost=0.00..5.37 rows=37 width=1216) (actual time=0.014..0.052 rows=37 loops=1)"
" -> Index Scan using geo_index on habit b (cost=0.14..13.30 rows=1 width=1216) (actual time=0.491..0.671 rows=2 loops=37)"
" Index Cond: (geom && a.geom)"
" Filter: (((a.id)::text <> (id)::text) AND st_intersects(a.geom, geom))"
" Rows Removed by Filter: 1"
"Planning Time: 1.704 ms"
"Execution Time: 25.719 ms"