Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B777D2E30C6 for ; Wed, 28 Nov 2007 09:36:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 25617-01-2 for ; Wed, 28 Nov 2007 09:36:45 -0400 (AST) Received: from oxford.xeocode.com (unknown [62.232.55.118]) by postgresql.org (Postfix) with ESMTP id 50CC62E00D5 for ; Wed, 28 Nov 2007 09:36:50 -0400 (AST) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.67) (envelope-from ) id 1IxN5s-0001MK-NS; Wed, 28 Nov 2007 13:36:48 +0000 To: "Matthew" Cc: Subject: Re: GiST indexing tuples In-Reply-To: (matthew@flymine.org's message of "Wed\, 28 Nov 2007 13\:08\:04 +0000 \(GMT\)") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 3364) References: <20071127183855.GA22330@uio.no> From: Gregory Stark Organization: EnterpriseDB Date: Wed, 28 Nov 2007 13:36:48 +0000 Message-ID: <8763zmh4pb.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/340 X-Sequence-Number: 27755 "Matthew" 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!