combining st_contains with st_buffer gives very slow query

Question

I have a layer with roads (4000 features) and a layer with routes (80 features). I want to select every road located on a route, combined with the route-type.

Normaly I would use select road.geom, road.id, route.type from road, route where st_contains(route.geom, road.geom).

Because the routes are not properly digitized I need to buffer the routes to select all roads contained by a route. So when I do select road.geom, road.id, route.type from road, route where st_contains(st_buffer(route.geom, 3), road.geom) the query keeps running for a long time without result.

How can I make this query running faster?

Answer 1

Ensuring the geom is spatially indexed in the road and route tables will be essential to the speed of this query.

Assuming this is already in place I would recommend trying ST_DWithin which 'Returns true if the geometries are within the specified distance of one another.'

ST_DWithin(route.geom, road.geom, 3);

To ensure only geometries that are fully within the specified distance, ST_DFullyWithin can be used instead.

ST_DFullyWithin(route.geom, road.geom, 3);