Re: JDBC int8 hack

OK, I talked to Tom Lane and he says the problem still exists in 7.1,
but that this patch is more of a workaround, which I think you will
agree with. Tom would like to see this problem corrected rather than
adding hacks to work around it.

> 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

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026