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

[Nicolas Ribot]

On 3/29/07, Leticia <lgomez at itba.edu.ar> 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 ?
>

You can disable sequential scan for the time of the query:
set enable_seqscan = off;
to force optimizer to use index, then reset the sequential scan:
set enable_seqscan = on;

Nicolas