"Thomas O'Connell" <tfo@xxxxxxxxxxxx> writes:
> On Sep 16, 2005, at 9:18 AM, Bill Moseley wrote:
>> First question is why the planner is not using an index scan when I
>> use "now()" or CURRENT_TIMESTAMP?
> It also used to be the case (pre-8.0; I couldn't find in the release
> notes whether this was an 8.0 or 8.1 fix) that now() and
> CURRENT_TIMESTAMP were not indexable, I think because of mutability.
8.0 fix. It's not that they're "not indexable" per se, it's that
pre-8.0 planners punted when it came to estimating what fraction of rows
would meet a condition like "timestampcol > now()" --- and the default
estimate for such things doesn't favor an indexscan. The 8.0 change is
to go ahead and run the function and see what value it's returning now
(pardon the pun) and then compare that to the column's statistical
histogram to derive a rows estimate. It's entirely likely that we'll
get ragged on eventually because of cases where this procedure generates
bad estimates ... but for the moment it seems like a win.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
