To: "Gilles Bernard" <gbernard@matra-ms2i.fr>
cc: pgsql-docs@postgresql.org
Subject: Re: Postgres ignoring RTree for geometric operators 
In-reply-to: <200012290946.KAA01221@fwm1.matra-ms2i.fr> 
References: <200012290946.KAA01221@fwm1.matra-ms2i.fr>
Comments: In-reply-to "Gilles Bernard" <gbernard@matra-ms2i.fr>
	message dated "Fri, 29 Dec 2000 09:43:31 +0100"
Date: Sun, 31 Dec 2000 17:42:34 -0500
Message-ID: <14474.978302554@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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