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

[Leticia]

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

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mark
Cave-Ayland
Sent: Thursday, March 29, 2007 11:47 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Re: [postgis-devel] Several R-Trees

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.


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users