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

Here are the details: http://stackoverflow.com/a/32300222/1261287

TL;DR: pgjdbc automatically uses server-side prepared statements
since 9.4-1202.

Vladimir

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"

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

@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(dot)vladimir(at)gmail(dot)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(at)postgresql(dot)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(at)postgresintl(dot)com
www.postgresintl.com

On 11 January 2016 at 08:29, Christian Bjørnbak <cbj(at)touristonline(dot)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
> Dir. TLF: +45 32888235
>
> 2016-01-11 14:05 GMT+01:00 Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)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(at)postgresql(dot)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

On 11 January 2016 at 08:54, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)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(at)postgresintl(dot)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.

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:
> 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(at)postgresintl(dot)com <mailto:davec(at)postgresintl(dot)com>
> www.postgresintl.com <http://www.postgresintl.com>
>
> On 11 January 2016 at 08:29, Christian Bjørnbak <cbj(at)touristonline(dot)dk
> <mailto:cbj(at)touristonline(dot)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(dot)vladimir(at)gmail(dot)com <mailto:sitnikov(dot)vladimir(at)gmail(dot)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(at)postgresql(dot)org
> <mailto:pgsql-jdbc(at)postgresql(dot)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

Dave Cramer schrieb am 11.01.2016 um 14:56:
> On 11 January 2016 at 08:54, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com <mailto:sitnikov(dot)vladimir(at)gmail(dot)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

On 11 January 2016 at 09:44, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:

> Dave Cramer schrieb am 11.01.2016 um 14:56:
> > On 11 January 2016 at 08:54, Vladimir Sitnikov <
> sitnikov(dot)vladimir(at)gmail(dot)com <mailto:sitnikov(dot)vladimir(at)gmail(dot)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(at)postgresintl(dot)com
www.postgresintl.com

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

Dave Cramer schrieb am 11.01.2016 um 15:46:
>
> On 11 January 2016 at 09:44, Thomas Kellerer <spam_eater(at)gmx(dot)net <mailto:spam_eater(at)gmx(dot)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?

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(at)postgresintl(dot)com
www.postgresintl.com

On 11 January 2016 at 09:54, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:

> Dave Cramer schrieb am 11.01.2016 um 15:46:
> >
> > On 11 January 2016 at 09:44, Thomas Kellerer <spam_eater(at)gmx(dot)net
> <mailto:spam_eater(at)gmx(dot)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(at)postgresql(dot)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

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

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

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

Can you put more details on the APIs you use to retrieve the data?
.getTimestamp(int)?
.getTimestamp(int, Calendar)?
.getString(int)?
.getObject(int)?
Vladimir

Actually, I don't know.

I'm using EclipseLink as layer before the database.
Since my Java classes are annotated with @Temporal(TIMESTAMP), I do
understand EclipseLink will be using getTimeStamp - but I can't tell for
sure.
Also, when looking data using "pgAdmin" timestamp data appears normal.
But if querying data using NetBeans SQL explorer or ART (reporting
tool), I get exactly same issue as with EclipseLink.
I suppose NetBeans and ART use getObject, but it is mere speculation
from my side.

As said before, since prepareThreshold=0 solved my problem, and I have a
time shortage last few months, I'll try to investigate further in near
future.
Sorry not being of great help right now.

Regards,

Atenciosamente,

Edson Carlos Ericksson Richter

Em 11/01/2016 17:50, Vladimir Sitnikov escreveu:
> Can you put more details on the APIs you use to retrieve the data?
> .getTimestamp(int)?
> .getTimestamp(int, Calendar)?
> .getString(int)?
> .getObject(int)?
> Vladimir
>
>

What is the field type?
timestamp? timestamp with time zone?

Are you using the latest java timezone updates? (tzupdater)

>As said before, since prepareThreshold=0 solved my problem

Then binaryTransferDisable=DATE,DATE_ARRAY,TIMESTAMP,TIMESTAMP_ARRAY,TIMESTAMPTZ,TIMESTAMPTZ_ARRAY,TIME,TIME_ARRAY,TIMETZ,TIMETZ_ARRAY
connection property might be better workaround.

Vladimir

This is really interesting.
I would give a try.

Regards,

Atenciosamente,

Edson Carlos Ericksson Richter

Em 11/01/2016 18:42, Vladimir Sitnikov escreveu:
> What is the field type?
> timestamp? timestamp with time zone?
>
> Are you using the latest java timezone updates? (tzupdater)
>
>> As said before, since prepareThreshold=0 solved my problem
> Then binaryTransferDisable=DATE,DATE_ARRAY,TIMESTAMP,TIMESTAMP_ARRAY,TIMESTAMPTZ,TIMESTAMPTZ_ARRAY,TIME,TIME_ARRAY,TIMETZ,TIMETZ_ARRAY
> connection property might be better workaround.
>
> Vladimir

According to EclipseLink's sources, the type of the column must be
DATE and you are using java.sql.Date.
Is that correct?

https://github.com/eclipse/eclipselink.runtime/blob/fc5d47ec1b26dea932a26ae152de347da8ec62f9/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/PostgreSQLPlatform.java#L329
Vladimir

Here's the fix:
https://github.com/pgjdbc/pgjdbc/commit/642b48a787098a6c5a068710bdbbf9f1b11f3aac

You can try it via Maven Central's <version>9.4.1208-SNAPSHOT</version>

I wonder if there is anybody from Portugal on the list?
Can you explain how you represent 2000-03-26 00:00:00?

As far as I understand, somewhere near 26th March Atlantic/Azores
skips from 00:00:00 to 01:00:00.
See http://www.timeanddate.com/time/change/portugal/ponta-delgada-azores
for 2000-03-26 00:00:00 -> 01:00:00

This causes troubles since java.util.Date is supposed to be 2000-03-26
00:00:00 Atlantic/Azores, however there is no such thing like
"2000-03-26 00:00:00 Atlantic/Azores" in Java.
If you try to use calendar.set(Calendar.HOUR_OF_DAY, 0), then you get
01:00:00 back.

This caused me to add exception to the expected results:
https://github.com/pgjdbc/pgjdbc/commit/642b48a787098a6c5a068710bdbbf9f1b11f3aac#diff-b318f589843d60530d1eada03263c1b0R895

Vladimir

Even though the particular case with DATE is fixed, I would still
recommend to consider timestamptz data type.

DATE has inherent timezone problems, so if you are storing time
instants, then you'd better operate on timestamptz.
See "2000-03-26 00:00:00 Atlantic/Azores problem" above.

Vladimir

> Date: Tue, 12 Jan 2016 01:31:37 +0300
> Subject: Re: Driver behaves differently with prepareThreshold and timestamp fields when daylights is active (was Re: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102)
> From: sitnikov(dot)vladimir(at)gmail(dot)com
> To: edsonrichter(at)hotmail(dot)com; pgsql-jdbc(at)postgresql(dot)org
>
> Here's the fix:
> https://github.com/pgjdbc/pgjdbc/commit/642b48a787098a6c5a068710bdbbf9f1b11f3aac
>
> You can try it via Maven Central's <version>9.4.1208-SNAPSHOT</version>

Uau, that was fast!!!
I'll try to download a build, and then test the fix.

>
>
> I wonder if there is anybody from Portugal on the list?
> Can you explain how you represent 2000-03-26 00:00:00?

I'm on Brazil, and we are on DST right now. In Brazil, "2000-03-26 00:00:00" would be better shown as "26/03/2000 00:00:00".

Regards,

Edson

