pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Daniele Orlandi <daniele@orlandi.com>
To: pgsql-sql@postgreSQL.org
Subject: Strange behaviour with date costants....
Date: Fri, 16 Apr 1999 23:23:56 +0200
Message-ID: <3717AA6C.38C530FD@orlandi.com> (raw)


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



Message-ID: <3717AA6C.38C530FD@orlandi.com>
Permalink:  ../3717AA6C.38C530FD@orlandi.com/
Also on:    postgresql.org/message-id/3717AA6C.38C530FD@orlandi.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: daniele@orlandi.com
  Subject: Re: Strange behaviour with date costants....
  In-Reply-To: <3717AA6C.38C530FD@orlandi.com>
  References: <3717AA6C.38C530FD@orlandi.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox