Avoiding bad prepared-statement plans.

Jeroen Vermeulen wrote:

I've been discussing this with Josh, Heikki, and Peter E. over the
past few weeks.

Is this searchable in the archives? I'm interested in ideas discussed.

If a prepared statement takes parameters, and the generic plan has a
high projected cost, re-plan each EXECUTE individually with all its
parameter values bound. It may or may not help, but unless the
planner is vastly over-pessimistic, re-planning isn't going to
dominate execution time for these cases anyway.

This sounds like a really nice to have feature. Maybe it'd also be
possible to skip replanning between executes if the current bound values
are 'indexwise-equivalent' to the values used at previous planning, i.e.
nothing in the statistics indicates that execution cost would be (much)
different. Are there more ways to cut down on planning time? Obviously
some plannedstatement/plannerinfo structures could be kept, but maybe
it'd also be possible to plan only that part of the join tree where the
params are used in a scan/join qual.

regards,
Yeb Havinga

Yeb Havinga wrote:

I've been discussing this with Josh, Heikki, and Peter E. over the
past few weeks.

Is this searchable in the archives? I'm interested in ideas discussed.

No, sorry. These were face-to-face discussions at linux.conf.au and FOSDEM.

If a prepared statement takes parameters, and the generic plan has a
high projected cost, re-plan each EXECUTE individually with all its
parameter values bound. It may or may not help, but unless the
planner is vastly over-pessimistic, re-planning isn't going to
dominate execution time for these cases anyway.

This sounds like a really nice to have feature. Maybe it'd also be
possible to skip replanning between executes if the current bound values
are 'indexwise-equivalent' to the values used at previous planning, i.e.
nothing in the statistics indicates that execution cost would be (much)
different. Are there more ways to cut down on planning time? Obviously
some plannedstatement/plannerinfo structures could be kept, but maybe
it'd also be possible to plan only that part of the join tree where the
params are used in a scan/join qual.

I think we should be careful not to over-think this. Planning isn't
*that* costly, so apply Amdahl's Law liberally. I'm proposing some easy
things we could do without adding much overhead or maintenance burden;
I've been assuming that getting intimate with the planner would risk
those advantages.

Jeroen

On 09/02/10 12:08, Jeroen Vermeulen wrote:

= Projected-cost threshold =

[snip - this is the simple bit. Sounds very sensible. ]

= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to
run in practice.

Do you mean:
1. Rollback the current query and start again
2. Mark the plan as a bad one and plan again next execute

If you can figure out how to do #1 then you could probably do it for all
queries, but I'm guessing it's far from simple to implement.

= Plan refresh =

Periodically re-plan prepared statements on EXECUTE. This is also a
chance for queries that were being re-planned every time to go back to a
generic plan.

Presumably some score based on update stats and vacuum activity etc.

The good side of all these ideas is good indeed. The bad side is plan
instability. Someone somewhere will have a generic plan that turns out
better than the specific plan (due to bad stats or config settings or
just planner limitations). The question is (I guess): How many more
winners will there be than losers?

--
Richard Huxton
Archonet Ltd

On Tuesday 09 February 2010 13:08:54 Jeroen Vermeulen wrote:

I've been discussing this with Josh, Heikki, and Peter E. over the past
few weeks.

As Peter observed years ago, prepared statements can perform badly
because their plans are overly generic. Also, statistics change and
sometimes plans should change with them. It would be nice if we could
avoid users having to worry about these things.

I have some ideas that I'm willing to work on, if people agree that
they're useful. These are simple changes; the goal is to avoid
pathological performance at minimal cost, not to make prepared
statements faster than parameterized ones for everyone. The ideas
interact in various ways.

= Projected-cost threshold =

If a prepared statement takes parameters, and the generic plan has a
high projected cost, re-plan each EXECUTE individually with all its
parameter values bound. It may or may not help, but unless the planner
is vastly over-pessimistic, re-planning isn't going to dominate
execution time for these cases anyway.

= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to
run in practice. Statistics may have gone bad. It could also be a
one-off due to a load peak or something, but that's handled by:

That is not that easy. It means that you have to use savepoints enclosing each
and every execution of a prepared statement because the query could have
sideeffects. Which wouldnt be terribly efficient...

Andres

Richard Huxton wrote:

= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to
run in practice.