>
> As far as I understand, somewhere near 26th March Atlantic/Azores
> skips from 00:00:00 to 01:00:00.
> See http://www.timeanddate.com/time/change/portugal/ponta-delgada-azores
> for 2000-03-26 00:00:00 -> 01:00:00
>
> This causes troubles since java.util.Date is supposed to be 2000-03-26
> 00:00:00 Atlantic/Azores, however there is no such thing like
> "2000-03-26 00:00:00 Atlantic/Azores" in Java.
> If you try to use calendar.set(Calendar.HOUR_OF_DAY, 0), then you get
> 01:00:00 back.
>
> This caused me to add exception to the expected results:
> https://github.com/pgjdbc/pgjdbc/commit/642b48a787098a6c5a068710bdbbf9f1b11f3aac#diff-b318f589843d60530d1eada03263c1b0R895
>
> Vladimir

> Is it possible to get server logs ?

I have picked out one of the statements that suffered from this and sanitized the logfile.

http://sql-workbench.net/pg_jdbc_94.log

The complete statement is at the top of the file and in the messages themselves,
I have replaced each occurrence of the statement with "select ...."

The interesting thing (at least for me) is that the first few executions of the
server side statement have pretty much the same runtime as the ones before the prepare.
And then suddenly the runtime shoots through the rough going up from 1ms to 40ms or even 60ms

Regards
Thomas

>And then suddenly the runtime shoots through the rough going up from 1ms to 40ms or even 60ms

Can it be data-dependent?
Can you try using the same input values for multiple executions?

Vladimir

Vladimir Sitnikov schrieb am 12.01.2016 um 11:44:
>> And then suddenly the runtime shoots through the rough going up from 1ms to 40ms or even 60ms
>
> Can it be data-dependent?

Apparently the server chooses a bad execution plan when a server side prepared
statement is used - it is very likely because of the parameter placeholders
that don't allow Postgres to evaluate the correct cardinality and it
creates a "one size fits all plan"

My guess is that the plan that is generated using the prepared statement only
works for some input values, but not for all (a problem that Oracle has suffered
from for ages as well). As I have written previously we didn't bother to enable the
auto-explain module to see where Postgres goes wrong in the planning because using
prepareThreshold=0 is good enough for us.

Given my experience with bad plans for parametrized statements in Oracle I think it is
definitely the better choice to not use server side prepared statements - at least for
this application.

> Can you try using the same input values for multiple executions?

The tests we are running are pretty much pre-defined. Re-working them would probably be a bigger deal.
Plus I am not directly involved in that project, so I am actually relaying everything here ;)

Regards
Thomas

>My guess is that the plan that is generated using the prepared statement only
works for some input values, but not for all (a problem that Oracle has suffered
from for ages as well).

I think the problem is as follows:
1) During initial runs in server-prepared mode PG still uses exact
values to plan the query
2) It turns out that for certain conditions PG properly understands
that certain conditions are bad.
I bet $1 = '180', $2 = '1' returns lots of rows, thus backend chooses
to start with $3 = '7728'.
3) Later backend creates generic plan. That one does not include
knowledge of exact $1 and &2 values. Thus backend estimates the
cardinality differently.
That results a plan flip.

Note: in Oracle (9-10) bind peeking worked in another way.
There, a plan built for the first bind values was reused for all the
subsequent executions.
That makes more sense for me since that gives at least some stability
(it avoids sudden plan flips).

I'll try to file a case to hackers list to check what they say on the plan flip.

PS. The sad thing is JDBC does have room for "SQL-injection-safe,
non-server-prepared query". In other words, plain java.sql.Statement
does not have "setXXX" methods, and PreparedStatement has no
user-provided API to convey "please, do not use server-prepared
statement as the plan is very data-dependent".

PPS. I do not think the plan is data-dependent in the particular case.
I bet the same plan works for all the values that particular
application uses. It is just PG's fault that plan flip happens.

Vladimir

Actually around 9.2 Tom Lane added the ability to have multiple plans for
prepared statements, specifically

Allow the planner to generate custom plans for specific parameter values
even when using prepared statement

What version of the server are you using ?

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 12 January 2016 at 06:26, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>
wrote:

> >My guess is that the plan that is generated using the prepared statement
> only
> works for some input values, but not for all (a problem that Oracle has
> suffered
> from for ages as well).
>
> I think the problem is as follows:
> 1) During initial runs in server-prepared mode PG still uses exact
> values to plan the query
> 2) It turns out that for certain conditions PG properly understands
> that certain conditions are bad.
> I bet $1 = '180', $2 = '1' returns lots of rows, thus backend chooses
> to start with $3 = '7728'.
> 3) Later backend creates generic plan. That one does not include
> knowledge of exact $1 and &2 values. Thus backend estimates the
> cardinality differently.
> That results a plan flip.
>
> Note: in Oracle (9-10) bind peeking worked in another way.
> There, a plan built for the first bind values was reused for all the
> subsequent executions.
> That makes more sense for me since that gives at least some stability
> (it avoids sudden plan flips).
>
> I'll try to file a case to hackers list to check what they say on the plan
> flip.
>
> PS. The sad thing is JDBC does have room for "SQL-injection-safe,
> non-server-prepared query". In other words, plain java.sql.Statement
> does not have "setXXX" methods, and PreparedStatement has no
> user-provided API to convey "please, do not use server-prepared
> statement as the plan is very data-dependent".
>
> PPS. I do not think the plan is data-dependent in the particular case.
> I bet the same plan works for all the values that particular
> application uses. It is just PG's fault that plan flip happens.
>
> Vladimir
>
>
> --
> 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
>

I'm using "PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by
gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit"

Vladimir

We have an interesting problem, and the reporter has been kind enough to
provide logs for which we can't explain.

I'd be interested to hear any plausible explanations for a prepared plan
suddenly going from 2ms to 60ms for the same input values ?

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

---------- Forwarded message ----------
From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Date: 12 January 2016 at 04:03
Subject: [JDBC] Re: 9.4-1207 behaves differently with server side prepared
statements compared to 9.2-1102
To: pgsql-jdbc(at)postgresql(dot)org

> Is it possible to get server logs ?

I have picked out one of the statements that suffered from this and
sanitized the logfile.

http://sql-workbench.net/pg_jdbc_94.log

The complete statement is at the top of the file and in the messages
themselves,
I have replaced each occurrence of the statement with "select ...."

The interesting thing (at least for me) is that the first few executions of
the
server side statement have pretty much the same runtime as the ones before
the prepare.
And then suddenly the runtime shoots through the rough going up from 1ms to
40ms or even 60ms

Regards
Thomas

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

On 12/01/16 13:00, Dave Cramer wrote:
> We have an interesting problem, and the reporter has been kind enough to
> provide logs for which we can't explain.
>
> I'd be interested to hear any plausible explanations for a prepared plan
> suddenly going from 2ms to 60ms for the same input values ?

This is a new feature in 9.2, where on the fifth (or sixth, not sure)
execution the planner might choose to use a generic plan. From the 9.2
release notes (though I'm fairly certain this is documented somewhere in
the manual as well):

In the past, a prepared statement always had a single "generic" plan
that was used for all parameter values, which was frequently much
inferior to the plans used for non-prepared statements containing
explicit constant values. Now, the planner attempts to generate custom
plans for specific parameter values. A generic plan will only be used
after custom plans have repeatedly proven to provide no benefit. This
change should eliminate the performance penalties formerly seen from use
of prepared statements (including non-dynamic statements in PL/pgSQL).

.m

