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
-------------------------------------------------------------------------------