Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 96C859FB58A for ; Tue, 22 May 2007 09:43:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 31625-02-3 for ; Tue, 22 May 2007 09:43:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from cassarossa.samfundet.no (cassarossa.samfundet.no [129.241.93.19]) by postgresql.org (Postfix) with ESMTP id A78439FB50A for ; Tue, 22 May 2007 09:43:23 -0300 (ADT) Received: from trofast.sesse.net ([129.241.93.32]) by cassarossa.samfundet.no with esmtp (Exim 4.50) id 1HqThw-0006IL-Lq for pgsql-performance@postgresql.org; Tue, 22 May 2007 14:43:20 +0200 Received: from sesse by trofast.sesse.net with local (Exim 3.36 #1 (Debian)) id 1HqThw-0006SF-00 for ; Tue, 22 May 2007 14:43:20 +0200 Date: Tue, 22 May 2007 14:43:20 +0200 From: "Steinar H. Gunderson" To: pgsql-performance@postgresql.org Subject: Re: Performace comparison of indexes over timestamp fields Message-ID: <20070522124320.GA24779@uio.no> Mail-Followup-To: pgsql-performance@postgresql.org References: <4652C846.4020801@andromeiberica.com> <88daf38c0705220539q5807d2c2t1f20a03922bf8d33@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <88daf38c0705220539q5807d2c2t1f20a03922bf8d33@mail.gmail.com> X-Operating-System: Linux 2.6.20.4 on a x86_64 X-Message-Flag: Outlook? --> http://www.mozilla.org/products/thunderbird/ User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/434 X-Sequence-Number: 24824 On Tue, May 22, 2007 at 02:39:33PM +0200, Alexander Staubo wrote: > PostgreSQL uses B-tree indexes for scalar values. For an expression > such as "t between a and b", I believe it's going to match both sides > of the table independently (ie., t >= a and t <= b) and intersect > these subsets. This is inefficient. A B-tree index can satisfy range queries such as this. > You should get better performance by mapping timestamps to a > one-dimensional plane and indexing them using GiST. GiST implements an > R-tree-like structure that supports bounding-box searches. You may be thinking of interval overlaps? /* Steinar */ -- Homepage: http://www.sesse.net/