[postgis-users] Query performance issue

[Michael Fuhr]

On Wed, Oct 12, 2005 at 01:17:30PM -0400, Stephen Woodbridge wrote:
> I think this might be the problem of small table with big polygons and 
> large table of small geometries confusing the the query planner. I can't 
> find what if any work-a-round there was for this problem in the archives.
> 
> explain update streets set prov=prov_ab from streets a, province b where
> a.the_geom && b.the_geom and intersects(a.the_geom, b.the_geom);

Are you sure you want streets in the from list?  I think this is
what's giving you the wrong plan; see the PostgreSQL documentation
for a description of how FROM works in UPDATE statements.

http://www.postgresql.org/docs/8.0/interactive/sql-update.html

The following might be what you need instead, but the results will
be indeterminate for rows in streets that join to more than one row
in province:

UPDATE streets SET prov = prov_ab 
FROM province b
WHERE streets.the_geom && b.the_geom AND intersects(streets.the_geom, b.the_geom);

As the UPDATE documentation suggests, if the indeterminacy is a
problem then you could use a subselect:

UPDATE streets SET prov = (
  SELECT prov_ab
  FROM province b
  WHERE streets.the_geom && b.the_geom AND intersects(streets.the_geom, b.the_geom)
  ORDER BY prov_ab
  LIMIT 1
);

-- 
Michael Fuhr