[postgis-users] points in polygon
Brent Wood
pcreso at pcreso.com
Fri Mar 31 20:23:56 PST 2006
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
>
More information about the postgis-users
mailing list