[postgis-users] points in polygon
Paul Ramsey
pramsey at refractions.net
Fri Mar 31 20:26:49 PST 2006
Because if the plan runs backwards (first spatial, index then
attribute index) it actually performs a full spatial join of *all*
the data in both tables, then throws out everything not inside LA at
the attribute stage. The good news is that we can do a full spatial
join on his tables in only 30 seconds. :) The bad news is, he
doesn't want that. :(
P.
On Mar 31, 2006, at 8:23 PM, Brent Wood wrote:
>
> Hi
>
>
> I'm jumping in late here, but looking at the results of the
> distance query with
> & without the && operator, I was wondering if a spatial index
> exists on BOTH
> geometry fields? If they do, & vacuum analyse was run after the
> indices were
> created can anyone explain why a query with the && to utilise the
> indices is
> actually slower? (32 vs 18 sec)?
>
> The indices listed include: idx_polygon_fips (for the LA id number)
> idx_point_the_geom (I assume a spatial
> index on the
>
> points)
>
> but I can't see a reference to an index on the polygon geometries.
>
> Cheers,
>
> Brent Wood
>
>
>
> --- "Gregory S. Williamson" <gsw at globexplorer.com> wrote:
>
>> What are the changes made to the configuration file (amount of RAM
>> per
>> connection, etc.) ?
>>
>> You might also run vmstat or some such and post the results ... is
>> the
>> machine I/O bound ? Lots of context switches or swapping ? Mostly
>> idle ?
>> (mostly relevant on a *NIX box, but windows can provide some basic
>> monitoring
>> of CPU activity).
>>
>> Greg Williamson
>> DBA
>> GlobeXplorer LLC
>>
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net on behalf of
>> Young Kim
>> Sent: Fri 3/31/2006 4:17 PM
>> To: PostGIS Users Discussion
>> Cc:
>> Subject: Re: [postgis-users] points in polygon
>>
>> 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
>>
>> !DSPAM:442dc6b0258221804284693!
>>
>>
>>
>>
>> _______________________________________________
>> 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