Dont let those int8's drive you mad!!

I was stuck for 3 hours today trying to figure out why postgres was doing a seq scan on a primary key/unique index
column.

the statement was innocuous enough....

update transactions set state='O' where trans_id=14332

trans_id was the primary key and also had a unique btree index on it.
No matter what I did, seq_scan....
I vacuum/full/analyzed to no avail.
Then it hit me.  trans_id is an int8.
simply changing the query to:

update transactions set state='O' where trans_id=int8(14332)

Sped it up from 4 milliseconds to .07 milliseconds (and obviously now did an index scan)!!!!
This HAS bitten me before.

Questions:
If postgres knows the field is an int8, why do I have to cast it in my query?
Any way I can avoid having to watch for this particular column (and 3 others in other tables) column in all my queries?







--
Jeff Amiel
Systems/Development Manager
iStream Imaging, an iTeam Company
jamiel@iStreamImaging.com
(262) 796-0925 x1011