Vladimir Sitnikov wrote:
> >My guess is that the plan that is generated using the prepared statement only
> works for some input values, but not for all (a problem that Oracle has suffered
> from for ages as well).
>
> I think the problem is as follows:
> 1) During initial runs in server-prepared mode PG still uses exact
> values to plan the query
> 2) It turns out that for certain conditions PG properly understands
> that certain conditions are bad.
> I bet $1 = '180', $2 = '1' returns lots of rows, thus backend chooses
> to start with $3 = '7728'.
> 3) Later backend creates generic plan. That one does not include
> knowledge of exact $1 and &2 values. Thus backend estimates the
> cardinality differently.
> That results a plan flip.
>
> Note: in Oracle (9-10) bind peeking worked in another way.
> There, a plan built for the first bind values was reused for all the
> subsequent executions.
> That makes more sense for me since that gives at least some stability
> (it avoids sudden plan flips).
>
> I'll try to file a case to hackers list to check what they say on the plan flip.

I didn't look at the specific query, but I have seen cases like that before.

During the first 5 executions, PostgreSQL generates a specific plan
and remembers the *estimated* cost.
After that, the cost for the generic plan (with $1, $2, ...) is
estimated and compared to the average of the estimated costs of
the previous specific plans.
If the generic plan is no worse, it is used from then on.

The problem is that only estimates are compared.
The performance drop from the sixth execution on usually means that
the cost estimate for the generic plan is off.

It would be helpful to create a prepared statement, and on the sixth
execution capture the output of
EXPLAIN (ANALYZE, BUFFERS) EXECUTE mystmt(args);

Analyzing that should help you find out where things go wrong.

Yours,
Laurenz Albe

Yes, we are aware of the change from the 5th to the 6th iteration. This is
not the problem. The 5th, and 6th iteration are pretty close, the problem
occurs further down

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 12 January 2016 at 07:15, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

> Vladimir Sitnikov wrote:
> > >My guess is that the plan that is generated using the prepared
> statement only
> > works for some input values, but not for all (a problem that Oracle has
> suffered
> > from for ages as well).
> >
> > I think the problem is as follows:
> > 1) During initial runs in server-prepared mode PG still uses exact
> > values to plan the query
> > 2) It turns out that for certain conditions PG properly understands
> > that certain conditions are bad.
> > I bet $1 = '180', $2 = '1' returns lots of rows, thus backend chooses
> > to start with $3 = '7728'.
> > 3) Later backend creates generic plan. That one does not include
> > knowledge of exact $1 and &2 values. Thus backend estimates the
> > cardinality differently.
> > That results a plan flip.
> >
> > Note: in Oracle (9-10) bind peeking worked in another way.
> > There, a plan built for the first bind values was reused for all the
> > subsequent executions.
> > That makes more sense for me since that gives at least some stability
> > (it avoids sudden plan flips).
> >
> > I'll try to file a case to hackers list to check what they say on the
> plan flip.
>
> I didn't look at the specific query, but I have seen cases like that
> before.
>
> During the first 5 executions, PostgreSQL generates a specific plan
> and remembers the *estimated* cost.
> After that, the cost for the generic plan (with $1, $2, ...) is
> estimated and compared to the average of the estimated costs of
> the previous specific plans.
> If the generic plan is no worse, it is used from then on.
>
> The problem is that only estimates are compared.
> The performance drop from the sixth execution on usually means that
> the cost estimate for the generic plan is off.
>
> It would be helpful to create a prepared statement, and on the sixth
> execution capture the output of
> EXPLAIN (ANALYZE, BUFFERS) EXECUTE mystmt(args);
>
> Analyzing that should help you find out where things go wrong.
>
> Yours,
> Laurenz Albe
>
> --
> 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
>

Hi Marko,

Interesting so why would it choose a worse plan at that point ? Why would
it change at all if the current plan is working well ?

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 12 January 2016 at 07:15, Marko Tiikkaja <marko(at)joh(dot)to> wrote:

> On 12/01/16 13:00, Dave Cramer wrote:
>
>> We have an interesting problem, and the reporter has been kind enough to
>> provide logs for which we can't explain.
>>
>> I'd be interested to hear any plausible explanations for a prepared plan
>> suddenly going from 2ms to 60ms for the same input values ?
>>
>
> This is a new feature in 9.2, where on the fifth (or sixth, not sure)
> execution the planner might choose to use a generic plan. From the 9.2
> release notes (though I'm fairly certain this is documented somewhere in
> the manual as well):
>
> In the past, a prepared statement always had a single "generic" plan that
> was used for all parameter values, which was frequently much inferior to
> the plans used for non-prepared statements containing explicit constant
> values. Now, the planner attempts to generate custom plans for specific
> parameter values. A generic plan will only be used after custom plans have
> repeatedly proven to provide no benefit. This change should eliminate the
> performance penalties formerly seen from use of prepared statements
> (including non-dynamic statements in PL/pgSQL).
>
>
> .m
>

On 12/01/16 11:26, Vladimir Sitnikov wrote:
>> My guess is that the plan that is generated using the prepared statement only
> works for some input values, but not for all (a problem that Oracle has suffered
> from for ages as well).
>
> I think the problem is as follows:
> 1) During initial runs in server-prepared mode PG still uses exact
> values to plan the query
Most types in the driver map directly but date time parameters are
special. There may be others treated as a special case.

I think you'll find initial executions (Bind) use the paramater type
org.postgresql.core.Oid.UNSPECIFIED for the date time.
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L1426
> 2) It turns out that for certain conditions PG properly understands
> that certain conditions are bad.
> I bet $1 = '180', $2 = '1' returns lots of rows, thus backend chooses
> to start with $3 = '7728'.
At the threshold a ParameterDescribe is issued by the front end. The
back end returns the actual type used to store the parameter. The front
end stores the updated parameter type information.
> 3) Later backend creates generic plan. That one does not include
> knowledge of exact $1 and &2 values. Thus backend estimates the
> cardinality differently.
Subsequent Bind messages use the replaced parameter type. The back end
switches to a new plan using the updated type information.

I could be wrong on this as the codebase has changed dramatically in
recent weeks.

Jeremy
> That results a plan flip.
>
> Note: in Oracle (9-10) bind peeking worked in another way.
> There, a plan built for the first bind values was reused for all the
> subsequent executions.
> That makes more sense for me since that gives at least some stability
> (it avoids sudden plan flips).
>
> I'll try to file a case to hackers list to check what they say on the plan flip.
>
> PS. The sad thing is JDBC does have room for "SQL-injection-safe,
> non-server-prepared query". In other words, plain java.sql.Statement
> does not have "setXXX" methods, and PreparedStatement has no
> user-provided API to convey "please, do not use server-prepared
> statement as the plan is very data-dependent".
>
> PPS. I do not think the plan is data-dependent in the particular case.
> I bet the same plan works for all the values that particular
> application uses. It is just PG's fault that plan flip happens.
>
> Vladimir
>
>

--
Jeremy Whiting
Senior Software Engineer, JBoss Performance Team
Red Hat

------------------------------------------------------------
Registered Address: RED HAT UK LIMITED, 64 Baker Street, 4th Floor, Paddington. London. United Kingdom W1U 7DF
Registered in UK and Wales under Company Registration No. 3798903 Directors: Michael Cunningham (US), Charles Peters (US), Matt Parson (US) and Michael O'Neill(Ireland)

Jeremy,

The issue is easily reproducible in plain psql.
It has nothing to do with jdbc driver.
It has nothing to do with date-time either.

> I think you'll find initial executions (Bind) use the paramater type org.postgresql.core.Oid.UNSPECIFIED for the date time.

Does $1 = '180', $2 = '1' or $3 = '7728' look like a date-time value?

Vladimir

Here's the simplified testcase:
https://gist.github.com/vlsi/df08cbef370b2e86a5c1

It reproduces the problem in both 9.4.4 and 9.5rc1.
It is reproducible via both psql and pgjdbc.

I use a single table, however my production case includes a join of
two tables and the query is like
select ... from foo, bar where foo.skewed=? and bar.non_skewed=? and
foo.bar_id=bar.id

