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 [EMAIL PROTECTED] (262) 796-0925 x1011
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
