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

Dave Cramer

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

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

> Hi, Dave. Thanks for the response. You wrote:
>
> 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.
>>
>
> Is there any way to avoid this, and to give the planner more hints?

No, not really, pg doesn't have hints.

> Is there an option in JDBC that can give us a more direct connection?
>
> Yes but you will have no protection from sql injection. You can use
protocol 2 add protocolVersion=2 to the url

>
>> This does not explain the slowness on linux however. Is postgresql tuned
>> on linux, or windows ?
>>
>
> The tuning suggestions that I've given were pretty plain overall, and
> mostly involved changing the amounts given to shared_buffers and
> effective_cache_size. I know that I gave them suggestions for the Linux
> box, so if any tuning was done, it was actually there. I was quite
> surprised to hear that they were even trying it on Windows, let alone that
> it gave significantly better performance.
>

OK, so 25% memory to shared memory and 75%effective cache, you should also
lower randam page cost as this is very conservative.

>
> On the Linux side, they've switched from ext3 to (non-journaled) ext2, to
> improve performance. Execution is excellent on Linux, except via JDBC and
> bound parameters.
>

This is not advisable, the difference is minimal the risk is great. They
would be better with ex4.

Can you get the result of explain analyze for prepare/execute and the non
prepared statement from psql?

> Any other suggestions regarding the differences between Windows and Linux?
> As you can imagine, this is really puzzling me (er, us).

Are the boxes really the same hardware wise ?

Dave

>
>
> 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
>