Note: my application _always_ sends *the same* *bad* value for skewed
column (it effectively is used as a filtering column in the particular
query).
Unfortunately, on 6th execution backend switches to the plan that uses
skewed index access.

Is it something that can be fixed/improved?

Good plan (the first 5 executions):
Index Scan using non_skewed__flipper on plan_flipper
(cost=0.43..42.77 rows=10 width=113) (actual time=0.030..0.072 rows=10
loops=1)
Index Cond: (non_skewed = 42)
Filter: (skewed = 0)
Rows Removed by Filter: 10
Buffers: shared hit=20 read=3
Execution time: 0.094 ms

Bad plan (all the subsequent executions):
Index Scan using skewed__flipper on plan_flipper (cost=0.43..6.77
rows=1 width=113) (actual time=0.067..355.867 rows=10 loops=1)
Index Cond: (skewed = $1)
Filter: (non_skewed = $2)
Rows Removed by Filter: 999990
Buffers: shared hit=18182 read=2735
Execution time: 355.901 ms

Vladimir

Vladimir Sitnikov wrote:
> Here's the simplified testcase:
> https://gist.github.com/vlsi/df08cbef370b2e86a5c1
>
> It reproduces the problem in both 9.4.4 and 9.5rc1.
> It is reproducible via both psql and pgjdbc.
>
> I use a single table, however my production case includes a join of
> two tables and the query is like
> select ... from foo, bar where foo.skewed=? and bar.non_skewed=? and
> foo.bar_id=bar.id
>
> Note: my application _always_ sends *the same* *bad* value for skewed
> column (it effectively is used as a filtering column in the particular
> query).
> Unfortunately, on 6th execution backend switches to the plan that uses
> skewed index access.
>
> Is it something that can be fixed/improved?
>
> Good plan (the first 5 executions):
> Index Scan using non_skewed__flipper on plan_flipper
> (cost=0.43..42.77 rows=10 width=113) (actual time=0.030..0.072 rows=10
> loops=1)
> Index Cond: (non_skewed = 42)
> Filter: (skewed = 0)
> Rows Removed by Filter: 10
> Buffers: shared hit=20 read=3
> Execution time: 0.094 ms
>
> Bad plan (all the subsequent executions):
> Index Scan using skewed__flipper on plan_flipper (cost=0.43..6.77
> rows=1 width=113) (actual time=0.067..355.867 rows=10 loops=1)
> Index Cond: (skewed = $1)
> Filter: (non_skewed = $2)
> Rows Removed by Filter: 999990
> Buffers: shared hit=18182 read=2735
> Execution time: 355.901 ms

The problem is that the index "skewed__flipper" is more selective than
"non_skewed__flipper" except when "skewed = 0", so the generic plan prefers it.

I don't know if there is a good solution except disabling server prepared statements.

Yours,
Laurenz Albe

> I don't know if there is a good solution except disabling server prepared statements.

Why doesn't backend reuse already existing good plan?
The plan does account for the skew.

Can backend take selectivities from the original bind values?

Vladimir

2016-01-12 16:52 GMT+01:00 Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>:

> > I don't know if there is a good solution except disabling server
> prepared statements.
>
> Why doesn't backend reuse already existing good plan?
>

probably good plan is more expensive than wrong plan :(

this logic is driven by plan cost, not by plan execution time.

> The plan does account for the skew.
>
> Can backend take selectivities from the original bind values?
>

> Vladimir
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

VS>>Why doesn't backend reuse already existing good plan?
PS>this logic is driven by plan cost, not by plan execution time.

It do understand that currently PG replans with $1, $2 and uses
default selectivities for that.

What I am asking is to make PG aware of "previously used bind values",
so it would calculate proper selectivities for $1, $2.

PS. It is not the first time the problem bites me, so I hand-crafted a testcase.

Vladimir

2016-01-12 17:01 GMT+01:00 Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>:

> VS>>Why doesn't backend reuse already existing good plan?
> PS>this logic is driven by plan cost, not by plan execution time.
>
> It do understand that currently PG replans with $1, $2 and uses
> default selectivities for that.
>
> What I am asking is to make PG aware of "previously used bind values",
> so it would calculate proper selectivities for $1, $2.
>

the implementation is simply - but it hard to design some really general -
it is task for UI

Pavel

>
> PS. It is not the first time the problem bites me, so I hand-crafted a
> testcase.
>

>
> Vladimir
>

>the implementation is simply - but it hard to design some really general - it is task for UI

Can you please rephrase?

Current design is "if the cost of a generic plan is less than the one
of a custom plan+replan, prefer generic".
I think that is wrong.

"Generic plan" misunderestimates a cost in a sense that it assumes
some pre-defined selectivities.
In other words, if "skewed" values are used, "custom plan" would
likely to have *worse cost* than the one of a generic plan, yet custom
plan is much more suitable for a particular parameter set.
As backend refers to boundParams, it does see that particular
condition is tough, while generic estimator just the cost.

Looking into plancache.c comments I see 3 possible plans:
1) custom plan with PARAM_FLAG_CONST=1. It should probably
constant-fold based on input parameters.

2) custom plan with PARAM_FLAG_CONST=0. I think it should just use
given parameters for selectivity estimations. The generated plan
should still be valid for use with other input values.
3) generic plan. The plan with all variables. <-- here's current behavior

1 has a replan cost.

2&3 can be cached and reused.

Is that correct?
I think #2 is better option than #3 since it gives better plan
stability, thus it is much easier to test and reason about.

This all boils down to adjustment in a single line:
https://github.com/postgres/postgres/blob/ee943004466418595363d567f18c053bae407792/src/backend/utils/cache/plancache.c#L1151-L1152

Does that make sense?

Vladimir

2016-01-13 11:44 GMT+01:00 Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>:

> >the implementation is simply - but it hard to design some really general
> - it is task for UI
>
> Can you please rephrase?
>

Sorry - It is task for artifical inteligency

>
> Current design is "if the cost of a generic plan is less than the one
> of a custom plan+replan, prefer generic".
> I think that is wrong.
>
> "Generic plan" misunderestimates a cost in a sense that it assumes
> some pre-defined selectivities.
>

Generic plan in Postgres is optimized for most common values - so in
avarage it should be optimal. But the reality is different - the wrong
estimation can be everywhere and the estimation can be lower or upper than
reality.

> In other words, if "skewed" values are used, "custom plan" would
> likely to have *worse cost* than the one of a generic plan, yet custom
> plan is much more suitable for a particular parameter set.
> As backend refers to boundParams, it does see that particular
> condition is tough, while generic estimator just the cost.
>

And there is a second issue - you have not a idea, what parameter vector
will follow. You cannot to check and optimize plans every where, because a
planning can be expensive, and you should to reuse plan more times. What
was true, for first iterations, then it should not be true in following
iterations.

I like a strategy based on risks. Probably there are situation, when the
generic plan is great every time - INSERTs, UPDATEs via PK, simple SELECTs
via PK. generic plan can be well if almost all data has similar
probability. Elsewhere on bigger data, the probability of pretty slow plan
is higher, and then we should to prefer custom plan.

so the strategy - if cost of generic plan is less than some MAGIC CONSTANT
(can be specified by GUC), then use generic plan. Elsewhere use a custom
plan everytime.

It allow to controll the plan reusing. When MAGIC CONSTANT = 0 .. use
custom plan everytime, When MAGIC CONSTANT = M, then use generic plan
always.

Regards

Pavel