Do you mean:
1. Rollback the current query and start again
2. Mark the plan as a bad one and plan again next execute

If you can figure out how to do #1 then you could probably do it for all
queries, but I'm guessing it's far from simple to implement.

I'm talking about #2. As a matter of fact #1 did come up in one of
those discussions, but how do you know you're not killing the query
juuust before it'd done, and then maybe executing a different plan
that's no better?

= Plan refresh =

Periodically re-plan prepared statements on EXECUTE. This is also a
chance for queries that were being re-planned every time to go back to a
generic plan.

Presumably some score based on update stats and vacuum activity etc.

I was thinking of something very simple: re-do whatever we'd do if the
statement were only being prepared at that point.

The good side of all these ideas is good indeed. The bad side is plan
instability. Someone somewhere will have a generic plan that turns out
better than the specific plan (due to bad stats or config settings or
just planner limitations). The question is (I guess): How many more
winners will there be than losers?

That's a good and surprising point, and therefore I'd like to draw
attention away to a different point. :-)

Yes, there will be losers in the sense that people may have optimized
their use of prepared statements to whatever the current planner does.
Maybe somebody out there even deliberately uses them to trick the
planner into a different plan. But that is always going to happen;
we're aiming for better plans, not for giving more detailed control over
them. If you really can't take a change, don't upgrade.

The competing point is: people out there may currently be forgoing
prepared statements entirely because of erratic performance. To those
people, if we can help them, it's like having a new feature.

Jeroen

Andres Freund wrote:

= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to
run in practice. Statistics may have gone bad. It could also be a
one-off due to a load peak or something, but that's handled by:

That is not that easy. It means that you have to use savepoints enclosing each
and every execution of a prepared statement because the query could have
sideeffects. Which wouldnt be terribly efficient...

This is not within an execution of the statement, but across executions.
So the next execution learns from the previous result. So I'm not
talking about aborting the ongoing execution. Sorry for being unclear.

Jeroen

On 09/02/10 14:25, Jeroen Vermeulen wrote:

Richard Huxton wrote:

= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to
run in practice.

Do you mean:
1. Rollback the current query and start again
2. Mark the plan as a bad one and plan again next execute

If you can figure out how to do #1 then you could probably do it for
all queries, but I'm guessing it's far from simple to implement.

I'm talking about #2. As a matter of fact #1 did come up in one of those
discussions, but how do you know you're not killing the query juuust
before it'd done, and then maybe executing a different plan that's no
better?

Ah, you'd need to be smarter when planning and also remember the
expected rows from each node. That way if your (index driven) inner node
was expecting 3 rows you could mark it to force a cancellation if it
returns (say) 30 or more. You'd allow more slack in later processing and
less slack earlier on where a bad estimate can explode the final number
of rows.

Or, there is always the case where we reverse-search an index to find
the last 10 messages in a group say, but the particular group in
question hasn't had a comment for months, so you trawl half the table.
People regularly get bitten by that, and there's not much to be done
about it. If we could abort when it looks like we're in worst-case
rather than best-case scenarios then it would be one less thing for
users to worry about.

= Plan refresh =

Periodically re-plan prepared statements on EXECUTE. This is also a
chance for queries that were being re-planned every time to go back to a
generic plan.

Presumably some score based on update stats and vacuum activity etc.

I was thinking of something very simple: re-do whatever we'd do if the
statement were only being prepared at that point.

Yes, I thought so, the scoring was for *when* to decide to cancel the
old plan. I suppose total query-time would be another way to decide this
plan needs reworking.

The good side of all these ideas is good indeed. The bad side is plan
instability. Someone somewhere will have a generic plan that turns out
better than the specific plan (due to bad stats or config settings or
just planner limitations). The question is (I guess): How many more
winners will there be than losers?

That's a good and surprising point, and therefore I'd like to draw
attention away to a different point. :-)

Yes, there will be losers in the sense that people may have optimized
their use of prepared statements to whatever the current planner does.
Maybe somebody out there even deliberately uses them to trick the
planner into a different plan. But that is always going to happen; we're
aiming for better plans, not for giving more detailed control over them.
If you really can't take a change, don't upgrade.

The competing point is: people out there may currently be forgoing
prepared statements entirely because of erratic performance. To those
people, if we can help them, it's like having a new feature.

