Skip site navigation (1) Skip section navigation (2)

key = currval('tab_key_seq') choses SEQSCAN?!

From: Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: key = currval('tab_key_seq') choses SEQSCAN?!
Date: 2004-02-25 21:52:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
I have a large table (named "changes") which is perfectly willing to
support index lookups when its primary key (named "change") is
compared to a constant integer:

# explain select * from changes where change = 42;
                                 QUERY PLAN                                  
 Index Scan using changes_pkey on changes  (cost=0.00..3.01 rows=1 width=78)
   Index Cond: (change = 42)
(2 rows)

But this same table suddenly becomes unwilling to use an index scan if
the target value is the result of the currval() function:

# explain select * from changes where change = currval('changes_change_seq');
                        QUERY PLAN                        
 Seq Scan on changes  (cost=0.00..323.21 rows=1 width=78)
   Filter: (change = currval('changes_change_seq'::text))
(2 rows)

Explicitly casting the result of currval() to an integer (of any size)
does not seem improve the situation.  Is my expectation unreasonable
that the planner should consider the result of an INTEGER CAST in the
same way it considers a literal integer?

Brandon Craig Rhodes               
Georgia Tech                                            brandon(at)oit(dot)gatech(dot)edu


pgsql-general by date

Next:From: Martin MarquesDate: 2004-02-25 22:08:24
Subject: Re: key = currval('tab_key_seq') choses SEQSCAN?!
Previous:From: Rick GiggerDate: 2004-02-25 21:10:56
Subject: Memory usage

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group