Thread: 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
Jeff Amiel <jamiel@istreamimaging.com> writes: > If postgres knows the field is an int8, why do I have to cast it in my query? This is fixed in 8.0. -Doug
Jeff Amiel <jamiel@istreamimaging.com> writes:
> If postgres knows the field is an int8, why do I have to cast it in my query?
As of 8.0 you won't have to anymore.
You don't really want to know why it took us six years to find a
workable solution... suffice it to say that it was harder than you
might think, because of Postgres' extensible approach to datatypes.
regards, tom lane
Outstanding.... thanks all.... Tom Lane wrote: >Jeff Amiel <jamiel@istreamimaging.com> writes: > > >>If postgres knows the field is an int8, why do I have to cast it in my query? >> >> > >As of 8.0 you won't have to anymore. > >You don't really want to know why it took us six years to find a >workable solution... suffice it to say that it was harder than you >might think, because of Postgres' extensible approach to datatypes. > > regards, tom lane > > > >
> > 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? That is a long answer. > Any way I can avoid having to watch for this particular column (and 3 > others in other tables) column in all my queries? 1. Automatically cast all your queries 2. Quote all parameters. > > > > > > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL