Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan
Date
Msg-id 10393.966792329@sss.pgh.pa.us
Whole thread Raw
In response to Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan  (Hannu Krosing <hannu@tm.ee>)
Responses Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan
List pgsql-hackers
Hannu Krosing <hannu@tm.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


pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Flex vs Lex
Next
From: Tiago Antão
Date:
Subject: Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan