Nikolay: Hello, hello, this is PostgresFM, episode number 96.
My name is Nikolay, founder of Postgres.AI, and as usual, my
co-host is Michael from pgMustard.
Hi, Michael.
Michael: Hello, Nikolay.
Nikolay: So pgMustard, as I mentioned last time, is focusing on
explain plans and helping optimize Postgres queries.
And the topic I chose this time is very, very close to that topic,
right?
I mean, it's a subtopic.
I know many people discuss a lot of very in-depth materials around
this topic, but let's just focus on basics just for awareness.
This topic is how plan caching works and generic versus custom
plans.
Because at a very high level, we don't know about them at all.
We know that EXPLAIN shows us a plan.
We know there is plan caching.
We know pgBouncer since recently started supporting prepared
statements, so you can avoid planning time, because sometimes
it's very costly.
For example, if you have a lot of partitions, a lot of indexes,
log manager contention can happen.
So it's good, especially for queries which have very high frequency,
for example, a thousand times per second, it's good to just perform
planning once per session and then rely on the cached plan.
But it's not really once per session, right?
There are more nuances in this area.
So let's uncover them and discuss.
Michael: Yeah, nice.
At a kind of high level, I see people that are using ORMs and
frameworks using prepared statements, sometimes without realizing
it, or sometimes they know but they don't really know what it
means.
Do you see a lot of people, like is that the same in your experience?
A lot of people using this without realizing, or are you seeing
quite a lot of conscious choice, like conscious use of prepared
statements, even for people that aren't using it via an ORM or
similar?
Nikolay: Well, you know my point of view on this world.
On one hand, I think people are quite smart in general,
Michael: but
Nikolay: they are smart only when they are very motivated.
And we know a lot of engineers don't distinguish between binary tree
and B-tree at all, because they are not motivated enough to understand
the differences.
Similarly here, a lot of people, in my opinion, don't understand
that one normalized query in pg_stat_statements might have different
plans depending on parameters.
This simple truth is something that should be understood, but
it can be understood only when you are motivated, when you have
some incident, for example, and you start off thinking, oh, okay,
this query can behave differently depending on parameters.
So this is like basic knowledge.
And honestly, I didn't realize it for quite long in my career
as well.
I thought, okay, this is so, but why is it important?
Well, it's very important because looking at pg_stat_statements
and understanding heavy queries, it's not easy to jump to optimizing
these queries, especially when we talk about mixed situation,
when it's also quite high frequency query and also it has issues
with not perfect indexes chosen, not perfect plan, like not only
just index only scan, that's it.
So it means that we need to understand, okay, we depend on parameters,
but which parameters should we choose for optimization?
Michael: When you say for optimization, do you mean like we get
the PG stat statements and it has these parameterized queries,
which ones do we put in in order to run, explain, analyze buffers?
Nikolay: Right, right, right.
This is unfortunately a big gap between macro and micro levels
of query analysis, which still exists.
Of course, with the addition of query ID to logs and to pg_stat_activity,
we have this bridge between pg_stat_statements and individual
queries, which we can find in logs or pg_stat_activity.
But still, I remember 1 lesson I learned in practice when I spent
1 hour or so optimizing some query.
It was some parameter which was true or false.
And I was trying to optimize for value which was present in 90%
of rows, majority of rows.
And it was, of course, difficult because selectivity is low,
cardinality is high, right?
But then I understood all this hour spent was wasted because
it's never solved.
Never, ever, application runs this value, uses this value in
query.
So The idea is that we need to understand real cases and also
not super rare cases or non-existing at all cases, but we need
to understand the cases which hurt user experience, for example,
and focus on them.
In this case, generic plan is something really weird in this
picture I just painted, right?
So what do you think?
Like generic and custom and maybe let's talk about how planner
uses them and then discuss what should we do about it at all.
So this 5 times rule, what do you think?
Michael: Yeah, I think that catches people out sometimes.
So the way prepared statements works, at least my understanding,
is that you can prepare a query in advance.
And that will do, like, the parse stage.
And it will do, like, several stages up to but not including
the execution of that query, then you can ask it to execute with
parameters.
Now, that then, on the subsequent executions, you don't have
to reparse, like do the set, like do several of those.
Well, planning at first does have to be done again, right?
So it's the parsing and the rewriting stages.
Yes, on a per session basis, we don't have to do several of those
steps.
But there is also this weird kind of initial phase.
So if you've done prepare, if you've prepared the statement and
then you execute it, the first 5 times, Postgres will plan each
of those 5 executions separately based on the parameters that
are given.
So if those are in the case you gave if you used true was it
that was 90% of time You've used true every single time for those
5 times It's gonna plan it the same way each time and on the
sixth execution it will then look at the cost of planning.
And if that cost is less than a generic, or if it's more than
a generic plan that it's calculated, or I think there's some
subtlety around exactly how precise that is, then it will choose
its generic plan thereafter.
It will flip to not planning each new execution.
So sixth time, seventh time, eighth time, ninth time, forever
onwards, during that session, it won't replan.
So now you're not only saving on the parsing, on the rewriting
stages, you're also saving on the planning stage, which you mentioned
already.
But that's not true if, for example, you did 3 or 4 executions
with true and then a couple of executions with false in your
example, then you're going to get a different kind of average
cost for those 5 executions.
Then that will get compared to the generic plan and then a different
decision will get made.
So depending on those first 5, So those first 5 do actually determine
quite a lot what happens thereafter, which is interesting.
But yeah, the 1 time I've seen that the 5 become important is...
This can be really confusing for folks.
If you're trying to debug something and it's fast for a while,
and then suddenly it goes slow, This is 1 of those telltale signs
once you've been around a while.
If you notice that it's fast for 5 executions then slow on the
sixth, you should look into this issue.
Custom plan versus generic plan.
It's kind of a telltale sign, like a smoking gun, or like a,
you know, it's a telltale sign that this could be what you're
hitting.
But aside from that, I haven't really thought too much about
it.
I don't think it's configurable, that 5 number.
I think it's hard-coded.
Nikolay: Yeah, it's, by the way, why 5?
Why 5?
Michael: Yeah, good question.
Nikolay: Some decision, right?
That's it.
But like, what is the generic plan?
Like generic plan is just we don't take into account parameters
at all, right?
It means that we don't take into account this distribution of
values?
Or we take…
Michael: Yeah, good question.
Did you look into how it's calculated in the first place?
Nikolay: No, but it's an interesting question.
Michael: I could be wrong, but I would have guessed that it would
be based on some statistics at the time.
If you were implementing this feature, you would think if you've
got a data distribution there, maybe you'd factor that in.
But it's difficult.
Because if it's skewed, if it's like completely uniform, it's
quite easy.
Like if we're talking about primary key lookups, it's primary
key lookup query and every single one's unique and every single
one's only going to return 1 row.
It's only scanning 1 table, that kind of thing.
The generic plan is really easy because every single parameter
you could provide to that is going to be planned the same way,
even in the custom plan mode.
So generic plan in that case is quite easy.
But in the case where it's a skewed distribution, some values
have more entries than others, it becomes tricky, I think, to
know how you would plan that in a generic way.
So I don't actually know.
Nikolay: For me, a generic plan is a good thing that we don't
have a lot of time to spend finding particular examples in logs
or in pg_stat_activity.
In pg_stat_activity, we have a different problem, the track activity
size.
So this parameter, which by default is 1024, means that really
long queries are truncated and we don't see the full query.
Parameters usually are in the end, right?
Select all columns, our favorite, from table and so many column
names, so I don't see parameter.
Something equals truncated.
I saw it not once.
So when we don't want to spend time, we want something really
fast, this trick prepare statement, we prepare statement and
then we use just nulls as all values.
This gives us a generic plan, but also we can say set plan cache
mode to force generic plan, which exists as we just checked before
we started recording this episode.
Surprisingly, the plan cache mode configuration setting, it exists
since many, many years ago.
So in all current versions, it's supported, right?
Michael: Yes, since version 12.
Nikolay: Exactly.
So 12 is the oldest currently supported version.
A few months left until the end of it.
Michael: Yeah.
Nikolay: Yeah, yeah.
So this trick allows us to, looking at the pg_stat_statements,
just take some normalized query, which has this $1, $2, doesn't
have parameters at all, they are removed.
And just to use this prepared statements with null streak and
get some generic plan and think already how bad the situation
in general, not in particular cases, but overall.
But this trick, as you absolutely correctly mentioned before
this episode recording, doesn't work if we have, for example,
a primary key there.
Because primary key equals null or is null won't work properly
because there is no null constraint, so the planner knows that
nothing will be returned.
Doesn't show us the proper plan at all.
Michael: Yeah, if you don't specify, like, if you don't set plan
cache mode to force generic plan, then you'll get a custom plan
and the planner will shortcut it because there's a really, yeah.
So, but if you do force generic plan on version 12 onwards, then
you will get the generic plan you're hoping for.
Nikolay: Well, that's great.
I didn't realize that.
So yeah, good, good.
But if we are lucky enough to have already Postgres 16, all these
tricks are not needed because we can just say, explain generic
plan.
There is a new option in explain command, and this is very handy.
We just ask a generic plan and explain.
Don't care.
And interesting that we can even use $1, $2, and this query text
right from pg_stat_statements.
This is super convenient.
We can say, explain generic plan and that text.
We can even probably automate this, right?
And see, for example, if we have sequential scans in generic
plans and put warnings somewhere and so on.
But of course, this won't work if we want to execute and we want
to explain analyze buffers.
This won't work.
I mean, in this case, we need to replace $1, $2 with some values.
Michael: Yeah, and it makes sense, right?
We can't do the execution unless we specify something, otherwise
what are you executing?
So it makes sense.
And kudos to Lawrence from CyberTech who added this and has blogged
about it.
So I'll share the blog post about that.
Nikolay: I remember actually this wave from Lawrence and also
Lucas Fittal, who generalized blogging about generic plans and
also getting excited.
Also, Frank Pascho, I think, joined at some point.
I actually don't know who started this, but I remember waves
of, wave of thinking, multiple blog authors, blog posts about
this, generic plans a few years ago and it was great.
Michael: I don't think it's super surprising that this has come
out of a consultancy, like a Postgres consultancy, and that Lucas
is excited about it.
So I think it's 1 of those things that really helps people help
others.
Because if you really know what you're doing, like these people
all do, you can now set up Postgres in a way where you can find
parameter values in the vast majority of cases, or you can reconfigure
things so that it does log the query ID, or you can get your
slowest queries logged with parameters.
So there are these ways of doing it now, but if people haven't
set that up in advance and you're helping them with a production
issue, this is a really helpful tool to give you a clue as to
what's going on.
Now explain will always, there will only ever be a clue, not
because you don't have the execution system, you don't know for
sure why it's slow.
But those clues are often enough for experienced people to get
a theory, get a hypothesis as to what's going wrong, and then
give them something to be getting on with instead of being able
to say, I have no idea why this is like, instead of, it's kind
of like extra information to make the guess a little bit more
educated rather than an actual answer.
Nikolay: Yeah, and we can have a holistic approach now, like checking
everything in an automated fashion, not guessing and so on.
It's great.
So, yeah, what else worth mentioning in this area?
Maybe partitioning, which we like, it's funny that you found
this commit in Postgres 15, which improved the situation.
My general impression, if you use a lot of partitioning, you
should perform major upgrades promptly, not lagging at all, because
every year a lot of things are improved.
So this is a quite in-depth thing that was improved in Postgres
15, saying that now, like partition pruning, so exclusion for
irrelevant partitions based on, like if user asks to return some
data in a query, Postgres can remove partitions which are for
sure irrelevant from consideration.
For example, we have partitioning by time, range by time and
by date, and then the user for sure wants fresh data, definitely
didn't need to look at very old partitions, partitions with very
old data.
But this might happen both at planning time and execution time,
which can be a big surprise.
And you want this to happen at planning time, basically, better.
Because this will mean that we can rely later on cached plan,
or if we use prepared statements, we can rely more on cached plans.
And then we have a very good situation when partition pruning
was already done at planning time, we just execute that set.
But it's tricky when we talk about generic plan, because generic
plan doesn't take into account the values.
So something was improved in Postgres 15 in this area, right?
Michael: Yeah, We tried to look into it before the call, didn't
we?
And couldn't...
Nikolay: Yeah, I couldn't reproduce the problem.
Michael: Yeah, so we tried on an old version before these.
So it would be interesting to hear from people that have reproduced
this.
I didn't see a blog post about it, I saw it referenced a couple
of times.
Nikolay: Yeah, Lukas Fittel in this blog post, the generalized
blog post, mentioned that before we had a problem with generic
plans, that partition pruning didn't work in planning time.
It worked only at execution time.
I couldn't reproduce it, but if it's so it means that, for example,
if you have a lot of partitions with a lot of indexes and at
execution time you involve all of them.
In general, if you have a lot of partitions, just checking them,
not returning everything, it's a big overhead, huge overhead.
But at least at execution time, Postgres doesn't put access share
locks on all indexes, because at planning time it does.
And if at planning time we don't have partition pruning working,
it's a nightmare.
This case
Michael: of manager spikes.
Well, I think that would happen at X.
Yeah.
Nikolay: So,
Michael: a question for you.
Because we do now have this plan cache mode, so plan cache mode
got added in 12, that's 1 of the things we can use to force generic
plan, force custom plan while we're debugging, while we're trying
to, pre-version 16 releases, before we had this explained generic
plan, we could use that for debugging.
But I don't think I've ever seen anybody change.
So, Plan cache mode got added.
You have 3 options, Auto, Force Custom Plan and Force Generic
Plan.
Auto is the default, so Auto acts as it did as...
Nikolay: As you described.
Michael: Yes, so the first 5 executions...
5
Nikolay: times custom, then decision is made.
Yeah.
What choice?
Well, I
Michael: was going to ask because I think I got the impression
that some of these ORMs, like ActiveRecord, for example, uses
prepared statements.
I got the impression the main reason they do so is to protect
against SQL injection attacks, not for performance reasons.
And that might be not quite accurate.
But there is this other benefit that seems to be mentioned quite
a lot around prepared statements.
Now, I wondered if you'd ever seen anybody change, like if you
only were, if you only were using prepared statements for.
SQL injection protection, and you didn't, you weren't as bothered
about the performance benefits in some cases, or pitfalls in
other cases, you might want to allow Postgres to replan every
single time.
I can imagine some people considering turning force custom plan
on.
I can imagine like the upside of doing so.
I can't see quite the same upside for doing forced generic plan
because all you're doing is saving those 5.
Well, I think you'd only save those 5 executions and then you
flip to the generic plan anyway.
Maybe there's another case for that.
But yeah, have you seen anybody changing that setting in any
cases?
Nikolay: I see everyone doesn't know about it, almost everyone.
It's very deep.
It's not super, super...
It feels like some kind of fine tuning.
I see people have fears, for example, to rely on prepared statements
globally, because this idea, what if global plan will work not
well?
Michael: Right?
And...
So generic plan is bad for a specific parameter...
Nikolay: If generic plan is chosen, it's cached, and then it's
bad for some cases and we have performance degradation for particular
parameters.
But also I think this idea to general, to use prepared statements,
I don't think these days it already matters as protection from...
I think the main reason is planning time is usually fast, but
not always fast, as we know.
And the idea that during planning time Postgres locks all indexes,
it's a super big surprise.
Many people, including myself, had not long ago.
And we had benchmarks showing this small overhead adding with
each index for select, not for update, for select.
And at some point when total number of relations, both tables
and indexes, reaches 16, which is also a hard-coded constant.
If a query is very frequent, 1000 or 2000 per second, primary kilo
caps are usually so.
We have a log manager spike, and partitioning increases the probability
of it.
And that's why getting rid of planning is good.
And when you get rid of planning, it's worth understanding this
behavior of how caching works and generic versus custom.
But just changing this globally, I'm not sure this is a popular
decision.
But of course, my experience is very limited.
Michael: Let us know if you have changed it.
It'd be interesting to hear from you.
Nikolay: Yeah, it's an interesting topic anyway.
But in general, I think it's worth spending efforts to get rid
of planning for high-frequency queries, especially for partitioning
tables.
Michael: I have one more related question.
So now pgBouncer supports prepared statements.
Nikolay: I
Michael: saw they have a max prepared state.
Well, so it's off by default, which I didn't realize.
They have a max prepared statements parameter.
Do you have any opinions on tuning that?
Or like I saw that they said a default of 100 might make sense.
Like, might be a sensible start.
Nikolay: Yeah, some high value is good.
I think it's like, depends on the situation, of course, right?
I mean, but in general, it's not an issue to just increase it.
Yeah.
Cool.
But again, I see the tendency to be afraid of turning prepare
statements globally in already existing heavily loaded projects.
If you start from scratch, it's a very good decision to make
to start using prepared statements from the very beginning.
And then you just grow and see the problems, fix them as usual,
like you would do anyway.
And then there's confidence level of using already there, but
switching for a big project from 0 to 100% coverage, it's a very
hard decision to make.
Michael: That's hard as in scary or?
Nikolay: Just people are not sure and that's the problem.
And there is no good path.
Maybe there is a good path to it.
But in a couple of big companies I observed recently, people
decided not to proceed with this after a lot of consideration.
And it's kind of an interesting topic.
Instead, It feels safer to start using prepare statements only
for specific queries.
Michael: That's a slow way in.
Maybe for new features.
Nikolay: Not new features.
Not at all.
Again, the problem is usually this log manager overhead.
When we have 1,000, 2,000 QPS, we see very simple queries, select
star from or select something from table When we have 1000-2000
QPS, we see a very simple query, select something from the table
where ID equals something, primary queue lookup.
And if we have planning time every time, select itself is fast,
planning itself is also fast.
But if you have a lot of backends competing, access share lock
is not a problem at all.
But lock manager has overhead because of fast path is false.
And this is what multiple companies experienced recently.
And Jeremy Schneider said, local managers strikes back or bites
back or something like that.
Last year was a local manager year in terms of performance issues
people experienced.
Michael: So you're saying, yeah, so I'll share the episode where
we discussed that in more detail.
And so you're saying they choose to turn prepared statements
on only for those queries, because it reduces the issue there,
but not globally.
Okay, makes sense.
Nikolay: Just to remove these hotspots.
Yeah.
Only for these primary lookups.
Michael: I mean, we're talking, So if you're talking about these
super fast queries, even though both execution and planning time
are fast, it could be that planning time is more than execution
time.
Like it's quite common.
Nikolay: It happens.
Michael: So even if it wasn't for the issues you're describing,
you could maybe increase throughput.
Or like there's a lot of overhead to be gained by not having
to plan those queries.
Nikolay: Yeah, shave off planning time is a good idea in many
cases in general.
It's like, for example, partitioning, right?
If partitioning is there, planning time can be high if you have
a lot.
It can depend on the number of partitions.
This is the problem as well.
In this case, of course, getting rid of it is good.
Nice.
Cool.
Anything else you want
Michael: to add to this 1?
Nikolay: No.
It's an interesting thing to keep in mind when dealing with plan
optimization.
So use explain-generic-plan in Postgres 16 and in future Postgres
versions.
Michael: Yeah, if you can't, as a starting point, or if you can't
get
Nikolay: parameters.
Michael: Lazy mode.
Lazy mode.
Nikolay: If you don't want to go to logs to find examples, or
maybe a log min duration statement was not configured somehow.
Michael: Exactly.
If it wasn't configured for the time where you're having issues,
or if you can't turn it on easily, like if you've got a lot of
process at your company before changing some of these parameters,
you know, so there might be reasons why you can't get real values.
But of course, I think The reason I don't know as much as maybe
I should on this is, if you can get the real values, performance
optimization work becomes so much easier.
Nikolay: Of course.
Michael: There's less guesswork.
Nikolay: Yeah.
This generic plan should be something like a partial solution
in terms of analysis.
It's a partial analysis.
It always should be with remark, you know, this is not, maybe
not it.
Maybe but maybe not.
Michael: Yeah.
Like a stepping stone on the way to the solution, but like it's
not a solution in itself.
Nikolay: Right.
On the other hand, it might give very quick insights what to
do and how to speed up.
So yeah,
Michael: good point.
Yeah, good point.
Yeah.
Nice one, Nikolay.
Thank you so much.
Nikolay: Thank you.
Have a good day.
Bye.
Michael: You too.