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:
Show quoted text
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