Strange behaviour with date costants.... - Mailing list pgsql-sql

I have a connections log, some data, the starttime (datetime) and the elapsed
time (timespan).
I'm selecting all the connections active at a particular time.
I've a btree index on starttime.

To cut out a big slice of entries, I added a condition that excludes all the
connections starting before the day before. Now look what happens:

explain select username from log2 where starttime > '29/6/1998 22:30' and
'30/6/1998 22:30' between starttime AND starttime+elapsedtime;

Index Scan using log2_starttime_idx on log2  (cost=2577.87 size=9446 width=32)

Returns almost immediately.

---------

explain select username from log2 where starttime > ('30/6/1998 22:30'::datetime
- '1 day'::timespan) and '30/6/1998 22:30' between starttime AND
starttime+elapsedtime;

Index Scan using log2_starttime_idx on log2  (cost=7732.60 size=9446 width=32)

Takes 20-30 seconds to execute (probably all entries are checked).

---------

Now I am forced to:

select '30/6/1998 22:30'::datetime - '1 days'::timespan;

And put the result in the previous select.

Why all this difference ????
Tested also on 6.5b1, same behaviour....

Thanks in advance.

Best regards.

-- Daniele

-------------------------------------------------------------------------------Daniele Orlandi - Utility Line Italia -
http://www.orlandi.comViaMezzera 29/A - 20030 - Seveso (MI) - Italy
 
-------------------------------------------------------------------------------