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

The problem lies in the fact that JDBC uses an unnamed statement to do the
query. The planner can't plan these very well as the parameters are not
available at plan time.

This does not explain the slowness on linux however. Is postgresql tuned on
linux, or windows ?

In order to test this in psql you would have to use a prepared statement
such as

Prepare foo as select * from bar where col = $1
execute foo('blah')

http://www.postgresql.org/docs/current/static/sql-prepare.html

You can also do explain analyze on execute foo ('blah') to see the
difference in the plans

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Sat, May 25, 2013 at 6:52 PM, Reuven M. Lerner <reuven(at)lerner(dot)co(dot)il>wrote:

> Hi, everyone. A project I'm assisting is connecting to PostgreSQL (9.2)
> via JDBC. We're experiencing extremely slow query execution, for
> precisely the same queries, depending on (a) operating system and (b)
> whether we use bind parameters.
>
> We're using two different Red Hat Linux systems (one running RHEL 5, and
> the other RHEL 6). The machines have 16 GB and 64 GB RAM, respectively,
> with 4+ GB allocated to shared buffers on each one. However, given that
> the performance difference is between psql and JDBC, I doubt that these are
> significant factors.
>
> We have two different Java programs (which do 1,000 inserts), each of
> which we've run 10 times on each of Windows and Linux. The average time
> needed for running each program is:
>
> Windows: 232 ms with binding, 152 ms without binding
> Linux: 2,233 ms (yes, 2.233 *seconds*) with binding, 176 ms without binding
>
> We've also run the same 1,000 inserts via psql on a remote machine, and
> the timing was quite fast (i.e., like what we saw on the Windows machine).
>
> The combination of binding + Linux seems to be the problematic one here,
> but I'm at a loss for why.
>
> I can provide the test programs if they'll help, but I'm first wondering
> where the problem might lie.
>
> Any suggestions or ideas? What additional information (e.g., the test
> programs) would help to track this problem down?
>
> Thanks in advance,
>
> Reuven
>
> --
> Reuven M. Lerner -- Web development, consulting, and training
> Mobile: +972-54-496-8405 * US phone: 847-230-9795
> http://lerner.co.il * Skype/AIM: reuvenlerner
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-jdbc<http://www.postgresql.org/mailpref/pgsql-jdbc>
>