From: Chris Gamache <cgg007@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@postgresql.org
Subject: Re: Query planner: current_* vs. explicit date
Date: Thu, 23 Oct 2003 08:29:44 -0700 (PDT)
Message-ID: <20031023152944.56378.qmail@web13805.mail.yahoo.com> (raw)
In-Reply-To: <24641.1066862720@sss.pgh.pa.us>
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cgg007@yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: Query planner: current_* vs. explicit date
In-Reply-To: <20031023152944.56378.qmail@web13805.mail.yahoo.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