Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> It seems that optimiser is unaware that currval('seq') can be treated
> as a constant within an expression and thus produces suboptimal plans
> for WHERE clauses that use currval thus using a seq scan instead of
> index scan.

currval() does not qualify to be marked cachable, since it does not
always return the same result given the same arguments.

There are a few functions that are not cachable but could be treated
as constants within a single transaction, now() being the most obvious
example. Currently there is no intermediate function type between
"cachable" and "noncachable" but I have toyed with the idea of inventing
one. Getting the semantics right could be tricky however.

However, even if we had a concept of "constant within a transaction/
scan/whatever", currval() would not qualify --- what if there is a
nextval() being invoked somewhere else in the query, possibly inside a
user-defined function where the optimizer has no chance of seeing it?

In short, there is no way of optimizing currval() in the way you want
without risking breakage.

For interactive queries you could fake the behavior you want by creating
a user-defined function that just calls currval(), and then marking this
function cachable. Don't try calling such a function inside a SQL or
plpgsql function however, or you will be burnt by premature constant-
folding. Basically, this technique leaves it in your hands to determine
whether the optimization is safe.

regards, tom lane