Re: JDBC int8 hack - Mailing list pgsql-patches

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@postgresql.org so that your
> message can get through to the mailing list cleanly

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