> Looking into plancache.c comments I see 3 possible plans:
> 1) custom plan with PARAM_FLAG_CONST=1. It should probably
> constant-fold based on input parameters.
>
> 2) custom plan with PARAM_FLAG_CONST=0. I think it should just use
> given parameters for selectivity estimations. The generated plan
> should still be valid for use with other input values.
> 3) generic plan. The plan with all variables. <-- here's current behavior
>
> 1 has a replan cost.
>
> 2&3 can be cached and reused.
>
> Is that correct?
> I think #2 is better option than #3 since it gives better plan
> stability, thus it is much easier to test and reason about.
>
> This all boils down to adjustment in a single line:
>
> https://github.com/postgres/postgres/blob/ee943004466418595363d567f18c053bae407792/src/backend/utils/cache/plancache.c#L1151-L1152
>
> Does that make sense?
>
> Vladimir
>

Please, read my suggestion again.

TL;DR: I suggest to create "generic plan" with regard to current bind values.
What's wrong with that approach?

Vladimir

On 13/01/16 14:12, Pavel Stehule wrote:
> so the strategy - if cost of generic plan is less than some MAGIC CONSTANT
> (can be specified by GUC), then use generic plan. Elsewhere use a custom
> plan everytime.
>
> It allow to controll the plan reusing. When MAGIC CONSTANT = 0 .. use
> custom plan everytime, When MAGIC CONSTANT = M, then use generic plan
> always.

I don't think that would solve even the original problem without
effectively disabling generic plans, despite the problem being
relatively simple. The generic plan appears to be really cheap, because
the planner doesn't have the concept of a "worst case".

.m

On 13/01/16 14:27, Vladimir Sitnikov wrote:
> TL;DR: I suggest to create "generic plan" with regard to current bind values.
> What's wrong with that approach?

I don't understand how this is supposed to work. Say you already have a
plan which looks like this:

Seq Scan on foo (cost=0.00..100.00 rows=1 width=630)
Filter: (bar = $1)

Now the plan gets invoked with $1 = 5. What exactly in your mind would
happen here?

.m

Note: I do not suggest changing already cached plans yet.
I suggest looking into "6th bind values" when building a cached plan.
In other words, "if first 5 execution do not reveal dependence on bind
values, then cache the generated plan".

>Say you already have a plan which looks like this:
>Now the plan gets invoked with $1 = 5. What exactly in your mind would happen here?

A sequential scan with $1=5 condition. What else could be there?

Vladimir

On 13/01/16 14:36, Vladimir Sitnikov wrote:
>> Say you already have a plan which looks like this:
>> Now the plan gets invoked with $1 = 5. What exactly in your mind would happen here?
>
> A sequential scan with $1=5 condition. What else could be there?

I don't know, it's your proposal :-) But it looks like I misunderstood.

> Note: I do not suggest changing already cached plans yet.
> I suggest looking into "6th bind values" when building a cached plan.

But that wouldn't have helped your case. The custom plan is *more
expensive*; I'm guessing because the generic plan gambles on a better
average case instead of preparing for the worst case.

.m

> The custom plan is *more expensive*;

You compare costs of custom vs generic plans.
I suggest: do not compare costs *at all*.

>I don't know, it's your proposal :-) But it looks like I misunderstood.

It is not.

My suggestion is: build a generic plan (that is the plan that will
return proper result for every possible bind value), yet refer to the
values of 6th binds when estimating cardinalitites.
Is it clear now?

Vladimir

On January 13, 2016 3:02:27 PM GMT+01:00, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com> wrote:
>> The custom plan is *more expensive*;
>
>You compare costs of custom vs generic plans.
>I suggest: do not compare costs *at all*.
>
>>I don't know, it's your proposal :-) But it looks like I
>misunderstood.
>
>It is not.
>
>My suggestion is: build a generic plan (that is the plan that will
>return proper result for every possible bind value), yet refer to the
>values of 6th binds when estimating cardinalitites.
>Is it clear now?

That's not going to fly for two reasons: for one custom plans can be much better than the generic plan, independent of cardinalities. Consider e.g. a partitioned table, where the generic scan will scan all partitions. For another, just using the specific values for the generic plan will have horrible results if the distribution isn't entirely boring, consider e.g a table with one somewhat common and otherwise just unique values.

---
Please excuse brevity and formatting - I am writing this on my mobile phone.

Maybe more work, but:

Could a custom plan be cached /along with the values of
the parameters for which it was planned/? Then it's kind of
a no-brainer to say "if the parameters are the same this time,
I'm probably looking at the best plan already." Pretty simpleminded,
but at least it would catch the testcase where the plan flips to
a bad one even when literally the very same parameters are used.

Generalizing a little, how about caching a plan along with a
boolean expression tree over the parameters, evaluating to true
if the new parameters are "close enough" to the planned ones so
that the plan is probably still better than generic?

Version zero could just create the expression p1 = oldp1
AND p2 = oldp2 AND p3 = oldp3, and be simply the same as the
first suggestion. But then, how to generate more interesting
and useful validity-expressions for different plan types and
statistics could be a future-work area with some meat to it,
promising successive improvements. Maybe plugins could
supplement it for particular characterized workloads....

-Chap

On 01/13/2016 08:28 AM, Marko Tiikkaja wrote:
> On 13/01/16 14:12, Pavel Stehule wrote:
>> so the strategy - if cost of generic plan is less than some MAGIC
>> CONSTANT
>> (can be specified by GUC), then use generic plan. Elsewhere use a custom
>> plan everytime.
>>
>> It allow to controll the plan reusing. When MAGIC CONSTANT = 0 .. use
>> custom plan everytime, When MAGIC CONSTANT = M, then use generic plan
>> always.
>
> I don't think that would solve even the original problem without
> effectively disabling generic plans, despite the problem being
> relatively simple. The generic plan appears to be really cheap, because
> the planner doesn't have the concept of a "worst case".
>
>
> .m
>

> for one custom plans can be much better than the generic plan, independent of cardinalities

So what? I do not suggest dropping custom plans entirely.
I perfectly understand there are cases when better replan every time.

> consider e.g a table with one somewhat common and otherwise just unique values.

So what?
If I understand you properly, you mean: "if client sends unique binds
first 5-6 executions and bad non-unique afterwards, then cached plan
would be bad". Is that what you are saying?
I agree that is the corner-case for my suggestion.
Is is really happening often?

I state the following:
1) It is way easier to debug & analyze.
For instance: current documentation does *not* list a way to get a
*generic plan*.
Is that obvious that "you just need to EXPLAIN ANALYZE EXECUTE *6
times in a row*" just to get a generic plan?

2) It is likely to be more performant. We just need to explain users
that "if different plans required, just use different statements".
Isn't that obvious?
Frankly speaking, I do not like "plug&pray" kind of code that just
sends bind values and expects magically optimized plan for each bind
combination.

3) What about "client sends top most common value 5 times in a row"?
Why assume "it will stop doing that"?
I think the better assumption is "it will continue doing that".

At the end, if a client wants specific treatment of a query, then
he/she might be better using separate server-prepared statements (the
one for "unique values", and another one for "non-unique").

Vladimir

On 13/01/16 15:26, Vladimir Sitnikov wrote:
> 2) It is likely to be more performant. We just need to explain users
> that "if different plans required, just use different statements".

This completely screws over PL/PgSQL, among other things.

.m

>This completely screws over PL/PgSQL, among other things.

Can you elaborate a bit?

Vladimir

On 2016-01-13 17:26:43 +0300, Vladimir Sitnikov wrote:
> > consider e.g a table with one somewhat common and otherwise just unique values.>
> So what?
> If I understand you properly, you mean: "if client sends unique binds
> first 5-6 executions and bad non-unique afterwards, then cached plan
> would be bad". Is that what you are saying?

That's one of several problems, yes. Generally using a very small sample
("bind values in the the first query"), to plan every future query isn't
going to be fun.

> I agree that is the corner-case for my suggestion.
> Is is really happening often?

Yes.

> I state the following:
> 1) It is way easier to debug & analyze.

