[postgis-users] Query performance issue

[Bill Binko]

A couple of thoughts I had right after sending...


On Thu, 13 Oct 2005, Bill Binko wrote:
> 
> Ok, just to throw my hat into the ring, why don't you try separating the 
> SELECT and the UPDATE.
> 
> --First, CLUSTER your streets table around its GIST index:
> -- (This can take a while, but not hours)
> 
> CLUSTER streets_gidx on streets;
> 
> -- Second, grab the (possibly many-many) relationships
> 
  SELECT streets.oid as soid, prov_ab INTO TEMPORARY street_prov FROM 
  streets JOIN province ON (streets.the_grom && province.the_geom AND 
  distance(streets.the_geom, province.the_geom) <= 0);

I  missed a parenthesis - it should work now.  Also, you might try the 
WHERE form instead of the JOIN form, as Postgresql is not always identical 
in how it plans the query.

> 
> -- Then, index them with both fields in the index...for fast lookup
> 
> CREATE INDEX street_prov_idx ON street_prov(soid, prov_ab);

Order is very important here: don't do on street_prov(prov_ab, soid) or it 
will not use the index.

> 
> -- I am only taking the first one.
> 
> UPDATE streets set prov = (select prov_ab from street_prov_idx where soid 
> = streets.oid limit 1); 
> 
> If nothing else, you'd know which part of this is causing your pain.
> 
> Bill
> 
> PS: the CLUSTER is a good idea anyway
>