My last attempt to post this didn't go through since I wasn't a member of
the list, so I'll try again.
There has been some discussion on lists in the past about indecies on INT8
columns not being found/used by the optimizer. This really bit us on the
ass with the application we're writing. I see fixing this is in the
current TODO list. In the mean time, for those using JDBC, a simple
one-line patch can help greatly (see attached). It simply appends
"::int8" to any parameter added to a PreparedStatement via setLong().
To test this, I created a table with 100,000 records using the attached
perl script. Then, I used the attached Java program to perform 1000
SELECTs against this table using the INT8 primary key in the WHERE clause.
I ran 12 runs, alternating between using the stock PostgreSQL JDBC2 driver
and my modified one. The mean time to run this Java program with the
stock driver was 195465 milliseconds. Using my patched driver, it was
1558 milliseconds. Yes: two orders of magnitude faster (this of course
relates to the size of the table being scanned).
Please consider applying my patch to the 7.0 codebase as a stop-gap
measure until such time as the optimizer can be improved to notice
indecies on INT8 columns and cast INT arguments up. At the very least,
this will now be in list archives for people having this problem to find.
I also imagine this idea could be generalized to deal with similar
problems mentioned in the mail archives about INT2.
Thanks.
--
Kyle.
"I hate every ape I see, from chimpan-A to chimpan-Z" -- Troy McClure
