[postgis-users] problems optimising PostGIS queries

[Bill Binko]

On Fri, 22 Jul 2005, Gavin Fleming wrote:
>  
> SET enable_seqscan TO off;
> explain SELECT t.gid,t.objectid,t.id,t.sub_cat,t.lat,t.longx,f.fid,astext(f.the_geom) FROM towers t,fire f
> WHERE (f.tick > '2005-07-18') AND (expand(f.the_geom,.01) && expand(t.the_geom,.01));
> SET enable_seqscan TO on;
> 

The reason you're not using the GiST index(es) is that you don't have 
indexes defined for the term "expand(f.the_geom, .01)" or 
"expand(t.the_geom,.01)".  

In an ideal world, if the the value you're giving to expand (.01) 
is always a constant, you could solve this by adding a functional index:

CREATE INDEX expanded_t_gist ON towers GIST (expand(the_geom, .01))
CREATE INDEX expanded_f_gist ON fires GIST (expand(the_geom, .01))

Then, your SQL above would use the indexes.


> NOTICE:  LWGEOM_gist_joinsel called with arguments that are not column references

This makes me think that yours is not an ideal world, and that strk or 
others will tell me this won't work.  

If you have a need to change that constant on each request, you really do 
need to calculate a new bounding box for each shape you're comparing.  
However, since you're working with bounding boxes, you can probably get 
away with adding the entire buffer to one side:

SELECT ... WHERE t.the_geom && expand(f.the_geom, .02)

That should use t's GiST index regardless of whether you have a functional 
index defined.  You should also add it to the side with fewer items 
(hopefully fires! :)

Hope this helps.
Bill