Meh. That a prepared statement suddenly performs way differently
depending on which the first bind values are is not, in any way, easier
to debug.

> For instance: current documentation does *not* list a way to get a
> *generic plan*.

Which doensn't have anything to do with your proposal. That'd not change
with the change you propose.

> Is that obvious that "you just need to EXPLAIN ANALYZE EXECUTE *6
> times in a row*" just to get a generic plan?

No. And I hate that. I think it'd be very good to expand EXPLAIN's
output to include information about custom/generic plans.

> 3) What about "client sends top most common value 5 times in a row"?
> Why assume "it will stop doing that"?
> I think the better assumption is "it will continue doing that".

If 20% of your values are nonunique and the rest is unique you'll get
*drastically* different plans, each performing badly for the other case;
with the unique cardinality plan being extremly bad.

Andres

On 13/01/16 15:34, Vladimir Sitnikov wrote:
>> This completely screws over PL/PgSQL, among other things.
>
> Can you elaborate a bit?

You just write a query like this:

SELECT * FROM foo WHERE bar = _Variable;

so you don't get to (or want to) have any control over the underlying
prepared statement.

.m

>so you don't get to (or want to) have any control over the underlying prepared statement.

That is pl/pgsql's problem, isn't it?
In the mean time, user can use different query texts (e.g. by adding
offset 0, offset 0*1, offset 0*2, etc kind of stuff they typically use
to tune queries) to convince plpgsql to use different statement ids.

Vladimir

On 2016-01-13 17:38:22 +0300, Vladimir Sitnikov wrote:
> >so you don't get to (or want to) have any control over the underlying prepared statement.
>
> That is pl/pgsql's problem, isn't it?
> In the mean time, user can use different query texts (e.g. by adding
> offset 0, offset 0*1, offset 0*2, etc kind of stuff they typically use
> to tune queries) to convince plpgsql to use different statement ids.

Basically you're arguing to fix one specific edge case which bugs you
personally, by creating a lot of others, which don't bug you. Not
convincing.

Andres Freund

> Generally using a very small sample

That is another issue. Inventing some other algorithm instead of
current "cache after 5 executions" is another effort.

However, I suggest to "learn" from what client is sending.
You suggest to completely ignore that and just prepare for the case
he/she will send "a random value".
Why expect client would stop sending MCVs if we have already seen them
during previous 5 executions?

> That'd not change with the change you propose.

It will.
In my suggestion, the first "explain analyze execute" will match the
"finally cached plan" provided the plan is not treated in a special
way (e.g. replan every time, etc).

> That a prepared statement suddenly performs way differently
>depending on which the first bind values are is not, in any way, easier
>to debug.

It is way easier to debug since *the first* execution plan you get out
of "explain" *matches* the one that will finally be used.
Lots of developers are just not aware of "5 replans by backend".
Lots of the remaining confuse it with "5 non-server-prepared
executions by pgjdbc driver".

In other way: in order to identify a root cause of a slow query you
find bind values. Then you perform explain analyze and you find shiny
fast plan.
Does that immediately ring bells that you need to execute it 6 times
to ensure the plan would still be good?
Well, try being someone not that smart as you are when you answering
this question.

2) In my suggestion, "the first execution would likely to match the plan".

VS>> 3) What about "client sends top most common value 5 times in a row"?
VS>> Why assume "it will stop doing that"?
AF>If 20% of your values are nonunique and the rest is unique you'll get
AF>*drastically* different plans, each performing badly for the other case;
AF>with the unique cardinality plan being extremly bad.

Can you elaborate a bit? I can hardly follow that.

Vladimir

>Basically you're arguing to fix one specific edge case which bugs you
>personally, by creating a lot of others, which don't bug you. Not
>convincing.

It bugs me.
It bugs clients of pgjdbc (e.g. Thomas who started the thread).

Note: support of prepared statements for java applications has just
landed. The release dates are 2015-08-27 for pgjdbc, and 2014-02-24
for pgjdbc-ng.
I think current report is just a tip of the iceberg.

> by creating a lot of others, which don't bug you

I think it will not create "lots of others".
Do you have any statistics why particular flavour of generic plan was
committed in 9.2?

Suppose there are two type of binds: "non_unique" (N) and "unique" (U)
that require different plans for perfect response times.

I see the following sequences
UUUUUUUU -- all clear, all the approaches would converge to plan for
"unique values".

NNNNNNNN -- query for non-unique value is executed again and again.
Perfect optimizer would either replan or reuse plan with regard to "MCV"
Current behaviour would switch to "optimistic" plan at 6th
iteration. It is the case of the thread.
My suggestion is to learn that "MCV is used -> use plan optimized for MCV"

^^^ note that above are "recommended" uses of the database. Each
statement is used for its own purpose: one for MCVs, another for "good
values".

Then there are cases of mixed executions.
Note: I state that mixing "kinds" of bind values is a bad application
design anyway. In other words, application developer should understand
if a query is DWH-like (requires replans) or OLTP-like (does not
require replans). Agreed?

NNNNNUUUUUUU
Current behavior optimized for exactly this pattern.
Well, why was it chosen over "UUUUUNNNNNNN"?

In other words, a pattern like UUUUUNNNNNNN would "create a lot of
others" as you say.

