[postgis-users] points in polygon
Paul Ramsey
pramsey at refractions.net
Fri Mar 31 20:24:41 PST 2006
Yeah, reading the explain analyze output with a couple beers in me,
it seems clear... you have a bad plan there. It's trying to do the
spatial join before restricting one half of the situation using your
fips code. And it is the result of bad estimates...
>>> 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)
For the attribute fips side, a slight over-estimate...
>>> -> 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)
For the spatial join, a big under-estimate (expect 0-6, get 62).
It is possible that the planner will just re-write this back into the
wrong order, but try forcing the order to be the one we know is more
selective:
SELECT point.id
FROM point, (select the_geom from polygon where fips = '0644000')
as pgon
WHERE
point.the_geom && pgon.the_geom
AND intersects(pgon.the_geom, point.the_geom);
On Mar 31, 2006, at 4:17 PM, Young Kim wrote:
> machine setup:
> fast dual xeon processors
> 2 gig ram
> slackware 10.2
> postgresql 8.1.3
> postgis 1.1.1
>
> testing machine. nothing else is running.
>
> VACUUM ANALYZE - most definitely.
>
>
> On 3/31/06, Paul Ramsey <pramsey at refractions.net> wrote:
>> 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
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list