Count points inside polygons using PostGIS

Question

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;

This is the EXPLAIN ANALYZE

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? Thx!

Answer 1

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.