Received: from etabeta.xt.net (IDENT:root@etabeta.xt.net [62.212.0.40]) by hub.org (8.9.2/8.9.1) with ESMTP id RAA28311 for ; Fri, 16 Apr 1999 17:24:02 -0400 (EDT) (envelope-from daniele@orlandi.com) Received: from orlandi.com (nabla.xt.net [62.212.0.41]) by etabeta.xt.net (8.9.3/8.8.8) with ESMTP id XAA25411 for ; Fri, 16 Apr 1999 23:23:56 +0200 Message-ID: <3717AA6C.38C530FD@orlandi.com> Date: Fri, 16 Apr 1999 23:23:56 +0200 From: Daniele Orlandi Organization: Utility Line Italia X-Mailer: Mozilla 4.51 [en] (WinNT; U) X-Accept-Language: it MIME-Version: 1.0 To: pgsql-sql@postgreSQL.org Subject: Strange behaviour with date costants.... Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 -------------------------------------------------------------------------------