I have a table of points called features with 57k points
A table of small polygons called lsoa with 36k polygons
A table of large polygons called la with 316 polygons covering same area as lsoa
All three tables have a GIST spatial index
This query is relatively quick:-
SELECT count(feature_id) FROM features INNER JOIN LSOA ON ST_CONTAINS(lsoa_geom, feature_geom)
EXPLAIN tells me that the query planner is doing a seq_scan down the LSOA table and using the spatial index on LSOA as a filter
This query is REALLY slow
SELECT count(feature_id) FROM features INNER JOIN LA ON ST_CONTAINS(la_geom, feature_geom)
EXPLAIN shows a seq_scan down the features table using the LA spatial index as a filter
So both queries are using the indexes, but the some 100 times slower than the first.
I am not sure why the larger geometries make a difference?
My question is should I be using a different operator to ST_Contains for the 2nd query, or taking a different approach?
Here is the explain output for the LA query
Aggregate (cost=31558.48..31558.49 rows=1 width=8) -> Nested Loop (cost=0.14..31542.19 rows=6515 width=8) -> Seq Scan on features f (cost=0.00..9744.33 rows=51433 width=40) -> Index Scan using sidx_local_authority_boundary_geom on local_authority_boundary la (cost=0.14..0.41 rows=1 width=80659) Index Cond: (st_transform(wkb_geometry, 4326) ~ f.wkb_geometry) Filter: _st_contains(st_transform(wkb_geometry, 4326), f.wkb_geometry)
(Note well the index has been created on a transformed geometry as the types differ between features and the other table)