[postgis-users] Query performance issue

[strk at refractions.net]

Stephen, I think the plan would look better when it will
use the && operator as Join filter rather then the intersects().
Try using explicit join with the select

	SELECT ... FROM table1 JOIN table2
		ON (table1.the_geom && table2.the_geom)
		...

--strk;

On Wed, Oct 12, 2005 at 04:40:57PM -0400, Stephen Woodbridge wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users