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

Tom Lane wrote:
>
> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > Could we add an additional function with strictly defined behaviour of
> > returning the value of a sequence at the beginning of current query, perhaps
> > called ccurval()
>
> Not unless you want the system to run around and read the current value
> of *every* sequence object at the start of *every* transaction, as
> insurance against the possibility that some bit of code might ask for
> the value of ccurval('foo') at some point in the transaction.
>
> This state-saving could doubtless be optimized away to some extent,
> but quite frankly I don't feel a strong need to work on it. You haven't
> yet presented any compelling reason why we should care deeply about the
> performance of WHERE bar = currval('foo') --- how many people want to do
> that?

Probably not many. It just happened that I had to optimise some code
that used it
a lot and it took me some time to figure out why it does a sequential
scan when index
scan would be orders of magnitude faster.

> Even more to the point, why is this so important that we should
> care about making it fast with absolutely no help from the user?

Because it would be very easy to do by marking curval as cacheable.

As I demonstrated to you earlier, using nextval and currval in the same
query is
inherently unsafe and anyone doing it deserves the consequences ;)

Thus making curval cacheable just replaces almost completely
undeterministic
behaviour with another, more predictable and arguably more "correct"
behaviour and
also makes life easier for people programming in pure SQL.

> I have a hard time accepting an "I won't use plpgsql" argument.

I probably will at some point, but I'd much more like the simple case to
be
fast by default.

BTW, did the fmgr update mend the problem with pl functions taking only
8/16 arguments ?

> There are many more pressing performance problems on my to-do list,
> most of them with no such easy workaround.

Sure. This one would just be soo easy to fix ;)

----------
Hannu