Re: Postgres ignoring RTree for geometric operators

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Gilles Bernard" <gbernard(at)matra-ms2i(dot)fr>
Cc: pgsql-docs(at)postgresql(dot)org
Subject: Re: Postgres ignoring RTree for geometric operators
Date: 2000-12-31 22:42:34
Message-ID: 14474.978302554@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

"Gilles Bernard" <gbernard(at)matra-ms2i(dot)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

In response to

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Hannu Krosing 2001-01-01 03:57:55 Re: Inheritance docs error.
Previous Message Tom Samplonius 2000-12-31 18:26:08 Re: Rather incorrect text in admin guide