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

[Leticia]

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


-----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 10:33 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Re: [postgis-devel] Several R-Trees

On Thu, 2007-03-29 at 10:22 -0300, Leticia wrote:
> Sorry, I made a typo error. I am using the "&&" operator and I have run
the
> VACUUM ANALYZE over the two tables, prior to query the plan.
> 
> Could you help me?
> Thanks a lot,
> Leticia


Hi Leticia,

Well the question is more about whether the query returns fast enough
for your application - for example, PostgreSQL will favour sequential
scans over index scans while table sizes are small.

If you do find the speed is slower than you need/expect, you'll have to
post the complete output of EXPLAIN ANALYZE for your query in order to
help determine whether the query can be optimised or not.


Kind regards,

Mark.


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