[postgis-users] more query optimization

[Robert W. Burgholzer]

OK,
the USE_STATS option was indeed set to 1 in my postgis makefile, I have run 
the following query:

select update_geometry_stats();

And voila! the query comes back in under a minute,  perfectly acceptable. 
Now, do you suggest I include this update_geometry_stats() call in my daily 
vacuum job?

Thanks a bunch,
r.b.

At 07:33 AM 6/1/2004 -0700, you wrote:
>Do you have USE_STATS on, and have you run the update statistics function? 
>That is the magic incantation, generally...
>
>On Tuesday, June 1, 2004, at 06:41 AM, Robert W. Burgholzer wrote:
>
>>I am trying to do a join using views, and am having some serious 
>>performance issues as a result.
>>
>>I have a postgis table, with soil map shapes and ID's. I have three other 
>>tables that when linked together in a view give the names and 
>>characteristics of the soils in the soil map, and the variation of the 
>>characteristics by soil layer, indexed by a key they share with the soil 
>>shape table.
>>
>>All of these layers can be queried fairly quickly, although my soil map 
>>table has around a half million entries in it, with about 4,000 unique 
>>ID's that map to the other two data tables. When I just pull the soil 
>>layer shapes into my arcview view (using Armin Burger's avpgconn) without 
>>joining on the property data, the process takes about 10-20 seconds 
>>depending on the width of the view. When I pull it with one soil layer 
>>(the top layer only) properties view joined, I get about 10 minutes as 
>>the result, and when I join both the top soils layer view and the second 
>>soil layer view my query time is about 15-20 minutes.
>>
>>I surmise from the query explain output that the join on the key 
>>properties between these layers is occurring before the soil shape 
>>records are weeded out to the spatial extent (which is a very low cost 
>>selection since they are indexed), thus, all half million records are 
>>being joined first, then the ones in the spatial extent are being 
>>grabbed. What I would like to do is reverse this order. Does anyone have 
>>any idea how to do this?
>>
>>Thanks in advance.
>
>      Paul Ramsey
>      Refractions Research
>      Email: pramsey at refractions.net
>      Phone: (250) 885-0632
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users

Robert Burgholzer
Environmental Engineer
MapTech Inc.
http://www.maptech-inc.com/