[postgis-users] optimize: count up points near a point

[Josh Livni]

I checked a few threads on this topic, but didn't come across anything 
that helped me make my query a lot quicker.  So, I post here asking for 
your advice:

I have about a million parcels, some of which are missing an attribute. 
  I'd like to query parcels nearby (say within 1km) to get some stats on 
their value for the attribute.

Right now it takes about 40-60 seconds to report on the nearby parcels - 
since over 100k parcels are missing the attribute, this is a problem...

---BEGIN QUERY---
SELECT count(*) as count,
   avg(asr_impr) as avg,
   sum(asr_impr) as sum,
   max(asr_impr) as max,
   min(asr_impr) as min
FROM parcels as p
WHERE attribute > 0 and other_attribute = 11
  AND (expand(p.the_geom,3280.8) && setsrid('POINT(6296272.3 
1955364.5)'::geometry,102646))
  AND distance(centroid(p.the_geom), setsrid('POINT(6296272.3 
1955364.5)'::geometry,102646)) < 3280.8
---END QUERY---

I have a btree index on the attribute and other_attribute columns, and a 
gist index on the parcels centroid(the_geom) column.



Any help is greatly appreciated,

Thanks,

   -Josh