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.