[postgis-users] Overlap Queries - speed

[Bill Binko]

On Mon, 3 Oct 2005, rb wrote:

> Thanks for the feedback, I have tried using the intersects() clause and
> it is still running (about 10 minutes going). You are right that there
> were many false posiives with the other. Here is the results of the
> explain :
> 
> 
> satest=# explain select a.msid, b.lrseg, b.landseg, b.riverseg,
> area2d(intersection(a.the_geom, b.the_geom))/area2d(b.the_geom) from
> bmp_multiseg as a, p5lrsegs as b where a.the_geom && b.the_geom;
>                                        QUERY
> PLAN                                                                                 
> ----------------------------------------------------------------------------------------
>  Nested Loop  (cost=0.00..14498.24 rows=17870 width=28135)
>    ->  Seq Scan on p5lrsegs b  (cost=0.00..98.64 rows=2364 width=11555)
>    ->  Index Scan using bmm_gix on bmp_multiseg a  (cost=0.00..6.00
> rows=1 width=16580)
>          Index Cond: (a.the_geom && "outer".the_geom)
> (4 rows)
> 
> 
> it seems to me that the index is being used. It has been analyzed and
> vacuumed about six brazillion times so far.
> 
> So, I feel like a teenager: "Is this normal??"
> 

You have Indexes on both Tables?  I have seen places where it will not use 
both.  I have found that putting the index on the larger table is a big 
improvement.  

Is this normal.....  Well, that's a tough one.  Strk will have to actually 
answer that.  I will say that it's a known problem that "small numbers of 
complex geometries" perform worse than "large numbers of simple 
geometries".  There are good reasons for this, but I don't know of a 
solution.

Bill