To: josh@agliodbs.com
Cc: "scott.marlowe" <scott.marlowe@ihs.com>, Chris Gamache <cgg007@yahoo.com>,
	pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Query planner: current_* vs. explicit date 
In-reply-to: <200310221325.18470.josh@agliodbs.com> 
References: <Pine.LNX.4.33.0310221308070.12830-100000@css120.ihs.com>
	<200310221325.18470.josh@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Wed, 22 Oct 2003 13:25:18 -0700"
Date: Wed, 22 Oct 2003 18:45:20 -0400
Message-ID: <24641.1066862720@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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