Oh, I'm persuaded, but that doesn't really get you anywhere :-)

--
Richard Huxton
Archonet Ltd

On 02/09/2010 08:46 AM, Jeroen Vermeulen wrote:

This sounds like a really nice to have feature. Maybe it'd also be
possible to skip replanning between executes if the current bound
values are 'indexwise-equivalent' to the values used at previous
planning, i.e. nothing in the statistics indicates that execution
cost would be (much) different. Are there more ways to cut down on
planning time? Obviously some plannedstatement/plannerinfo structures
could be kept, but maybe it'd also be possible to plan only that part
of the join tree where the params are used in a scan/join qual.

I think we should be careful not to over-think this. Planning isn't
*that* costly, so apply Amdahl's Law liberally. I'm proposing some
easy things we could do without adding much overhead or maintenance
burden; I've been assuming that getting intimate with the planner
would risk those advantages.

In a current commercial app we have that uses JDBC and prepared plans
for just about everything, it regularly ends up with execution times of
30+ milliseconds when a complete plan + execute would take less than 1
millisecond.

PostgreSQL planning is pretty fast. In terms of not over thinking things
- I think I would even prefer an option that said "always re-plan
prepared statements" as a starting point. If it happened to become
smarter over time, such that it would have invalidation criteria that
would trigger a re-plan, that would be awesome, but in terms of what
would help me *today* - being able to convert prepared plans into just a
means to use place holders would help me today on certain real
applications in production use right now.

Cheers,
mark

--
Mark Mielke<mark@mielke.cc>

On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen <jtv@xs4all.nl> wrote:

= Projected-cost threshold =

If a prepared statement takes parameters, and the generic plan has a high
projected cost, re-plan each EXECUTE individually with all its parameter
values bound.  It may or may not help, but unless the planner is vastly
over-pessimistic, re-planning isn't going to dominate execution time for
these cases anyway.

How high is high?

= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to run
in practice.  Statistics may have gone bad.  It could also be a one-off due
to a load peak or something, but that's handled by:

= Plan refresh =

Periodically re-plan prepared statements on EXECUTE.  This is also a chance
for queries that were being re-planned every time to go back to a generic
plan.

The most common problem here seems to be that (some?) MCVs need
different treatment than non-MCVs, so I don't think periodically
replanning is going to help very much. What might help is something
like plan twice, once assuming you have the most common MCV and once
assuming you have a non-MCV. If the two plans are same, you're
probably safe. Or if you can somehow determine that one of the plans
will still be pretty fast in the other case, you can just use that
plan across the board. Otherwise, you have to somehow vary the plan
based on the actual parameter value.

...Robert

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen <jtv@xs4all.nl> wrote:

Periodically re-plan prepared statements on EXECUTE. �This is also a chance
for queries that were being re-planned every time to go back to a generic
plan.

The most common problem here seems to be that (some?) MCVs need
different treatment than non-MCVs, so I don't think periodically
replanning is going to help very much.

It won't help at all. The only reason for replanning is if something
about the schema or the statistics change, and we already have got
automatic cached-plan invalidation in both those cases. If you replan
simply because some time has elapsed, you'll just get exactly the
same plan.

The only case that I think still has any merit is where you get a
significantly better plan with known parameter values than without.
The projected-cost threshold might be a reasonable approach for
attacking that, ie, if estimated cost of generic plan exceeds X
then take the time to build a custom plan instead. I'm not sure that
really will fix the problem, but it would be a very simple change to
make to see how much it helps people.

regards, tom lane

Jeroen Vermeulen <jtv@xs4all.nl> writes:

I think we should be careful not to over-think this. Planning isn't *that*
costly, so apply Amdahl's Law liberally. I'm proposing some easy things we
could do without adding much overhead or maintenance burden; I've been
assuming that getting intimate with the planner would risk those advantages.

On a project where performance is a must (5ms per query is about all you
can ask) I have queries for which planning is 40+ ms and execute 2 to 5
ms (dataset fits in RAM, by design).

I'm then abusing pgbouncer so that the PREPARE is shared by a lot of
clients, all the ones landing into the session (transaction pooling).

See preprepare to get a better idea, even if we're yet to run it (it's
being used in production elsewhere, though, I've been told).

http://preprepare.projects.postgresql.org/

