Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows)

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
>