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