[postgis-users] Re: [postgis-devel] Several R-Trees

[Mark Cave-Ayland]

On Thu, 2007-03-29 at 12:36 -0300, Leticia wrote:
> OK. When I execute
> 
> explain analyze
> SELECT COUNT(*)
> FROM usa_rivers2000, usa_cities2000
> WHERE  usa_rivers2000.geometry && usa_cities2000.geometry  AND 
> intersects(usa_rivers2000.geometry, usa_cities2000.geometry )
> 
> I obtain
> 
> "Aggregate  (cost=18.08..18.09 rows=1 width=0) (actual time=50.157..50.159
> rows=1 loops=1)"
> "  ->  Nested Loop  (cost=0.00..17.97 rows=41 width=0) (actual
> time=0.602..50.051 rows=27 loops=1)"
> "        Join Filter: intersects(usa_rivers2000.geometry,
> usa_cities2000.geometry)"
> "        ->  Seq Scan on usa_rivers2000  (cost=0.00..1.17 rows=17 width=130)
> (actual time=0.014..0.050 rows=17 loops=1)"
> "        ->  Index Scan using idxcities2000 on usa_cities2000
> (cost=0.00..0.97 rows=1 width=112) (actual time=0.013..0.277 rows=6
> loops=17)"
> "              Index Cond: (usa_rivers2000.geometry &&
> usa_cities2000.geometry)"
> "              Filter: (usa_rivers2000.geometry && usa_cities2000.geometry)"
> "Total runtime: 50.315 ms"
> 
> 
> Can I force the optimizer to choose a Spatial Join between both R-indexes?
> 
> Thanks,
> Leticia


Hi Leticia,

Well... 50ms should be quick enough for your application, no? The reason
the planner has chosen a sequential scan on your rivers table is because
it only contains 17 rows. Since each index lookup potentially requires
log2(rows) block reads, then the planner decided that it's quicker to
read in the blocks in the table directly.

So I really wouldn't worry about it. As you increase the number of items
in your usa_rivers2000 table, the plan should change accordingly to use
the index. If it doesn't, then let us know and we can try and help you
further.


Kind regards,

Mark.