[postgis-users] Query performance issue

[Stephen Woodbridge]

Michael Fuhr wrote:
> 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.

OK, thanks, this looks much better:

explain update streets set prov=prov_ab from province b where
streets.the_geom && b.the_geom and intersects(streets.the_geom, b.the_geom)

Nested Loop  (cost=0.00..3534.86 rows=1199269 width=326)
   Join Filter: intersects("inner".the_geom, "outer".the_geom)
   ->  Seq Scan on province b  (cost=0.00..1.14 rows=14 width=92196)
   ->  Index Scan using streets_gidx on streets  (cost=0.00..38.12 
rows=9 width=320)
         Index Cond: (streets.the_geom && "outer".the_geom)


> 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
> );
> 

Ah, this is very interesting also. I had thought that it would be nice 
to store an array() of provinces if there were more than one, but I was 
not sure how to define the column and do that.

The UPDATE example above does not seem correct, as it looks like it will 
update prov on ALL rows because there is not where in the UPDATE only in 
the sub-select. Is Postgres smart enough to apply the the where in the 
sub-select also to the UPDATE? The example in the doc page above implies 
this is not the case.

I tried to figure out how to do it as a sub select, but came up empty.

Thanks you for looking at this and giving me some ideas.

-Steve