test=# create table tt (id serial unique, s varchar);
[populate tt w/ 100000 rows]
test=# insert into tt (s) values ('foo');
test=# select currval('tt_id_seq');
currval
---------
100002
(1 row)
test=# explain select s from tt where id = 100002;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using tt_id_key on tt (cost=0.00..6.01 rows=1 width=32)
Index Cond: (id = 100002)
(2 rows)
test=# explain select s from tt where id = currval('tt_id_key');
QUERY PLAN
------------------------------------------------------
Seq Scan on tt (cost=0.00..1734.42 rows=1 width=32)
Filter: (id = currval('tt_id_key'::text))
(2 rows)
Why is a sequential scan used when comparing id to currval() value vs.
index scan when compared to a constant?
TIA,
John
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
