Re: Query planner: current_* vs. explicit date

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