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

[Martin Davis]

Isn't it the case that given a two-table join, the query engine will 
always do a sequential scan of one table and look up values using the 
index of the other table (assuming the optimizer decides to use the 
index in the first place, of course) ? 

It seems to me that using R-trees on both sides of a join would require 
some kind of complicated mutual tree walking - which sounds more like an 
interesting resarch problem than a current implementation technique...  8^)

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
>
>
> -----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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022