Regards,
--
dim

On Tue, 9 Feb 2010, Mark Mielke wrote:

In a current commercial app we have that uses JDBC and prepared plans for
just about everything, it regularly ends up with execution times of 30+
milliseconds when a complete plan + execute would take less than 1
millisecond.

PostgreSQL planning is pretty fast. In terms of not over thinking things - I
think I would even prefer an option that said "always re-plan prepared
statements" as a starting point. If it happened to become smarter over time,
such that it would have invalidation criteria that would trigger a re-plan,
that would be awesome, but in terms of what would help me *today* - being
able to convert prepared plans into just a means to use place holders would
help me today on certain real applications in production use right now.

The JDBC driver has two methods of disabling permanently planned prepared
statements:

1) Use the version two frontend/backend protocol via adding
protocolVersion=2 to your URL. This interpolates all parameters into
the query on the client side.

2) Execute PreparedStatements using the unnamed statement rather than a
named statement via adding prepareThreshold=0 to your URL. A named
statement is expected to be re-used for later execution and ignores the
passed parameters for planning purposes. An unnamed statement may be
re-used, but it doesn't expect to be. The unnamed statement uses the
passed parameters for planning purposes, but still cannot make certain
optimatizations based on the parameter values because it may be
re-executed again later with different parameters. For example a LIKE
query with a parameter value of 'ABC%' cannot be transformed into range
query because the next execution may use a different parameter value for
which the transform is not valid. By default the driver switches to using
a named statement after the same PreparedStatement object is executed five
times.

http://jdbc.postgresql.org/documentation/84/connect.html#connection-parameters
http://jdbc.postgresql.org/documentation/84/server-prepare.html

Kris Jurka

Kris Jurka wrote:

The JDBC driver has two methods of disabling permanently planned prepared
statements:

1) Use the version two frontend/backend protocol via adding
protocolVersion=2 to your URL. This interpolates all parameters into
the query on the client side.

2) Execute PreparedStatements using the unnamed statement rather than a
named statement via adding prepareThreshold=0 to your URL. A named
statement is expected to be re-used for later execution and ignores the
passed parameters for planning purposes. An unnamed statement may be
re-used, but it doesn't expect to be. The unnamed statement uses the
passed parameters for planning purposes, but still cannot make certain
optimatizations based on the parameter values because it may be
re-executed again later with different parameters. For example a LIKE
query with a parameter value of 'ABC%' cannot be transformed into range
query because the next execution may use a different parameter value for
which the transform is not valid. By default the driver switches to using
a named statement after the same PreparedStatement object is executed five
times.

http://jdbc.postgresql.org/documentation/84/connect.html#connection-parameters
http://jdbc.postgresql.org/documentation/84/server-prepare.html

Can someone explain to me why we only do "delayed binding" for unnamed
prepared queries? Why do we not allow this option for named protocol
prepared queries and SQL prepared queries?

Here is what our documentation has in the protocols section:

The unnamed prepared statement is likewise planned during Parse processing
if the Parse message defines no parameters. But if there are parameters,
query planning occurs during Bind processing instead. This allows the
planner to make use of the actual values of the parameters provided in
the Bind message when planning the query.

and here is someone who is having problems with the generic plans we
create:

http://www.odecee.com.au/blogs/?p=134

Can we not document this better?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tom Lane wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen <jtv@xs4all.nl> wrote:

Periodically re-plan prepared statements on EXECUTE. ���This is also a chance
for queries that were being re-planned every time to go back to a generic
plan.

The most common problem here seems to be that (some?) MCVs need
different treatment than non-MCVs, so I don't think periodically
replanning is going to help very much.

It won't help at all. The only reason for replanning is if something
about the schema or the statistics change, and we already have got
automatic cached-plan invalidation in both those cases. If you replan
simply because some time has elapsed, you'll just get exactly the
same plan.

The only case that I think still has any merit is where you get a
significantly better plan with known parameter values than without.
The projected-cost threshold might be a reasonable approach for
attacking that, ie, if estimated cost of generic plan exceeds X
then take the time to build a custom plan instead. I'm not sure that
really will fix the problem, but it would be a very simple change to
make to see how much it helps people.

Ideally we would do late binding (bind on the first supplied parameters,
like we do for unnamed protocol prepared queries now), and then replan
if the statistics for later parameters significantly differ from the
ones used for the the initial planning.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:

