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

[Mark Cave-Ayland]

On Thu, 2007-03-29 at 11:03 -0300, Leticia wrote:
> The analyze plan shows me that it decides to use only one of these two
> indexes, instead of using a Spatial Join
> 
> I have built two R-trees indexes and run analyze over the tables:
> 
> Create index  idxrivers2000 on rivers2000 using gist (geometry
> gist_geometry_ops)                     
> 
> Create index  idxcities2000 on cities2000 using gist (geometry
> gist_geometry_ops)    
> ...
> 
> Then I query
> 
> explain 
> 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)"
> "  ->  Nested Loop  (cost=0.00..17.97 rows=41 width=0)"
> "        Join Filter: intersects(usa_rivers2000.geometry,
> usa_cities2000.geometry)"
> "        ->  Seq Scan on usa_rivers2000  (cost=0.00..1.17 rows=17
> width=130)" 
> "        ->  Index Scan using idxcities2000 on usa_cities2000
> (cost=0.00..0.97 rows=1 width=112)"
> "              Index Cond: (usa_rivers2000.geometry &&
> usa_cities2000.geometry)"
> "              Filter: (usa_rivers2000.geometry && usa_cities2000.geometry)"
> 
> 
> Can I force the optimizer to use the Two indexes ?
> 
> 
> Thanks, 
> Leticia


Hi Leticia,

You need to provide the output of EXPLAIN ANALYZE rather than just
EXPLAIN. EXPLAIN shows what the database *thinks* will happen, where as
EXPLAIN ANALYZE shows a comparison against what the database *thinks*
will happen and what actually *did* happen. Don't forget to include all
the timing information either.


Kind regards,

Mark.