Re: constant scalar subselect no longer equivalent to constant?

merlyn@stonehenge.com (Randal L. Schwartz) writes:
> So this changed between 7.2 and 7.3?

Well, no, it didn't change.  AFAIR the only relevant difference is that
7.3 classifies now() as stable whereas 7.2 didn't have that concept and
so had to classify it as noncachable ("volatile" in 7.3 terminology).

What your sub-select was doing for you in 7.2 was hiding the volatile
function --- the planner's decision about whether an expression is safe
to compare to an index doesn't look inside subselects.  (Which is
arguably a bug, but so far I haven't dared change it because I knew
people were using exactly this hack...)  In 7.3 that's not necessary
because now() isn't considered volatile anyway.

But in either version, what the planner is seeing is
    WHERE indexedcolumn > unknownvalue
which is going to force it to fall back on a default estimate for the
number of rows retrieved.  If you were actually scanning the whole
table, you'd not have gotten an indexscan in either version.  But the
presence of the LIMIT causes the planner to prefer a fast-start plan,
rather than the seqscan-and-sort you'd have gotten without any LIMIT.

I'm guessing that the situation with your table is right on the edge
of where the planner will flip between indexscan and seqscan for this
query.  How much difference is there in the estimated cost when you
force indexscan or seqscan?  It could be that the change in plan is
just due to some small tweak in the cost models between 7.2 and 7.3.

> What's odd is that even writing a function didn't help:
>     add function ago(interval) returns timestamp with time zone
>     stable
>     language 'sql'
>     as 'select now() - $1';
> I thought the addition of the "stable" keyword would make the return
> value be the same as a constant.

No, you'd have to claim it to be immutable to make the planner treat it
as a constant.  Stable means that the function value will hold still
long enough to be safe to use as an indexscan qualifier (ie, that
actually evaluating it at each row of a seqscan would give the same
semantic result as evaluating it only once for use in an indexscan).
It does not make it okay to pre-evaluate such a function during
planning.  now() is pretty much the prototypical example of why we
invented the "stable" category: it doesn't change within a query,
but that doesn't make it safe for the planner to pre-evaluate it.
Plans can live across transactions, so the value of now() at runtime
isn't necessarily what the planner would have gotten.

            regards, tom lane