Hello,
we have switched the Postgres JDBC driver from 9.2-1102 to 9.4-1207 to keep up-to-date.
After we did this our nightly regression tests that test for performance failed
because with the new driver the tests were roughly 30% slower than with
the previous driver (some of them even more than 30%).
After enabling pg_stat_statements in your test environment we found out that some
statements read a *much* higher number of blocks for the query.
Some sample figures (from pg_stat_statements)
One statement:
1102: 710 executions, total time: 845ms, shared_blks_hit = 624765
1207: 710 executions, total time: 30538ms, shared_blks_hit = 15046689
Another one:
1102: 8600 executions, total_time: 68ms, shared_blks_hit = 49500
1102: 8600 executions, total_time: 4035ms, shared_blks_hit = 3419644
The tests have been run against Postgres 9.4.5.
As the tests don't involve any DML, table bloat could be excluded for a reason why
the number of blocks is so high.
It does not affect *all* statements - some statements (also with execution counts
substantially larger than 200) run with the same performance.
So we assumed it had something to do with the execution plans, and the only way
the driver could influence that (while the statement being exactly the same),
was through the server side prepared statements.
So we ran the tests using "prepareThreshold=0" and then the tests with 1207
ran with the same performance as with the 1102 driver.
We have not yet looked at the actual execution plans (e.g. by enabling the
auto-explain module) because using "prepareThreshold=0" is fine for us for now.
So my question is:
Is it possible that the 1207 driver does something different when preparing statements
compared to 1102 that would cause Postgres to use a completely different execution plan?
The 9.3 driver behaves like the 9.2 driver, so the change probably occurred from 9.2 to 9.4
(we didn't test all the 9.4 builds, only the latest)
Regards
Thomas