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

The following bug has been logged online:

Bug reference:      4656
Logged by:          Mathias Seiler
Email address:      mathias.seiler@gmail.com
PostgreSQL version: 8.3.6
Operating system:   Debian Linux Lenny (testing)
Description:        Indexes not used when comparing nextval() and currval()
to integers
Details:

Hello there

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. This obviously results in a full
table scan, which gets very slow after a few thousand entries.

So I tried to cast the returning value from currval() to integer (which is
the same type of id) but this still doesn't use the index (which is there):

EXPLAIN UPDATE booking_entries SET booking_date = now() where id =
nextval('booking_entries_id_seq'::regclass)::int4;
                               QUERY PLAN
-------------------------------------------------------------------------
 Seq Scan on booking_entries  (cost=0.00..351.95 rows=1 width=89)
   Filter: (id = (nextval('booking_entries_id_seq'::regclass))::integer)
(2 rows)

set enable_seqscan = false;
SET

EXPLAIN UPDATE booking_entries SET booking_date = now() where id =
nextval('booking_entries_id_seq'::regclass)::int4;
                                   QUERY PLAN

----------------------------------------------------------------------------
----
 Seq Scan on booking_entries  (cost=100000000.00..100000163.01 rows=1
width=89)
   Filter: (id = (nextval('booking_entries_id_seq'::regclass))::integer)
(2 rows)

EXPLAIN UPDATE booking_entries SET booking_date = now() where id = 1;
                                         QUERY PLAN

----------------------------------------------------------------------------
-----------------
 Index Scan using booking_entries_pkey on booking_entries  (cost=0.00..8.28
rows=1 width=89)
   Index Cond: (id = 1)
(2 rows)


What's going wrong? Could this be a bug?

Kind Regards

P.S.

SELECT version();
                                          version

----------------------------------------------------------------------------
----------------
 PostgreSQL 8.3.6 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.3-3) 4.3.3
(1 row)

uname -s -r -v -m -o
Linux 2.6.26-1-686 #1 SMP Mon Dec 15 18:15:07 UTC 2008 i686 GNU/Linux

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

toruvinn wrote:
> On Mon, 16 Feb 2009 10:37:27 +0100, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.com> wrote:
>> You can read the value returned by currval into a host language
>> variable and send it back as a ?.
> Wouldn't it be possible to do UPDATE booking_entries SET date = ? where
> id = (SELECT currval('booking_entries_id_seq'::regclass)); too?

Oh yes, that should work as well.

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

On Mon, 16 Feb 2009 10:37:27 +0100, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
> You can read the value returned by currval into a host language variable
> and send it back as a ?.
Wouldn't it be possible to do UPDATE booking_entries SET date = ? where id = (SELECT
currval('booking_entries_id_seq'::regclass));too? 

--
ru