index and timestamp column

From: Thomas Zehetbauer <thomasz(at)hostmaster(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: index and timestamp column
Date: 2002-02-05 17:26:01
Message-ID: 20020205182601.B12903@hostmaster.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Running postgresql 7.1.3:

I have a timestamp column in my table and I want to select all rows either
elder or newer than 14 days.
SELECT * FROM table WHERE column > CURRENT_TIMESTAMP-'14 days'::interval
SELECT * FROM table WHERE column < CURRENT_TIMESTAMP-'14 days'::interval
Postgresql refuses to use the index on this column except if I do a
SELECT CURRENT_TIMESTAMP-'14 days'::interval
and use the returned value instead. This costs about one third of the
sequential scan used otherwise.

I have now also tried to work around this by creating an index on age(column)
SELECT * FROM table WHERE age(column) > '14 days'
SELECT * FROM table WHERE age(column) < '14 days'
but this index is only used if I use a equals operator...

Regards
Tom

PS: I believe that the postgresql source code has become the victim of a very
dangerous and widespread virus called featuritis. Previously known to be
widely spread in the world of closed source software it obviously has now
started to infect the world of Open Source Software. To avoid further
spreading I suggest that postgresql should be rewritten from scratch!
--
T h o m a s Z e h e t b a u e r ( TZ251 )
PGP encrypted mail preferred - KeyID 96FFCB89
mail pgp-key-request(at)hostmaster(dot)org

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2002-02-05 18:07:18 Re: resource leak in 7.2
Previous Message Tom Pfau 2002-02-05 17:12:48 Re: [CYGWIN] resource leak in 7.2