Re: BUG #4656: Indexes not used when comparing nextval() and currval() to integers

Mathias Seiler wrote:
> I'm not sure if I'm doing something terribly wrong here, but I when I
> noticed a slowdown during a large transaction I dig into the problem and
> found that when I use this prepared statement:
>
> UPDATE booking_entries SET date = ? where id =
> currval('booking_entries_id_seq'::regclass);
>
> The index over the column "id" is not used.

It's because currval and nextval are volatile functions
(http://www.postgresql.org/docs/8.3/interactive/xfunc-volatility.html).
Because of that, a lot of optimizations are disabled for them, and they
can not be used as index scan predicates. The interpretation of that
query is that you wanted to evaluate currval/nextval for all the rows in
the table, even though the UPDATE only matches some of the rows.

You can read the value returned by currval into a host language variable
and send it back as a ?.

Or you can create a wrapper function around currval that's marked as
stable instead of volatile, to hide currval's volatility. However, that
would amount to lying to the optimizer and you might get some surprising
results with more complex queries, so I wouldn't recommend it.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com