From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il> |
Cc: | List <pgsql-jdbc(at)postgresql(dot)org>, kim+pg(at)alleroedderne(dot)adsl(dot)dk |
Subject: | Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows) |
Date: | 2013-06-25 12:53:05 |
Message-ID: | CADK3HH+coLGO3qe0TrKcQXUTKum-0bZzuBv_iNy1=Zv6AbtAZg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Reuven,
Postgresql version < 9.2 suffers from a problem with bound parameters where
the planner does not choose a good plan with bound parameters. If I were a
betting man I would bet that your linux box is not running version > 9.2
select version() will confirm
Cheers,
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Tue, Jun 25, 2013 at 8:43 AM, Reuven M. Lerner <reuven(at)lerner(dot)co(dot)il>wrote:
> Hi, everyone. About a month ago, I e-mailed this list about a project I'm
> helping which is having hugely different performance results depending on
> whether they use Linux or Windows. Several list members were nice enough
> to offer some answers, but then I disappeared for a while, thanks to travel
> + illness + family + crazy schedules.
>
> Just to recap: We have an application written in Java. When we build the
> SQL query manually, using a StringBuilder, we get similar results on
> Windows and Linux. But when we use the JDBC driver's parameter bindings,
> we find wildly different performance. Specifically:
>
> With binding:
> Linux: 2,233 ms (yes, more than 2 seconds)
> Windows: 232 ms
>
> Without binding:
> Linux: 176 ms
> Windows: 152 ms
>
> The two test pieces of code, which we used to benchmark performance, and
> which work on the table we want to use in production, are at:
>
> https://gist.github.com/**reuven/5858095<https://gist.github.com/reuven/5858095> # without bindings
> https://gist.github.com/**reuven/5858092<https://gist.github.com/reuven/5858092> # with bindings
>
> I believe that the tests were run on localhost, such that the client and
> server were both executing on the same computer.
>
> When we execute the query manually, via psql, we get fast results, on both
> Windows and Linux. Thus, it would seem that something in the
> parameter-binding code in the JDBC driver is somehow interacting poorly
> with Linux.
>
> I'm far from a Java expert, and even less of a Windows guy, so I'm not
> sure what is going on here. Any suggestions as to what we should be
> looking for, or tuning in the PostgreSQL and/or Windows configuration to
> avoid such problems?
>
> Thanks again for any help that you can offer.
>
> Reuven
>
From | Date | Subject | |
---|---|---|---|
Next Message | Reuven M. Lerner | 2013-06-25 12:55:50 | Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows) |
Previous Message | Reuven M. Lerner | 2013-06-25 12:43:38 | Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows) |