I have this query, which is very slow (+50 seconds):
SELECT e.gid, e.geom, osm_id, name
FROM roads_vlaanderen_edges_2021_2022 e
inner join(select st_buffer(geom,30) geom from projecten where gid=126) p
on st_contains(p.geom, e.geom);
EXPLAIN ANALYZE gives this:
"Nested Loop (cost=85.60..86580.42 rows=1620 width=126) (actual time=26.775..49821.592 rows=3176 loops=1)"
" -> Seq Scan on projecten (cost=0.00..1.30 rows=1 width=58909) (actual time=0.007..0.013 rows=1 loops=1)"
" Filter: (gid = 126)"
" Rows Removed by Filter: 30"
" -> Bitmap Heap Scan on roads_vlaanderen_edges_2021_2022 e (cost=85.60..86577.50 rows=162 width=126) (actual time=26.763..49814.758 rows=3176 loops=1)"
" Filter: st_contains(st_buffer(projecten.geom, '30'::double precision, ''::text), geom)"
" Rows Removed by Filter: 7436"
" Heap Blocks: exact=3548"
" -> Bitmap Index Scan on roads_vlaanderen_geom_2021_2022 (cost=0.00..85.56 rows=1620 width=0) (actual time=1.248..1.249 rows=10612 loops=1)"
" Index Cond: (geom @ st_buffer(projecten.geom, '30'::double precision, ''::text))"
"Planning Time: 0.413 ms"
"Execution Time: 49824.095 ms"
Without the buffer the query is very fast (<1 second)
SELECT e.gid, e.geom, osm_id, name
FROM roads_vlaanderen_edges_2021_2022 e
inner join(select geom from projecten where gid=126) p
on st_contains(p.geom, e.geom);
The subquery itself is also very fast (<1 second)
select st_buffer(geom,30) geom from projecten where gid=126
So why is my first query that slow? (My tables are spatially indexed, and in a coordinate system in meter.)
ChatGPT came up with this solution, and it worked (<1 second), but I have no idea why this is different.
create temporary table project_buffer as
select st_buffer(geom,30) geom from projecten where gid=126;
SELECT e.gid, e.geom, osm_id, name
FROM roads_vlaanderen_edges_2021_2022 e
inner join project_buffer p
on st_contains(p.geom, e.geom);
DROP TABLE project_buffer;
I do regular queries like this, and never encountered this problem.