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

Re: GiST indexing tuples

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Matthew" <matthew(at)flymine(dot)org>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: GiST indexing tuples
Date: 2007-11-28 13:36:48
Message-ID: 8763zmh4pb.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
"Matthew" <matthew(at)flymine(dot)org> writes:

> On Tue, 27 Nov 2007, Steinar H. Gunderson wrote:
>> On Tue, Nov 27, 2007 at 06:28:23PM +0000, Matthew wrote:
>> > SELECT * FROM table WHERE a > 1 AND b < 4;
>>
>> This sounds like something an R-tree can do.
>
> I *know* that. However, Postgres (as far as I can see) doesn't provide a
> simple R-tree index that will index two integers. This means I have to
> write one myself. I'm asking whether it is possible to get two values into
> a GiST index, which would allow me to implement this.

The database is capable of determining that a>1 and b<4 are both conditions
which a single index can satisfy.

However GIST itself treats each column of the index independently applying the
first column then the second one and so on like a traditional btree index, so
it doesn't really do what you would want.

I did propose a while back that GIST should consider all columns
simultaneously in the same style as rtree. 

However this would require making GIST somewhat less general in another sense.
Currently page splits can be handled arbitrarily but if you have to be able to
combine different datatypes it would mean having to come up with a standard
algorithm which would work everywhere. (I suggested making everything work in
terms of "distance" and then using the n-space vector distance (ie
sqrt((a1-b1)^2+(a2-b2)^2+...).) This means GIST wouldn't be as general as
it is now but it would allow us to handle cases like yours automatically.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

In response to

pgsql-performance by date

Next:From: Csaba NagyDate: 2007-11-28 13:48:22
Subject: Re: TB-sized databases
Previous:From: Bill MoranDate: 2007-11-28 13:27:16
Subject: Re: TB-sized databases

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