From: | Daniele Orlandi <daniele(at)orlandi(dot)com> |
---|---|
To: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Strange behaviour with date costants.... |
Date: | 1999-04-16 21:23:56 |
Message-ID: | 3717AA6C.38C530FD@orlandi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.com
Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-04-17 15:46:31 | Re: [SQL] How to compare Datetime |
Previous Message | Dr. Alexey A. Terent'ev | 1999-04-16 18:38:00 | php for pgsql for windows |