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)

  • 1
    Your query is projecting on the fly. This will always slow query performance. – Vince May 24 '18 at 12:57
  • 1
    Yes, changing the columns to have same geometry type has significantly increased speed. Please add as answer and I will accept – CitizenFish May 24 '18 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.