<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] I've got a silly question about performance</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>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).<BR>
<BR>
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.<BR>
<BR>
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.<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
Globexplorer LLC, a company owned by DigitalGlobe<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of easpengren<BR>
Sent: Thu 1/17/2008 12:02 AM<BR>
To: postgis-users@postgis.refractions.net<BR>
Subject: Re: [postgis-users] I've got a silly question about performance<BR>
<BR>
<BR>
OK, I think I've got that. When I imported these shape files into the<BR>
database, an index was created. I added a Gist index to both to see what<BR>
would happen. Will that cause a problem?<BR>
<BR>
I do not understand Indices at all. I'll have to read up on them.<BR>
<BR>
We'll see how these things come out. I would like to have something usable<BR>
in the next day or two.<BR>
<BR>
I'll likely end up creating a view of this query. Again, I'm still figuring<BR>
this out. Speed is really quite important for me. I don't have the most<BR>
powerful machine doing this stuff (more RAM will likely show up this week).<BR>
What I'd like to do is make sure the software is working as efficiently as I<BR>
can get it.<BR>
<BR>
As I type this, I seem to have gotten a much quicker response from the<BR>
server.<BR>
<BR>
Wow.<BR>
<BR>
Eric<BR>
<BR>
Brent Wood-2 wrote:<BR>
><BR>
><BR>
> --- easpengren <phat-ass@thinkheavyindustries.com> wrote:<BR>
><BR>
>><BR>
>> I'm still getting the hang of some of the finer points of creating<BR>
>> queries in<BR>
>> PostGIS, as is probably obvious with my last post.<BR>
>><BR>
>> I've two tables, parcel2 that is a collection of parcels in a county and<BR>
>> a<BR>
>> table election, which is a table of voting precincts in the same county.<BR>
>> I'd<BR>
>> like to select all of the parcels in each precinct.<BR>
>><BR>
><BR>
> That query looks OK, but if parcels can be split across precincts, then it<BR>
> won't necessarily give the correct answer.<BR>
> <BR>
>> I have this query:<BR>
>><BR>
>> select precinct, sit_st_num, sit_st_dir, sit_st_nam, sit_st_typ,<BR>
>> city_code<BR>
>> from election, parcel2 where ST_contains(election.the_geom,<BR>
>> ST_pointonsurface(parcel2.wkb_geometry));<BR>
>><BR>
>> This gets the job done, but it's very slow. What can I do to speed this<BR>
>> up?<BR>
><BR>
> See the PostGIS docs about creating spatial indices on the geometry<BR>
> columns in<BR>
> your two tables.<BR>
><BR>
> <A HREF="http://postgis.refractions.net/docs/ch04.html#id2761842">http://postgis.refractions.net/docs/ch04.html#id2761842</A><BR>
> <A HREF="http://postgis.refractions.net/docs/ch04.html#id2761985">http://postgis.refractions.net/docs/ch04.html#id2761985</A><BR>
><BR>
> If you have, or if you create them, then you need to modify your query to<BR>
> use<BR>
> them:<BR>
><BR>
> .... where election.the_geom && parcel2.wkb_geometry and ST_contains ...<BR>
><BR>
> as described in:<BR>
> <A HREF="http://postgis.refractions.net/docs/ch04.html#id2762121">http://postgis.refractions.net/docs/ch04.html#id2762121</A><BR>
><BR>
><BR>
><BR>
> Hope this helps...<BR>
><BR>
> Brent Wood<BR>
> _______________________________________________<BR>
> postgis-users mailing list<BR>
> postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
><BR>
><BR>
<BR>
--<BR>
View this message in context: <A HREF="http://www.nabble.com/I%27ve-got-a-silly-question-about-performance-tp14910258p14913339.html">http://www.nabble.com/I%27ve-got-a-silly-question-about-performance-tp14910258p14913339.html</A><BR>
Sent from the PostGIS - User mailing list archive at Nabble.com.<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>