[postgis-users] I've got a silly question about performance

[Gregory Williamson]

The GIST index is the one you want / need for spatial operations. Make sure that you ANALYZE <tablename>; after creating the index or changing the data much (rule of thumb is maybe 10% but analyze is a fast and fairly low impact operation).

The ST_ functions usually (always?) invoke the && operator (which is the spatial operator which will use the GIST indexes); they are wrapper for the underlying functions.

Posting your information [type of hardware, OS, postgreSQL and postGIS versions, table structure and indexes, tables sizes, the query itself and the results of EXPLAIN ANALYZE <query>] to the PostgreSQL performance mail list might be worthwhile if this list doesn't help enough.

Greg Williamson
Senior DBA
Globexplorer LLC, a company owned by DigitalGlobe


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of easpengren
Sent: Thu 1/17/2008 12:02 AM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] I've got a silly question about performance
 

OK, I think I've got that. When I imported these shape files into the
database, an index was created. I added a Gist index to both to see what
would happen. Will that cause a problem?

I do not understand Indices at all. I'll have to read up on them.

We'll see how these things come out. I would like to have something usable
in the next day or two.

I'll likely end up creating a view of this query. Again, I'm still figuring
this out. Speed is really quite important for me. I don't have the most
powerful machine doing this stuff (more RAM will likely show up this week).
What I'd like to do is make sure the software is working as efficiently as I
can get it.

As I type this, I seem to have gotten a much quicker response from the
server. 

Wow.

Eric

Brent Wood-2 wrote:
> 
> 
> --- easpengren <phat-ass at thinkheavyindustries.com> wrote:
> 
>> 
>> I'm still getting the hang of some of the finer points of creating
>> queries in
>> PostGIS, as is probably obvious with my last post.
>> 
>> I've two tables, parcel2 that is a collection of parcels in a county and
>> a
>> table election, which is a table of voting precincts in the same county.
>> I'd
>> like to select all of the parcels in each precinct.
>> 
> 
> That query looks OK, but if parcels can be split across precincts, then it
> won't necessarily give the correct answer.
>  
>> I have this query:
>> 
>> select precinct, sit_st_num, sit_st_dir, sit_st_nam, sit_st_typ,
>> city_code
>> from election, parcel2 where ST_contains(election.the_geom,
>> ST_pointonsurface(parcel2.wkb_geometry));
>> 
>> This gets the job done, but it's very slow. What can I do to speed this
>> up?
> 
> See the PostGIS docs about creating spatial indices on the geometry
> columns in
> your two tables.
> 
> http://postgis.refractions.net/docs/ch04.html#id2761842
> http://postgis.refractions.net/docs/ch04.html#id2761985
> 
> If you have, or if you create them, then you need to modify your query to
> use
> them:
> 
> .... where election.the_geom && parcel2.wkb_geometry and ST_contains ...
> 
> as described in:
> http://postgis.refractions.net/docs/ch04.html#id2762121
> 
> 
> 
> Hope this helps...
> 
>   Brent Wood
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

-- 
View this message in context: http://www.nabble.com/I%27ve-got-a-silly-question-about-performance-tp14910258p14913339.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080117/44247453/attachment.html>