[postgis-users] more query optimization

[Paul Ramsey]

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