NUNUNUNUNUN -- perfect optimizer would replan every time (or have two
sets of plans, but let's leave that out)
Neither my suggestion nor current behaviour properly covers the case.

I suggest to spare "NNNNNUUUUUUU" pattern in order to improve "NNNNNNNN".

Vladimir

Pavel Stehule wrote:
> I like a strategy based on risks. Probably there are situation, when the generic plan is great every
> time - INSERTs, UPDATEs via PK, simple SELECTs via PK. generic plan can be well if almost all data has
> similar probability. Elsewhere on bigger data, the probability of pretty slow plan is higher, and then
> we should to prefer custom plan.
>
> so the strategy - if cost of generic plan is less than some MAGIC CONSTANT (can be specified by GUC),
> then use generic plan. Elsewhere use a custom plan everytime.
>
> It allow to controll the plan reusing. When MAGIC CONSTANT = 0 .. use custom plan everytime, When
> MAGIC CONSTANT = M, then use generic plan always.

I have a different idea:

What about a GUC "custom_plan_threshold" that controls after how many
executions a generic plan will be considered, with a default value of 5.

A value of -1 could disable generic plans.

Yours,
Laurenz Albe

>A value of -1 could disable generic plans

I do not like the idea.

I've seen dramatic performance improvements from using cached plans.
The numbers are like "20ms to plan vs 1ms to execute" for an often
used OLTP query. Query text is involved (~5-10KiB).

Vladimir

2016-01-13 16:18 GMT+01:00 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:

> Pavel Stehule wrote:
> > I like a strategy based on risks. Probably there are situation, when the
> generic plan is great every
> > time - INSERTs, UPDATEs via PK, simple SELECTs via PK. generic plan can
> be well if almost all data has
> > similar probability. Elsewhere on bigger data, the probability of pretty
> slow plan is higher, and then
> > we should to prefer custom plan.
> >
> > so the strategy - if cost of generic plan is less than some MAGIC
> CONSTANT (can be specified by GUC),
> > then use generic plan. Elsewhere use a custom plan everytime.
> >
> > It allow to controll the plan reusing. When MAGIC CONSTANT = 0 .. use
> custom plan everytime, When
> > MAGIC CONSTANT = M, then use generic plan always.
>
> I have a different idea:
>
> What about a GUC "custom_plan_threshold" that controls after how many
> executions a generic plan will be considered, with a default value of 5.
>
> A value of -1 could disable generic plans.
>

yes, I though about it too - it is simple, and almost deterministic

Pavel

>
> Yours,
> Laurenz Albe
>

2016-01-13 16:22 GMT+01:00 Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>:

> >A value of -1 could disable generic plans
>
> I do not like the idea.
>
> I've seen dramatic performance improvements from using cached plans.
> The numbers are like "20ms to plan vs 1ms to execute" for an often
> used OLTP query. Query text is involved (~5-10KiB).
>

but currently we have not any tool how to check the quality of plan for new
set of parameters. If plan is ok for one value parameters, then can be
pretty bad for following parameters.

Albe's proposal can be good enough for 2/3 cases and it doesn't block any
other enhancing. There is still 1/3 of queries - too complex (slow
planning) too dynamic plan (the generic plan doesn't work).

Regards

Pavel

>
> Vladimir
>

Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com> writes:
> Note: I state that mixing "kinds" of bind values is a bad application
> design anyway. In other words, application developer should understand
> if a query is DWH-like (requires replans) or OLTP-like (does not
> require replans). Agreed?

No, not agreed. As was already pointed out upthread, such information
is not available in many use-cases for the plancache.

The real problem here IMO is inaccurate plan cost estimates, and that's
not something that there is any easy fix for.

However ... one specific aspect of that is that to some extent, the cost
estimate made for the generic plan is incommensurate with the estimates
for the custom plans because the latter are made with more information.
I don't remember the details of your specific case anymore, but we've
seen cases where the generic plan is falsely estimated to be cheaper
than custom plans because of this.

I wonder whether it would be useful to reject a generic plan anytime its
estimate is less than the average (or minimum?) estimate for the custom
plans. If it is less, then either (1) the generic plan is falsely
optimistic, or (2) the specific parameter values provided for the custom
plans were all ones for which the planner could see that the generic plan
was non-optimal. If (2) holds for the first few custom plans then it's
not unreasonable to suppose that it will keep on holding, and we had
better not use the generic plan.

Basically, the case we're *expecting* to see is that a custom plan is the
same or better cost as the generic plan --- same cost if it's really the
same plan, better cost if knowing the parameter values allows some
optimization to be performed (LIKE-pattern-to-index conversion, partition
scan suppression via constraint exclusion, etc). If we get a higher cost
estimate for the custom plan then something is fishy and we shouldn't
believe it.

Maybe I'm missing some case where that situation would arise naturally.
Or maybe such a rule wouldn't actually help in very many real-world
cases. But it seems worth looking into.

regards, tom lane

>(1) the generic plan is falsely optimistic

That is my case.
Application is sending most common value on every execution while
backend is optimistic and it things that the app would stop sending
MCVs.

Costs for the plans are OK. However, there is a data skew, so it is
hard to tell what is the "true" selectivity of the skewed column in
general, thus the discussion.

VS>>In other words, application developer should understand
VS>> if a query is DWH-like (requires replans) or OLTP-like (does not
VS>> require replans). Agreed?
Tom>No, not agreed. As was already pointed out upthread, such information
Tom>is not available in many use-cases for the plancache.

I think you answer the wrong question.
I was asking if you agree that _application_ developer (not pg backed
developer) should know if a query is OLTP or DWH like.

Do you really think app developer should not care which plan would be
chosen for a particular query he is working on?
Why all that "explain" stuff in documentation then?

In the plancache.c you have CURSOR_OPT_GENERIC_PLAN and
CURSOR_OPT_CUSTOM_PLAN flags.
It is obvious that those flags are not yet exposed/used by
applications, but my message is that "one should *not* think that DB
has artificial intelligence to properly identify a plan for each bind
sets and cache plans at the same time".

Vladimir

>If plan is ok for one value parameters, then can be pretty bad for following parameters.

Happy statements are all alike; every unhappy statement is unhappy in
its own way (see [1]).
If user is sending different kinds of parameters, he is shooting in the foot.

>Albe's proposal can be good enough for 2/3 cases and it doesn't block any other enhancing

Albe's proposal effectively disables plan cache, thus it blocks enhancing.
If a user goes "replan every time" route, there is no way you
introduce plan caching there.

[1]: https://en.wikipedia.org/wiki/Anna_Karenina_principle

Vladimir

2016-01-13 17:12 GMT+01:00 Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>:

> >If plan is ok for one value parameters, then can be pretty bad for
> following parameters.
>
> Happy statements are all alike; every unhappy statement is unhappy in
> its own way (see [1]).
> If user is sending different kinds of parameters, he is shooting in the
> foot.
>
> >Albe's proposal can be good enough for 2/3 cases and it doesn't block any
> other enhancing
>
> Albe's proposal effectively disables plan cache, thus it blocks enhancing.
> If a user goes "replan every time" route, there is no way you
> introduce plan caching there.
>

I am sorry, I disagree. Albe's proposal should be compatible with current
state, so your argument is too strong. Default is same.

Pavel

>
> [1]: https://en.wikipedia.org/wiki/Anna_Karenina_principle
>
> Vladimir
>

On Wed, Jan 13, 2016 at 10:47:18AM -0500, Tom Lane wrote:
> Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com> writes:
> > Note: I state that mixing "kinds" of bind values is a bad application
> > design anyway. In other words, application developer should understand
> > if a query is DWH-like (requires replans) or OLTP-like (does not
> > require replans). Agreed?
>
> No, not agreed. As was already pointed out upthread, such information
> is not available in many use-cases for the plancache.
>
> The real problem here IMO is inaccurate plan cost estimates, and that's
> not something that there is any easy fix for.
>
> However ... one specific aspect of that is that to some extent, the cost
> estimate made for the generic plan is incommensurate with the estimates
> for the custom plans because the latter are made with more information.
> I don't remember the details of your specific case anymore, but we've
> seen cases where the generic plan is falsely estimated to be cheaper
> than custom plans because of this.

I never understood why we don't just keep the selectivity estimates of
previous plans and just reuse the plan if the selectivity estimates are
similar. Isn't parameter selectivity the only thing that distinguishes
on plan with parameter from another?

Checking selectivity estimates must be cheaper than replanning. This
could be done at the second use of the prepared plan, and maybe for all
plan reuses, rather than waiting for five and then perhaps getting this
bad behavior.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> I never understood why we don't just keep the selectivity estimates of
> previous plans and just reuse the plan if the selectivity estimates are
> similar. Isn't parameter selectivity the only thing that distinguishes
> on plan with parameter from another?

> Checking selectivity estimates must be cheaper than replanning. This
> could be done at the second use of the prepared plan, and maybe for all
> plan reuses, rather than waiting for five and then perhaps getting this
> bad behavior.

You're imagining that a selectivity recheck could be separated out from
the rest of the planner. That's nowhere near feasible, IMO. Even if it
were, what would we do with it? There's no reliable way to determine
whether X% change in one or another selectivity number would change the
selected plan, other than by redoing practically all of the planning work.

regards, tom lane

On Mon, Jan 18, 2016 at 02:14:11PM -0500, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > I never understood why we don't just keep the selectivity estimates of
> > previous plans and just reuse the plan if the selectivity estimates are
> > similar. Isn't parameter selectivity the only thing that distinguishes
> > on plan with parameter from another?
>
> > Checking selectivity estimates must be cheaper than replanning. This
> > could be done at the second use of the prepared plan, and maybe for all
> > plan reuses, rather than waiting for five and then perhaps getting this
> > bad behavior.
>
> You're imagining that a selectivity recheck could be separated out from
> the rest of the planner. That's nowhere near feasible, IMO. Even if it

I think you would have to do the checks before entering the planner and
save them off for use in the planner.

> were, what would we do with it? There's no reliable way to determine
> whether X% change in one or another selectivity number would change the
> selected plan, other than by redoing practically all of the planning work.

Well, if it is +/-1%, I think we can assume we can reuse the plan just
fine from the second prepared call until we see a major selectivity
change. While we have never exposed the count of prepared queries
before we choose a generic plan, I can see us exposing this percentage.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +

On Wed, Jan 13, 2016 at 10:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com> writes:
>> Note: I state that mixing "kinds" of bind values is a bad application
>> design anyway. In other words, application developer should understand
>> if a query is DWH-like (requires replans) or OLTP-like (does not
>> require replans). Agreed?
>
> No, not agreed. As was already pointed out upthread, such information
> is not available in many use-cases for the plancache.
>
> The real problem here IMO is inaccurate plan cost estimates, and that's
> not something that there is any easy fix for.

Not really. Even if the cost estimates for all of the plans tried are
perfectly accurate, you'll have only seen 5 values when you decide to
switch to a generic plan. If the 6th, 60th, 600th, or 6000th
execution uses a parameter where a custom plan would have been a big
win, you will blindly use the generic plan anyway and lose bigtime.
On the other hand, if first five plans are all equivalent to each
other and to the generic plan, then you've spent the cost of uselessly
replanning six times instead of just caching the first plan and being
done with it. I'm aware of an actual case where that extra
re-planning causes a serious performance problem, aggregated across
many queries and many backends.

This isn't the first complaint about this mechanism that we've gotten,
and it won't be the last. Way too many of our users are way more
aware than they should be that the threshold here is five rather than
any other number, which to me is a clear-cut sign that this needs to
be improved. How to improve it is a harder question. We lack the
ability to do any kind of sensitivity analysis on a plan, so we can't
know whether there are other parameter values that would have resulted
in a different plan, nor can we test whether a particular set of
parameter values would have changed the outcome.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> This isn't the first complaint about this mechanism that we've gotten,
> and it won't be the last. Way too many of our users are way more
> aware than they should be that the threshold here is five rather than
> any other number, which to me is a clear-cut sign that this needs to
> be improved.

No argument there.

> How to improve it is a harder question.

Exactly. I'm very suspicious of any easy answers to this; they'll
most likely just shift the pain around.

regards, tom lane

Robert Haas wrote:
> This isn't the first complaint about this mechanism that we've gotten,
> and it won't be the last. Way too many of our users are way more
> aware than they should be that the threshold here is five rather than
> any other number, which to me is a clear-cut sign that this needs to
> be improved. How to improve it is a harder question. We lack the
> ability to do any kind of sensitivity analysis on a plan, so we can't
> know whether there are other parameter values that would have resulted
> in a different plan, nor can we test whether a particular set of
> parameter values would have changed the outcome.

(I initially posted that question on the JDBC mailing list)

To be honest: looking at the efforts Oracle has done since 9 up until 12 I
am not sure this is a problem that can be solved by caching plans.

Even with the new "in-flight" re-planning in Oracle 12 ("cardinality
feedback") and all the effort that goes into caching plans we are still
seeing similar problems with (prepared) statements that are suddenly slow.
And as far as I can tell, the infrastructure around plan caching,
invalidation, bind variable peeking and all that seems to be a *lot* more
complex ("sophisticated") in Oracle compared to Postgres. And the results
don't seem to justify the effort (at least in my experience).

With all the problems I have seen (in Oracle and Postgres) I think that
maybe a better solution to this problem is to make the planner fast (and
reliable) enough so that plan caching isn't necessary in the first place.

However I have no idea how feasible that is.

--
View this message in context: http://postgresql.nabble.com/Fwd-JDBC-Re-9-4-1207-behaves-differently-with-server-side-prepared-statements-compared-to-9-2-1102-tp5881825p5882835.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

2016-01-18 23:50 GMT+01:00 Thomas Kellerer <spam_eater(at)gmx(dot)net>:

> Robert Haas wrote:
> > This isn't the first complaint about this mechanism that we've gotten,
> > and it won't be the last. Way too many of our users are way more
> > aware than they should be that the threshold here is five rather than
> > any other number, which to me is a clear-cut sign that this needs to
> > be improved. How to improve it is a harder question. We lack the
> > ability to do any kind of sensitivity analysis on a plan, so we can't
> > know whether there are other parameter values that would have resulted
> > in a different plan, nor can we test whether a particular set of
> > parameter values would have changed the outcome.
>
> (I initially posted that question on the JDBC mailing list)
>
> To be honest: looking at the efforts Oracle has done since 9 up until 12 I
> am not sure this is a problem that can be solved by caching plans.
>
> Even with the new "in-flight" re-planning in Oracle 12 ("cardinality
> feedback") and all the effort that goes into caching plans we are still
> seeing similar problems with (prepared) statements that are suddenly slow.
> And as far as I can tell, the infrastructure around plan caching,
> invalidation, bind variable peeking and all that seems to be a *lot* more
> complex ("sophisticated") in Oracle compared to Postgres. And the results
> don't seem to justify the effort (at least in my experience).
>
> With all the problems I have seen (in Oracle and Postgres) I think that
> maybe a better solution to this problem is to make the planner fast (and
> reliable) enough so that plan caching isn't necessary in the first place.
>
> However I have no idea how feasible that is.
>

for statements like INSERT INTO tab VALUES(..), UPDATE tab SET x = WHERE id
= .. will be planner significant overhead. But these statements are
relative simply and probably some solution is exists.

Regards

Pavel

>
>
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/Fwd-JDBC-Re-9-4-1207-behaves-differently-with-server-side-prepared-statements-compared-to-9-2-1102-tp5881825p5882835.html
> Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Tue, Jan 19, 2016 at 4:20 AM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
>
> Robert Haas wrote:
> > This isn't the first complaint about this mechanism that we've gotten,
> > and it won't be the last. Way too many of our users are way more
> > aware than they should be that the threshold here is five rather than
> > any other number, which to me is a clear-cut sign that this needs to
> > be improved. How to improve it is a harder question. We lack the
> > ability to do any kind of sensitivity analysis on a plan, so we can't
> > know whether there are other parameter values that would have resulted
> > in a different plan, nor can we test whether a particular set of
> > parameter values would have changed the outcome.
>
> (I initially posted that question on the JDBC mailing list)
>
> To be honest: looking at the efforts Oracle has done since 9 up until 12 I
> am not sure this is a problem that can be solved by caching plans.
>
> Even with the new "in-flight" re-planning in Oracle 12 ("cardinality
> feedback") and all the effort that goes into caching plans we are still
> seeing similar problems with (prepared) statements that are suddenly slow.
> And as far as I can tell, the infrastructure around plan caching,
> invalidation, bind variable peeking and all that seems to be a *lot* more
> complex ("sophisticated") in Oracle compared to Postgres. And the results
> don't seem to justify the effort (at least in my experience).
>

I have heard the same feedback as above some time back from some
of the research fellows doing research in query optimization area. They
come-up with different concept called "Plan Bouquet" [1] where
in they try to execute multiple plans during execution and proceed with
the best-among those or something like that to address bad
plan-selection problems and their claim is that this technique proves to
be better on benchmarks than existing mechanisms used for query
optimisation.

I am not advocating any such mechanism, but rather sharing an
information, I came across.

[1] - http://dsl.serc.iisc.ernet.in/publications/conference/bouquet.pdf

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Mon, Jan 18, 2016 at 5:50 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> With all the problems I have seen (in Oracle and Postgres) I think that
> maybe a better solution to this problem is to make the planner fast (and
> reliable) enough so that plan caching isn't necessary in the first place.
>
> However I have no idea how feasible that is.

The problem is that the floor is already littered with
potentially-very-beneficial query planning ideas that got discarded
because they would add too many cycles to planning time. Despite
that, planning time is a killer on some workloads. So right now we've
got workloads where we plan too much and workloads where we plan too
little. Argh.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company