9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102
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
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Here are the details: http://stackoverflow.com/a/32300222/1261287
TL;DR: pgjdbc automatically uses server-side prepared statements
since 9.4-1202.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Vladimir Sitnikov schrieb am 11.01.2016 um 13:56:
Here are the details: http://stackoverflow.com/a/32300222/1261287
TL;DR: pgjdbc automatically uses server-side prepared statements
since 9.4-1202.
Hmm, but the previous drivers also did that, at least the documentation suggests that:
https://jdbc.postgresql.org/documentation/92/connect.html#connection-parameters
"The default is five, meaning start using server side prepared statements
on the fifth execution of the same PreparedStatement object"
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
The difference is as follows: 1202 can cache across statement.close()
calls. 1201 can't do that.
In other words, to use server-prepared statements in previous
versions, you had to reuse the *same* PreparedStatement *object* again
and again. As soon as you issue .close() you lose your shiny
server-prepared statement.
Typical applications are written in a form of
PreparedStatement ps = con.prepareStatement(sql);
ps.excuteQuery();
ps.close();
In other words, almost always developers just recreate
PreparedStatement and close it.
The improvement of 1202 was to identify such patterns and use
server-prepared statement in such cases as well.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
@Vladimir but if 1202+ has become smarter than 1102 and reuse prepared
statements more often how come Thomas experience the 1207 to be magnitudes
slower?
Shouldn't it be the other way around?
Med venlig hilsen / Kind regards,
Christian Bjørnbak
Chefudvikler / Lead Developer
TouristOnline A/S
Islands Brygge 43
2300 København S
Denmark
TLF: +45 32888230
Dir. TLF: +45 32888235
2016-01-11 14:05 GMT+01:00 Vladimir Sitnikov <sitnikov.vladimir@gmail.com>:
Show quoted text
The difference is as follows: 1202 can cache across statement.close()
calls. 1201 can't do that.In other words, to use server-prepared statements in previous
versions, you had to reuse the *same* PreparedStatement *object* again
and again. As soon as you issue .close() you lose your shiny
server-prepared statement.Typical applications are written in a form of
PreparedStatement ps = con.prepareStatement(sql);
ps.excuteQuery();
ps.close();In other words, almost always developers just recreate
PreparedStatement and close it.
The improvement of 1202 was to identify such patterns and use
server-prepared statement in such cases as well.
Vladimir--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
It's not that JDBC is slower, it is the plans that are being generated are
slower. I can't imagine why that would even happen.
JDBC doesn't really change the incoming SQL
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
On 11 January 2016 at 08:29, Christian Bjørnbak <cbj@touristonline.dk>
wrote:
Show quoted text
@Vladimir but if 1202+ has become smarter than 1102 and reuse prepared
statements more often how come Thomas experience the 1207 to be magnitudes
slower?Shouldn't it be the other way around?
Med venlig hilsen / Kind regards,
Christian Bjørnbak
Chefudvikler / Lead Developer
TouristOnline A/S
Islands Brygge 43
2300 København S
Denmark
TLF: +45 32888230
Dir. TLF: +45 328882352016-01-11 14:05 GMT+01:00 Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
:The difference is as follows: 1202 can cache across statement.close()
calls. 1201 can't do that.In other words, to use server-prepared statements in previous
versions, you had to reuse the *same* PreparedStatement *object* again
and again. As soon as you issue .close() you lose your shiny
server-prepared statement.Typical applications are written in a form of
PreparedStatement ps = con.prepareStatement(sql);
ps.excuteQuery();
ps.close();In other words, almost always developers just recreate
PreparedStatement and close it.
The improvement of 1202 was to identify such patterns and use
server-prepared statement in such cases as well.
Vladimir--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
I can't imagine why that would even happen.
That can happen in case backend uses "bad plan" for server-prepared statement.
Here are more details:
http://www.postgresql.org/docs/9.4/static/sql-prepare.html
As far as I remember, backend can switch plan on 5th or 6th execution
of server-prepared statement.
PG DOC: If a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. This will occur immediately if the prepared statement has no parameters
In other words, even server-prepared statements can behave differently
from one execution to another.
I've seen a couple of times when a query was fast "the first 5 times",
then backend (9.4) switched to much slower plan.
That happened with exactly the same input value.
The resolution for me was to fix query plan as desired (add offset 0
here and there) so the join order was specific.
On the other hand, I've seen impressive performance improvements for
long queries that take much longer to plan than to execute. Common
wisdom is to hide long SQL into stored procedures (they have
transparent statement cache too), however it can't easily be done for
existing application.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 11 January 2016 at 08:54, Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
wrote:
I can't imagine why that would even happen.
That can happen in case backend uses "bad plan" for server-prepared
statement.
Yes, but we are talking about the same backend with the same SQL, The only
difference is the driver. Is that correct ?
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
Vladimir Sitnikov schrieb am 11.01.2016 um 14:05:
The difference is as follows: 1202 can cache across statement.close()
calls. 1201 can't do that.In other words, to use server-prepared statements in previous
versions, you had to reuse the *same* PreparedStatement *object* again
and again. As soon as you issue .close() you lose your shiny
server-prepared statement.
Ah, thanks.
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
I can confirm, It really behave differently, but not for the same reason
as yours.
In my case, problema is that after optimization at server side, results
get different.
I always work with url parameter:
jdbc:postgresql://dbdevel:5433/mydatabase?prepareThreshold=0
When using prepareThreshold=0, I do avoid the server side prepared
statements.
My be worth to give a try, since would also makes difference for you as
well.
Atenciosamente,
Edson Carlos Ericksson Richter
Em 11/01/2016 11:33, Dave Cramer escreveu:
Show quoted text
It's not that JDBC is slower, it is the plans that are being generated
are slower. I can't imagine why that would even happen.JDBC doesn't really change the incoming SQL
Dave Cramer
davec@postgresintl.com <mailto:davec@postgresintl.com>
www.postgresintl.com <http://www.postgresintl.com>On 11 January 2016 at 08:29, Christian Bjørnbak <cbj@touristonline.dk
<mailto:cbj@touristonline.dk>> wrote:@Vladimir but if 1202+ has become smarter than 1102 and reuse
prepared statements more often how come Thomas experience the 1207
to be magnitudes slower?Shouldn't it be the other way around?
Med venlig hilsen / Kind regards,
Christian Bjørnbak
Chefudvikler / Lead Developer
TouristOnline A/S
Islands Brygge 43
2300 København S
Denmark
TLF: +45 32888230 <tel:%2B45%2032888230>
Dir. TLF: +45 32888235 <tel:%2B45%2032888235>2016-01-11 14:05 GMT+01:00 Vladimir Sitnikov
<sitnikov.vladimir@gmail.com <mailto:sitnikov.vladimir@gmail.com>>:The difference is as follows: 1202 can cache across
statement.close()
calls. 1201 can't do that.In other words, to use server-prepared statements in previous
versions, you had to reuse the *same* PreparedStatement
*object* again
and again. As soon as you issue .close() you lose your shiny
server-prepared statement.Typical applications are written in a form of
PreparedStatement ps = con.prepareStatement(sql);
ps.excuteQuery();
ps.close();In other words, almost always developers just recreate
PreparedStatement and close it.
The improvement of 1202 was to identify such patterns and use
server-prepared statement in such cases as well.
Vladimir--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org
<mailto:pgsql-jdbc@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
In my case, problema is that after optimization at server side, results get different.
Do you mean "wrong results" kind of issue?
which driver version are you using?
Generally speaking, it is worth submitting "bad performance when using
prepared statements" issues to PostgreSQL hackers team.
If just a couple of SQLs behave badly due to server-prepared
statements, then it might make sense just tune the statements in
question.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Dave Cramer schrieb am 11.01.2016 um 14:56:
On 11 January 2016 at 08:54, Vladimir Sitnikov <sitnikov.vladimir@gmail.com <mailto:sitnikov.vladimir@gmail.com>> wrote:
I can't imagine why that would even happen.
That can happen in case backend uses "bad plan" for server-prepared statement.
Yes, but we are talking about the same backend with the same SQL, The only difference is the driver. Is that correct ?
Correct.
Identical SQL, identical query parameters, identical order of execution, identical backend, just a different driver version
Thomas
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 11 January 2016 at 09:44, Thomas Kellerer <spam_eater@gmx.net> wrote:
Dave Cramer schrieb am 11.01.2016 um 14:56:
On 11 January 2016 at 08:54, Vladimir Sitnikov <
sitnikov.vladimir@gmail.com <mailto:sitnikov.vladimir@gmail.com>> wrote:
I can't imagine why that would even happen.
That can happen in case backend uses "bad plan" for server-prepared
statement.
Yes, but we are talking about the same backend with the same SQL, The
only difference is the driver. Is that correct ?
Correct.
Identical SQL, identical query parameters, identical order of execution,
identical backend, just a different driver version
Is it possible to get server logs ?
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
Show quoted text
Thomas
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Dave Cramer schrieb am 11.01.2016 um 15:46:
On 11 January 2016 at 09:44, Thomas Kellerer <spam_eater@gmx.net <mailto:spam_eater@gmx.net>> wrote:
Yes, but we are talking about the same backend with the same SQL, The only difference is the driver. Is that correct ?
Correct.
Identical SQL, identical query parameters, identical order of execution, identical backend, just a different driver version
Is it possible to get server logs ?
If that is helpful, I think I can arrange that.
Which logging settings would you like to have for that?
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
I just want to see what the server sees for SQL statements with the
different drivers.
The easiest way I find is to set log_min_duration to 0 which logs
everything and then you can change it back
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
On 11 January 2016 at 09:54, Thomas Kellerer <spam_eater@gmx.net> wrote:
Show quoted text
Dave Cramer schrieb am 11.01.2016 um 15:46:
On 11 January 2016 at 09:44, Thomas Kellerer <spam_eater@gmx.net
<mailto:spam_eater@gmx.net>> wrote:
Yes, but we are talking about the same backend with the same SQL,
The only difference is the driver. Is that correct ?
Correct.
Identical SQL, identical query parameters, identical order of
execution, identical backend, just a different driver version
Is it possible to get server logs ?
If that is helpful, I think I can arrange that.
Which logging settings would you like to have for that?
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Thomas, as far as I see, you have offending queries and bind values
for them, don't you?
Can you please perform the following dance in psql or pgadmin?
PREPARE prep1207(number,number,number,number,number,number) as -- <--
data types for the bind variables
select .. $1, ... where id=$2; -- <-- your query here
EXPLAIN ANALYZE EXECUTE prep1207(1,2,3,42,100500); -- <-- values for
bind variables
EXPLAIN ANALYZE EXECUTE prep1207(1,2,3,42,100500); -- <-- values for
bind variables
EXPLAIN ANALYZE EXECUTE prep1207(1,2,3,42,100500); -- <-- values for
bind variables
EXPLAIN ANALYZE EXECUTE prep1207(1,2,3,42,100500); -- <-- values for
bind variables
EXPLAIN ANALYZE EXECUTE prep1207(1,2,3,42,100500); -- <-- values for
bind variables
Ideally, it should reproduce the issue. In other words, 5th or 6th
execution should flip to bad plan.
Then share the plan/query.
If table names cannot be made public, you might want try
http://explain.depesz.com/ to anonymize the plan.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Dave Cramer schrieb am 11.01.2016 um 15:56:
I just want to see what the server sees for SQL statements with the different drivers.
The easiest way I find is to set log_min_duration to 0 which logs everything and then you can change it back
I'll see what I can do.
We will have to anonymize the statements somehow because for legal reasons I can't disclose them.
Thomas
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
I'm using 1201 driver. No matter JDK 7 or JDK 8.
Big real issue for me: after third query, the prepareThresold is hit,
and timezone data is converted "on the fly" to "timezone without
daylights" for timestamp fields.
Data is stored in database as "2015-09-30 00:00:00", in the 1st to 3rd
query returns with "2015-09-30 00:00:00", and the 4º and on returns
"2015-09-29 23:00:00".
Disabling with prepareThreshold=0 solved the problem once for all.
I had no further time to investigate and/or produce a case for asking
for a fix.
Regards,
Atenciosamente,
Edson Carlos Ericksson Richter
Em 11/01/2016 12:14, Vladimir Sitnikov escreveu:
In my case, problema is that after optimization at server side, results get different.
Do you mean "wrong results" kind of issue?
which driver version are you using?Generally speaking, it is worth submitting "bad performance when using
prepared statements" issues to PostgreSQL hackers team.
If just a couple of SQLs behave badly due to server-prepared
statements, then it might make sense just tune the statements in
question.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Warning:
This only happens if your country has Daylights Saving Time active.
In Brazil, default timezone is GMT-03:00. Currently in DST, the timezone
is GMT-02:00.
This seems to affect some kind of value conversion, and subtract 1 hour
from timestamp values.
Atenciosamente,
Edson Carlos Ericksson Richter
Em 11/01/2016 14:15, Edson Richter escreveu:
I'm using 1201 driver. No matter JDK 7 or JDK 8.
Big real issue for me: after third query, the prepareThresold is hit,
and timezone data is converted "on the fly" to "timezone without
daylights" for timestamp fields.
Data is stored in database as "2015-09-30 00:00:00", in the 1st to 3rd
query returns with "2015-09-30 00:00:00", and the 4º and on returns
"2015-09-29 23:00:00".Disabling with prepareThreshold=0 solved the problem once for all.
I had no further time to investigate and/or produce a case for asking
for a fix.Regards,
Atenciosamente,
Edson Carlos Ericksson Richter
Em 11/01/2016 12:14, Vladimir Sitnikov escreveu:
In my case, problema is that after optimization at server side,
results get different.Do you mean "wrong results" kind of issue?
which driver version are you using?Generally speaking, it is worth submitting "bad performance when using
prepared statements" issues to PostgreSQL hackers team.
If just a couple of SQLs behave badly due to server-prepared
statements, then it might make sense just tune the statements in
question.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Can you put more details on the APIs you use to retrieve the data?
.getTimestamp(int)?
.getTimestamp(int, Calendar)?
.getString(int)?
.getObject(int)?
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
