I am trying to select all segments (among millions of them) that are less than X metres from a few thousands polygons obtained from a raster dataset (meaning they've got quite a complex geometry - many vertices) and less than X metres from another set of thousands polygons (less complex geometry). I am attempting to do so using ST_DWithin and a double LEFT JOIN, but it's proving to be very slow (more than 48 hours running and it hasn't finished yet).

Projected Coordinate System (British National Grid)

All 3 tables have a spatial index

This is the query I'm trying with:

CREATE TABLE result AS
SELECT t1.segments_id , t2.polyg1_id , t3.polyg2_id
FROM segments t1
LEFT JOIN polyg1 t2
ON ST_DWithin(t1.geom, t2.geom, X)
LEFT JOIN polyg2 t3
ON ST_DWithin(t1.geom, t3.geom, X)

Once this query is finished I would just need to discard all records from result that have no polyg1_id or polyg2_id values.

Is there any better (more efficient) ways to achieve what I'm trying to obtain?

I am trying to select all segments (among millions of them) that are less than X metres from a few thousands polygons obtained from a raster dataset (meaning they've got quite a complex geometry - many vertices) OR less than X metres from another set of thousands polygons (less complex geometry). I am attempting to do so using ST_DWithin and a double LEFT JOIN, but it's proving to be very slow (more than 48 hours running and it hasn't finished yet).

Projected Coordinate System (British National Grid)

All 3 tables have a spatial index

This is the query I'm trying with:

CREATE TABLE result AS
SELECT t1.segments_id , t2.polyg1_id , t3.polyg2_id
FROM segments t1
LEFT JOIN polyg1 t2
ON ST_DWithin(t1.geom, t2.geom, X)
LEFT JOIN polyg2 t3
ON ST_DWithin(t1.geom, t3.geom, X)

Once this query is finished I would just need to discard all records from result that have no polyg1_id or polyg2_id values.

Is there any better (more efficient) ways to achieve what I'm trying to obtain?