On Sat, May 25, 2013 at 7:21 PM, Reuven M. Lerner <reuven@lerner.co.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).