From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: scott.marlowe <scott.marlowe@ihs.com>
Cc: Chris Gamache <cgg007@yahoo.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Query planner: current_* vs. explicit date
Date: Wed, 22 Oct 2003 18:45:20 -0400
Message-ID: <24641.1066862720@sss.pgh.pa.us> (raw)
In-Reply-To: <200310221325.18470.josh@agliodbs.com>
References: <Pine.LNX.4.33.0310221308070.12830-100000@css120.ihs.com>
<200310221325.18470.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
>> I'd guess that the planner doesn't know what current_date::timestamp is
>> ahead of time, so it chooses a seq scan.
> Yes, this is a known problem. There was a single-query workaround, but I
> can't remember it right now.
Right; the problem from the planner's point of view is that it sees
WHERE datecol >= unknownvalue
and it has no idea how much of the table will be fetched by this query.
Its default guess is that a fair fraction of the table will be fetched
(one-third, I think) and that dissuades it from using an indexscan.
This seems reasonable to me, since the worst-case behavior if it were
to default to an indexscan could be awful.
The easiest workaround is to convert the query into a range query,
for example
WHERE datecol >= current_date AND datecol <= current_date + 1000;
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.
regards, tom lane
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: tgl@sss.pgh.pa.us, josh@agliodbs.com, scott.marlowe@ihs.com, cgg007@yahoo.com
Subject: Re: Query planner: current_* vs. explicit date
In-Reply-To: <24641.1066862720@sss.pgh.pa.us>
* 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