Can someone explain to me why we only do "delayed binding" for unnamed
prepared queries?

It was a way of shoehorning in some driver control over the behavior
without the protocol bump that would be involved in adding an actual
option to Parse messages.

regards, tom lane

The only case that I think still has any merit is where you get a
significantly better plan with known parameter values than without.
The projected-cost threshold might be a reasonable approach for
attacking that, ie, if estimated cost of generic plan exceeds X
then take the time to build a custom plan instead. I'm not sure that
really will fix the problem, but it would be a very simple change to
make to see how much it helps people.

regards, tom lane

It will definitely help with partitioned tables. It's very common case when
raw data taken from hardware stored in single table first, and later we start
to make partitions for each month/week/day. Feature can improve performance
transparently to client apps.

regards,
Dmitry

Hi Robert,

On Tue, Feb 9, 2010 at 17:43, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen <jtv@xs4all.nl> wrote:

= Projected-cost threshold =

If a prepared statement takes parameters, and the generic plan has a high
projected cost, re-plan each EXECUTE individually with all its parameter
values bound. It may or may not help, but unless the planner is vastly
over-pessimistic, re-planning isn't going to dominate execution time for
these cases anyway.

How high is high?

Perhaps this could be based on a (configurable?) ratio of observed planning
time and projected execution time. I mean, if planning it the first time
took 30 ms and projected execution time is 1 ms, then by all means NEVER
re-plan. But if planning the first time took 1 ms and resulted in a
projected execution time of 50 ms, then it's relatively cheap to re-plan
every time (cost increase per execution is 1/50 = 2%), and the potential
gains are much greater (taking a chunk out of 50 ms adds up quickly).

Cheers,
Bart

2010/2/11 Bart Samwel <bart@samwel.tk>:

Hi Robert,

On Tue, Feb 9, 2010 at 17:43, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen <jtv@xs4all.nl> wrote:

= Projected-cost threshold =

If a prepared statement takes parameters, and the generic plan has a
high
projected cost, re-plan each EXECUTE individually with all its parameter
values bound.  It may or may not help, but unless the planner is vastly
over-pessimistic, re-planning isn't going to dominate execution time for
these cases anyway.

How high is high?

Perhaps this could be based on a (configurable?) ratio of observed planning
time and projected execution time. I mean, if planning it the first time
took 30 ms and projected execution time is 1 ms, then by all means NEVER
re-plan. But if planning the first time took 1 ms and resulted in a
projected execution time of 50 ms, then it's relatively cheap to re-plan
every time (cost increase per execution is 1/50 = 2%), and the potential
gains are much greater (taking a chunk out of 50 ms adds up quickly).

It could be a good idea. I don't belive to sophisticate methods. There
can be a very simply solution. The could be a "limit" for price. More
expensive queries can be replaned every time when the price will be
over limit.

Regards

Pavel Stehule

On Thu, Feb 11, 2010 at 13:25, Pavel Stehule <pavel.stehule@gmail.com>wrote:

2010/2/11 Bart Samwel <bart@samwel.tk>:

Perhaps this could be based on a (configurable?) ratio of observed

planning

time and projected execution time. I mean, if planning it the first time
took 30 ms and projected execution time is 1 ms, then by all means NEVER
re-plan. But if planning the first time took 1 ms and resulted in a
projected execution time of 50 ms, then it's relatively cheap to re-plan
every time (cost increase per execution is 1/50 = 2%), and the potential
gains are much greater (taking a chunk out of 50 ms adds up quickly).

It could be a good idea. I don't belive to sophisticate methods. There
can be a very simply solution. The could be a "limit" for price. More
expensive queries can be replaned every time when the price will be
over limit.

I guess the required complexity depends on how variable planning costs are.
If planning is typically <= 2 ms, then a hard limit on estimated price is
useful and can be set as low as (the equivalent of) 15 ms. However, if
planning costs can be 50 ms, then the lowest reasonable "fixed" limit is
quite a bit larger than that -- and that does not solve the problem reported
earlier in this thread, where a query takes 30 ms using a generic plan and 1
ms using a specialized plan.

Anyhow, I have no clue how much time the planner takes. Can anybody provide
any statistics in that regard?

Cheers,
Bart