I'm trying to improve performance for the query below. No matter how I write the query (subquery in FROM clause, subquery in WHERE clause) postgres insists on running all ~570K rows through the expensive ST_DWITHIN function even though there are only 60 rows where county=24. How can I get postgres to filter on county=24 BEFORE running through the postgis func which seems to me would be much faster and far more efficient? 700ms isnt cause for too much concern but as this table grows to 10M+ I'm concerned about performance.
Also to note, p.id is a primary key, p.zipcode is an fk index, z.county is a fk index, and p.geom has a GiST index.
EXPLAIN ANALYZE SELECT count(p.id) FROM point AS p LEFT JOIN zipcode AS z ON p.zipcode = z.zipcode WHERE z.county = 24 AND ST_DWithin( p.geom, ST_SetSRID(ST_Point(-121.479756008715,38.563236291512),4269), 16090.0, false )
Aggregate (cost=250851.91..250851.92 rows=1 width=4) (actual time=724.007..724.007 rows=1 loops=1) -> Hash Join (cost=152.05..250851.34 rows=228 width=4) (actual time=0.359..723.996 rows=51 loops=1) Hash Cond: ((p.zipcode)::text = (z.zipcode)::text) -> Seq Scan on point p (cost=0.00..250669.12 rows=7437 width=10) (actual time=0.258..723.867 rows=63 loops=1) Filter: (((geom)::geography && '0101000020AD10000063DF8B52B45E5EC070FB752018484340'::geography) AND ('0101000020AD10000063DF8B52B45E5EC070FB752018484340'::geography && _st_expand((geom)::geography, 16090::double precision)) AND _st_dwithin((g (...) Rows Removed by Filter: 557731 -> Hash (cost=151.38..151.38 rows=54 width=6) (actual time=0.095..0.095 rows=54 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 3kB -> Bitmap Heap Scan on zipcode z (cost=4.70..151.38 rows=54 width=6) (actual time=0.023..0.079 rows=54 loops=1) Recheck Cond: (county = 24) Heap Blocks: exact=39 -> Bitmap Index Scan on fki_zipcode_county_foreign_key (cost=0.00..4.68 rows=54 width=0) (actual time=0.016..0.016 rows=54 loops=1) Index Cond: (county = 24) Planning time: 0.504 ms Execution time: 724.064 ms