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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
