Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-docs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group