Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group