I have 2 vector data in my Postgis DB: one is a point layer (punti) with about 200000 elements and the other is a polygon (province) with 4 elements. They cover the same area and I need to find the number of points for each polygon.

This is the query:

province.gid, count(*) AS totale FROM punti, province WHERE 
st_intersects(province.the_geom,punti.the_geom) GROUP BY province.gid;


HashAggregate (cost=35.35..35.36 rows=1 width=4) (actual time=23660.787..23660.789 rows=4 loops=1) -> Nested Loop (cost=0.00..35.34 rows=1 width=4) (actual time=0.307..23532.479 rows=199977 loops=1) Join Filter: _st_intersects(province.the_geom, punti.the_geom) -> Seq Scan on province (cost=0.00..1.04 rows=4 width=36) (actual time=0.003..0.009 rows=4 loops=1) -> Index Scan using punti_the_geom_idx on punti (cost=0.00..8.31 rows=1 width=100) (actual time=0.038..76.254 rows=69541 loops=4) Index Cond: (province.the_geom && punti.the_geom) Total runtime: 23660.850 ms

What do you think about this total runtime?

closed as primarily opinion-based by PolyGeo Feb 12 at 0:05

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • As of PostGIS 1.3, ST_Intersection does implicit bounding box checks to utilize the GIST index. So I'm not sure how to enhance this... – Crischan May 20 '12 at 0:35

I think 23 seconds ain't bad. If you want to make it even smaller, slice your polygons up into a larger collection smaller objects, by intersecting with a grid, for example.

  • Why would a larger collection of smaller polygons be faster? Wouldn't that just mean more intersection operations? Are you implying simpler as well as smaller? – naught101 May 20 '12 at 14:12
  • 3
    Because it will increase the index selectivity. – Nicklas Avén May 20 '12 at 17:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.