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
