Re: Postgres ignoring RTree for geometric operators - Mailing list pgsql-docs

"Gilles Bernard" <gbernard@matra-ms2i.fr> writes:
>> bernardg=3D# explain select count(*) from geo where ('((20000,20000),(3000=
> 0,25000))' && forme ) and (hmin>20000) and (hmax<25000);
>> NOTICE:  QUERY PLAN:
>>
>> Aggregate  (cost=3D13595.20..13595.20 rows=3D1 width=3D4)
>>   -> Seq Scan on geo  (cost=3D0.00..13592.98 rows=3D889 width=3D4)

> It seems that Postgres doesn't use the RTree index at all since it performs=
>  a sequential scan on the whole table.

Try it with the clause the other way round:

... where ( forme && '((20000,20000),(30000,25000))' )

Var on the left is the required normal form for indexscan restriction
clauses.  Postgres should be able to figure out that it can flip your
clause as given into that form ... but for some reason, && is not marked
as commutative in the 7.0 system catalogs, so it won't do it for you.

I have fixed that for 7.1.  If you really want to write the var on the
right side right now, you could patch your system catalogs for yourself:

    UPDATE pg_operator SET oprcom = oid WHERE oprname = '&&'

            regards, tom lane