Обсуждение: JDBC int8 hack
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
Вложения
Can someone comment on this? It appends :int8 to long constants in Java to fix our problem with not using int8 indexes with uncase long values. Is this fixed in 7.1, and therefore the patch isn't needed? > 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
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
Kyle VanderBeek <kylev@yaga.com> writes:
> On Mon, May 07, 2001 at 07:37:16PM -0400, Tom Lane wrote:
>> The problem is still there, but I think this proposed fix is entirely
>> inappropriate. See prior thread.
> And I, of course, still disagree. There is no ill effect to my patch,
> even once the optimizer gets "fixed". There is no problem caused by the
> explicit (transparent) cast being added by the driver.
At the moment, no, *if* JDBC guesses correctly about whether the hack is
applicable (it's a bit of a leap of logic to assume that the datatype on
the client side necessarily tells you what is being used on the server
side). Otherwise the hack could make performance worse.
But my real objection is that hacks have a way of lingering long after
the problem they solve is gone. I'm particularly concerned about the
fact that a backend deficiency is to be solved by hacking JDBC, which
means that (a) it does no good for people using other client interfaces,
and (b) there could be future problems from people using an old JDBC
with a new backend that doesn't need the hack, and maybe actively
doesn't like it.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can someone comment on this? It appends :int8 to long constants in Java
> to fix our problem with not using int8 indexes with uncase long values.
> Is this fixed in 7.1, and therefore the patch isn't needed?
The problem is still there, but I think this proposed fix is entirely
inappropriate. See prior thread.
regards, tom lane
On Mon, May 07, 2001 at 07:37:16PM -0400, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Can someone comment on this? It appends :int8 to long constants in Java > > to fix our problem with not using int8 indexes with uncase long values. > > Is this fixed in 7.1, and therefore the patch isn't needed? > > The problem is still there, but I think this proposed fix is entirely > inappropriate. See prior thread. And I, of course, still disagree. There is no ill effect to my patch, even once the optimizer gets "fixed". There is no problem caused by the explicit (transparent) cast being added by the driver. -- Kyle. "I hate every ape I see, from chimpan-A to chimpan-Z" -- Troy McClure