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