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
On Tue, Jun 25, 2013 at 8:43 AM, Reuven M. Lerner <reuven@lerner.co.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:
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?