[postgis-users] points in polygon

[Paul Ramsey]

You are right, 8000 is not a boatload, I did not know that was the  
number.  Is it just me (I am terrible at reading explain analyze  
results) but is the spatial index condition being evaluated *before*  
the FIPS attribute condition?

What is your PostGIS/PgSQL version?  Have you run VACUUM ANALYZE on  
this database?

P

On 31-Mar-06, at 4:00 PM, Young Kim wrote:

> here are explain outputs.
>
> 1st line is "aggregate" because i'm just counting number of rows.
>
> 8K points seem nothing to me.
> People don't do spatial analysis on 8K+ points?
>
> -------------------------------------
>
> WHERE (polygon.fips = '0644000') AND point.the_geom &&
> polygon.the_geom AND distance(polygon.the_geom, point.the_geom) <
> 0.00000001;
>
> Aggregate  (cost=9.09..9.10 rows=1 width=4) (actual
> time=32617.877..32617.877 rows=1 loops=1)
>   ->  Nested Loop  (cost=0.00..9.04 rows=17 width=4) (actual
> time=960.179..32615.023 rows=3248 loops=1)
>         Join Filter: (distance("outer".the_geom, "inner".the_geom) <
> 1e-08::double precision)
>         ->  Index Scan using idx_polygon_fips on polygon
> (cost=0.00..3.01 rows=1 width=9414) (actual time=0.012..0.015 rows=1
> loops=1)
>               Index Cond: ((fips)::text = '0644000'::text)
>         ->  Index Scan using idx_point_the_geom on point
> (cost=0.00..6.01 rows=1 width=25) (actual time=62.287..17938.112
> rows=7050 loops=1)
>               Index Cond: (point.the_geom && "outer".the_geom)
>               Filter: (point.the_geom && "outer".the_geom)
> Total runtime: 32617.945 ms
>
> -------------------------------------
>
> WHERE (polygon.fips = '0644000') AND distance(polygon.the_geom,
> point.the_geom) < 0.00000001;
>
> Aggregate  (cost=1060.81..1060.82 rows=1 width=4) (actual
> time=18605.436..18605.437 rows=1 loops=1)
>   ->  Nested Loop  (cost=0.00..1053.59 rows=2888 width=4) (actual
> time=10.252..18602.620 rows=3248 loops=1)
>         Join Filter: (distance("outer".the_geom, "inner".the_geom) <
> 1e-08::double precision)
>         ->  Index Scan using idx_polygon_fips on polygon
> (cost=0.00..3.01 rows=1 width=9414) (actual time=0.016..0.020 rows=1
> loops=1)
>               Index Cond: ((fips)::text = '0644000'::text)
>         ->  Seq Scan on point  (cost=0.00..920.63 rows=8663 width=25)
> (actual time=3.068..26.086 rows=8663 loops=1)
> Total runtime: 18605.496 ms
>
> -------------------------------------
>
> WHERE (polygon.fips = '0644000') AND point.the_geom &&
> polygon.the_geom AND CONTAINS(polygon.the_geom, point.the_geom);
>
> Aggregate  (cost=9.09..9.10 rows=1 width=4) (actual
> time=164835.027..164835.027 rows=1 loops=1)
>   ->  Nested Loop  (cost=0.00..9.04 rows=17 width=4) (actual
> time=4182.576..164829.831 rows=3248 loops=1)
>         Join Filter: contains("outer".the_geom, "inner".the_geom)
>         ->  Index Scan using idx_polygon_fips on polygon
> (cost=0.00..3.01 rows=1 width=9414) (actual time=0.016..0.020 rows=1
> loops=1)
>               Index Cond: ((fips)::text = '0644000'::text)
>         ->  Index Scan using idx_point_the_geom on point
> (cost=0.00..6.01 rows=1 width=25) (actual time=62.540..18306.731
> rows=7050 loops=1)
>               Index Cond: (point.the_geom && "outer".the_geom)
>               Filter: (point.the_geom && "outer".the_geom)
> Total runtime: 164835.099 ms
>
> -------------------------------------
>
> WHERE (polygon.fips = '0644000') AND CONTAINS(polygon.the_geom,  
> point.the_geom);
>
> Aggregate  (cost=1039.15..1039.16 rows=1 width=4) (actual
> time=146143.486..146143.487 rows=1 loops=1)
>   ->  Nested Loop  (cost=0.00..1031.93 rows=2888 width=4) (actual
> time=64.431..146139.232 rows=3248 loops=1)
>         Join Filter: contains("outer".the_geom, "inner".the_geom)
>         ->  Index Scan using idx_polygon_fips on polygon
> (cost=0.00..3.01 rows=1 width=9414) (actual time=0.016..0.021 rows=1
> loops=1)
>               Index Cond: ((fips)::text = '0644000'::text)
>         ->  Seq Scan on point  (cost=0.00..920.63 rows=8663 width=25)
> (actual time=0.283..38.307 rows=8663 loops=1)
> Total runtime: 146143.551 ms
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users