Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 87D739FB41B for ; Tue, 22 May 2007 09:39:42 -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 11755-05 for ; Tue, 22 May 2007 09:39:30 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.232]) by postgresql.org (Postfix) with ESMTP id 0F5A59FB3DF for ; Tue, 22 May 2007 09:39:34 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id s1so2581272nze for ; Tue, 22 May 2007 05:39:33 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=kmm/NkYd9MJ8U5Ki8Ivsuzp+YBtcreoI32adPupGlPLffUko695cXCjmoJ/FK9ZQQtpoo3AZp5IS0fa6MjxVU84rH3dUIEOUM/YLnSo0FpkINpmAz8oXlqMk7D3JaNlV7/RBDYtHxM8M76bjGd2K5GzEZlBd4/qAauCFZqLFDes= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=LqmAGSJcBRjPVAvyTXU8uqQdldzhrZFj+W64Z8dWAYAasi90L0C5x8InfqN99aKjgGCQAR04sZsqvxlf/jyfxRe73dDUuuKUuebY+9EPGDtCD3ZSioDInNr5UF65SgFfpHqRS3CCT/gTfnL5TfxRYfFqR9/wXhCXR61aJU4oSrU= Received: by 10.64.213.3 with SMTP id l3mr1123478qbg.1179837573504; Tue, 22 May 2007 05:39:33 -0700 (PDT) Received: by 10.65.177.7 with HTTP; Tue, 22 May 2007 05:39:33 -0700 (PDT) Message-ID: <88daf38c0705220539q5807d2c2t1f20a03922bf8d33@mail.gmail.com> Date: Tue, 22 May 2007 14:39:33 +0200 From: "Alexander Staubo" To: arnaulist@andromeiberica.com Subject: Re: Performace comparison of indexes over timestamp fields Cc: "PostgreSQL Performance" In-Reply-To: <4652C846.4020801@andromeiberica.com> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4652C846.4020801@andromeiberica.com> X-Google-Sender-Auth: 66dbb82df41697cd X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/433 X-Sequence-Number: 24823 On 5/22/07, Arnau wrote: > On older versions of PostgreSQL, at least in my experience, queries > on timestamps fields even having indexes where performing quite bad > mainly sequential scans where performed. 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. 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. This involves setting up a functional index: create index ... on payment_transactions using gist ( box(point(extract(epoch from time), 0), point(extract(epoch from time), 0)) box_ops) I'm using box() here because GiST doesn't have a concept of points. Then insert as usual, and then query with something like: select ... from payment_transactions where box( point(extract(epoch from '2006-04-01'::date), 0), point(extract(epoch from '2006-08-01'::date), 0)) && box( point(extract(epoch from time), 0), point(extract(epoch from time), 0)); PostgreSQL should be able to exploit the GiST index by recognizing that the result of box() expression operand is already computed in the index. This much less inconvenient and portable -- I would love for PostgreSQL to be provide syntactic sugar and special-casing to make this transparent -- but worth it if you are dealing with a lot of range searches. > Now I have a newer version of PostgreSQL and I've done some tests > comparing the performance of an index over a timestamp field with a > numeric field. To do so, I have the following table: > > Table "public.payment_transactions" > Column | Type | Modifiers > ----------------+-----------------------------+--------------------------------- > transaction_id | character varying(32) | not null > timestamp_in | timestamp without time zone | default now() > credits | integer | > epoch_in | bigint | > epoch_in2 | double precision | [snip] A timestamp is stored internally as an 8-byte double-precision float. Therefore, timestamp_in and epoch_in2 should behave identically. > While doing the tests this table has about 100.000 entries. Make sure PostgreSQL is able to keep the entire table in memory by setting shared_buffers; you don't want to be hitting to the disk. Make sure you run "analyze" on the table before you execute the test. > To test the diferent indexes I have executed the following: Your query plans are roughly identical. The difference in the timings implies that you only ran the queries once. I suggest you run each query at least 10 times, and report the individual numbers (the "total runtime" parts of the output) you get. Arithmetic means are not that interesting. Alexander.