[postgis-users] Query performance issue
strk at refractions.net
strk at refractions.net
Thu Oct 13 10:45:11 PDT 2005
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
More information about the postgis-users
mailing list