[postgis-users] Query performance issue

[Stephen Woodbridge]

Robert Burgholzer wrote:
> 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?

Just the first one. I did the explain on the select as a comparison. 
Michael Fuhr pointed out that my update query was constructed wrong 
because I had the streets table referenced in the from clause that was 
not needed and results in joining (streets, streets, province)!

I also think that I can use distance(a.the_geom, b.the_geom) = 0.0 
instead of intersects(a.the_geom, b.the_geom) which should be 
significantly faster.

I'll try your ideas below if Michael's fix doesn't speed it up enough.

Thank you for taking the time to look at this and offer some suggestions.

-Steve

> 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