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(colum=
n)
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 ve=
ry
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!
--=20
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@hostmaster.org