[postgis-users] Query performance issue

[Robert Burgholzer]

Are you saying that the second query, which explained out at
(cost=0.00..536.69 rows=1199269 width=6), ran for 15 hours, or the one
that explained out at (cost=0.00..6358011493.24 rows=2157363205190
width=346)??

Or both?

If the second one is as fast as the query analyzer predicts, it would be
a simple excercise of creating a temp table with the results of the
second query, including the OID or some other unique ID on your streets
table, and then running an update matching on the unique street ID.

If the second one is also prohibitively slow, then I guess that it is
the intersects() function that is killing you. I found that the GEOS
functions were increasing my execution time on a similar query by an
order of magnitude or more. 

One way I improved it somewhat was to force the query to use the GIST
index first, by doing the following:

select b.prov_ab 
from streets a, province b, 
   (select a.OID from streets as a, province as b 
     where a.the_geom && b.the_geom
   ) as c
where a.OID = c.OID and intersects(a.the_geom, b.the_geom);

This explains out as slower, but what I found was that the first query,
which uses the geos function && call in the same query, is faster until
the set of results reaches about 500, then the second query which uses
the && in a sub-query is actually faster. That said, I was using the 3
nested functions:
 (area2d(intersection(a.the_geom,b.the_geom))/area2d(b.the_geom)) > 0.9
instead of just the intersects() command, so your threshold of increased
efficiency may be higher.

On Wed, 2005-10-12 at 13:17, Stephen Woodbridge wrote:
> Hi all,
> 
> 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);
> 
> Nested Loop  (cost=0.00..6358011493.24 rows=2157363205190 width=346)
>    Join Filter: intersects("inner".the_geom, "outer".the_geom)
>    ->  Nested Loop  (cost=0.00..1211602.30 rows=25184558 width=92536)
>          ->  Seq Scan on province b  (cost=0.00..1.14 rows=14 width=92196)
>          ->  Seq Scan on streets  (cost=0.00..68553.97 rows=1798897 
> width=340)
>    ->  Index Scan using streets_gidx on streets a  (cost=0.00..38.12 
> rows=9 width=180)
>          Index Cond: (a.the_geom && "outer".the_geom)
> 
> -- and --
> 
> explain select b.prov_ab from streets a, province b where
> a.the_geom && b.the_geom and intersects(a.the_geom, b.the_geom);
> 
> Nested Loop  (cost=0.00..536.69 rows=1199269 width=6)
>    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 a  (cost=0.00..38.12 
> rows=9 width=160)
>          Index Cond: (a.the_geom && "outer".the_geom)
> 
> 
> This query ran for over 15 hours and I aborted it :(
> 
> both tables have gist indexes on the the_geom and I have run vaacum 
> analyze on them both.
> 
> Any ideas would be appreciated, as I have similar updates to do for 3 
> more tables after this one is done.
> 
> -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-- 
Non-point Source Data Analyst
University of Maryland, College Park
Chesapeake Bay Program Office
410 Severn Avenue, Suite 305B
Annapolis, MD, 21403
Phone: (410) 267-5779

rburghol at chesapeakebay.net