PostgreSQL Tuning: BIGINT
I luckily stumbled upon this blog about PostgreSQL indexing. I never really considered that it would be my database holding me back much, but it seems I should really analyze the queries my app server is creating.
Basically, searching against
BIGINT keys doesn't take advantage of the index, since it has to cast internally from
INT4 (integer) in the query to an
INT8 (bigint). I'm using
java.lang.Long in my Java code for my keys, so I wouldn't want to just drop back to
integer in the database -- I could get away with it for now, but it's not very future-proof.
In spite of
NUMERIC(18,0) index scans being slower than
BIGINT index scans, they'll beat out sequential scans of
BIGINT, which is what my blog is doing now. I'm testing it out in my development copy, but I'll probably have to test it in the production version of my blog to get a real feel for it.
At this point, I must settle for
NUMERIC(18,0) instead of casting my input parameters to bigint, since I know of no way to tune my queries created by JBoss' CMP implementation.
Update (6 July 2004): I haven't done any real benchmarks, but I really must wonder how to really test the plan for prepared statements in Postgres' JDBC driver. Many of the links I had read referred to Postgres 7.3, but I'm running 7.4 these days. Using Numeric also didn't feel much faster, so I'm trying Integer now, but I may revert back to Bigint down the road, since I really am using Long in my java code.