[postgis-users] Query performance issue
bill at binko.net
Thu Oct 13 15:18:54 PDT 2005
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.
> PS: the CLUSTER is a good idea anyway
More information about the postgis-users