Message-ID: <20031023152944.56378.qmail@web13805.mail.yahoo.com>
Date: Thu, 23 Oct 2003 08:29:44 -0700 (PDT)
From: Chris Gamache <cgg007@yahoo.com>
Reply-To: cgg007@yahoo.com
Subject: Re: Query planner: current_* vs. explicit date 
To: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-sql@postgresql.org
In-Reply-To: <24641.1066862720@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Thanks Tom (and others!) Right-on-the-money, as always...

By giving it a definitive range I was able to coax query planner to use the
index:

SELECT id FROM trans_table WHERE trans_date >=  (SELECT
current_date::timestamptz) AND trans_date < (SELECT current_timestamp);

gave me from midnight to the present...

Aside from a slight amount of ugliness, the solution is quite adequate. The
subselects shouldn't cause too much overhead, yes?

BTW, This didn't work:

SELECT id FROM trans_table WHERE trans_date >= current_date::timestamptz AND
trans_date < current_timestamp;

Which was a "nonconstant" version of the above. I think it still suffers from
the timestamp >= unknown_value problem.

CG

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> being careful that both comparison values are nonconstant (don't use
> 'infinity'::timestamp, for instance, even though that might seem like
> a reasonable thing to do).  The planner still has no idea how many rows
> will be fetched exactly, but it does realize that this is a range
> condition, and its default assumption about the number of matching rows
> is small enough to encourage indexscan use.
> 
> Of course this workaround assumes that you can pick an upper bound that
> you are sure is past the end of the available values, but that's usually
> not hard in the sort of context where you would have thought that the
> one-sided inequality test is a sane thing to do anyway.


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com