JDBC int8 hack

From: Kyle VanderBeek <kylev(at)yaga(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Cc: peter(at)retep(dot)org(dot)uk
Subject: JDBC int8 hack
Date: 2001-04-03 21:09:33
Message-ID: 20010403140933.A30314@yaga.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-patches

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.

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

Attachment Content-Type Size
int8hack.patch text/plain 472 bytes
SelectPerfPopulate.pl text/plain 602 bytes
SelectPerf.java text/plain 2.5 KB

Browse pgsql-patches by date

  From Date Subject
Next Message Fred Yankowski 2001-04-03 21:18:04 Re: [PORTS] Re: patch for minor Win32 makefile bug
Previous Message Tom Lane 2001-04-03 20:04:54 Re: [PORTS] Re: patch for minor Win32 makefile bug