Nikolay and Michael discuss planning time in Postgres — what it is, how to spot issues, its relationship to things like partitioning, and some tips for avoiding issues.
Here are some links to things they mentioned:
Here are some links to things they mentioned:
- Query Planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.html
- Are there limits to partition counts? (Blog post by depesz) https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts
- Nikolays recent experiment https://postgres.ai/chats/01920004-a982-7896-b8cb-dfd2406359b0
- PgBouncer now supports prepared statements https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0
- “The year of the lock manager’s revenge” (from blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability
- pg_stat_statements.track_planning https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.42.9.2.4.1.3
- pg_hint_plan https://github.com/ossc-db/pg_hint_plan
~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~
Postgres FM is produced by:
- Michael Christofides, founder of pgMustard
- Nikolay Samokhvalov, founder of Postgres.ai
With special thanks to:
- Jessie Draws for the elephant artwork
Creators & Guests
What is Postgres FM?
A weekly podcast about all things PostgreSQL
Michael: Hello and welcome to Postgres.FM,
a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard,
and this is my co-host Nikolay,
founder of Postgres.AI.
Hey Nikolay, what are we going
to be talking about today?
Nikolay: Hi Michael, let's talk
about planning time and what
can increase it, potentially.
Michael: Nice, And this was your
suggestion.
Why is it something you're thinking
about at the
moment?
Nikolay: Well, it's we are making circles
around this topic all the time
because basically almost all of
our customers, which are mostly
fast-growing startups, they reach
the point where partitioning
is needed, they reach the point
where LockManager contention
happens, number of indexes becomes
concerned, and also many of
them still don't use prepared statements
mostly because PgBouncer
didn't support it until
very recently, right?
This means that planning time being
basically unnoticed for quite
long suddenly becomes an issue.
And we often show them, oh, it
looks like planning time is an
issue in these cases.
And actually, especially partitioning
thing is like, there's
always a question, especially for
time series.
It's always a question how big
partitions should be, which can
be translated to how many partitions
you will have.
And if you partition not well,
you may end up having many thousands
of partitions.
They will be very small.
But if you don't have prepared
statements, you can end up having
a huge problem with planning time.
And planning time can exceed execution
time for simple primary
key lookups, right?
This is not fun.
Michael: Yeah, it's not the only
case of that, right?
But it definitely makes sense that
it's a common one.
Nikolay: Yeah, Basically, last
week we discussed with a couple
of companies how to properly implement
partitioning.
And this question was in the center,
what size of partitions
to choose?
And the rule of thumb is like,
don't let them be larger than
100 gigabytes, right?
Because this is the golden rule
of partitioning.
It's time to apply partitioning
when you have risks already table
exceeded 100 gigabytes.
But it also can be applied to partitions.
Also, they should be balanced in
terms of size.
This is usually a good idea to
do.
But on the other hand, if you make
them tiny, you end up having
a lot of them and it's not good.
Why is it not good?
Because of planning time, mostly.
This is not the only reason why
you don't want a lot of partitions,
but this is 1 of the biggest reasons.
If you have, for example, 10,000
partitions, you might have
dozens of milliseconds or like
maybe a second of planning time
for each query.
If prepared statements are not used,
every time a SQL query is
executed planning is happening
in Postgres, right?
Michael: Yeah, and you're talking
about the best case.
Planning time can expand depending
on the complexity of the query.
You're talking about for the simplest
possible query.
Nikolay: Very simple.
Bit real index scan.
Michael: Yeah.
Is it worth us explaining what
are we talking about?
What is planning time?
Nikolay: Yeah, let's do it.
So when query is executed, there
are stages, right?
Do you remember stages?
Planning, it means, well, The planner
is based on statistics
and considers...
The goal of the planning is to
choose the best plan as fast as
possible.
It might be wrong.
Very often it's wrong, actually.
Not very.
It happens.
For me, it's every day I deal with
such cases because I see usually
problems, not like ideal situations.
This is when my team and I are
involved when something is wrong.
So it needs to choose as fast as
possible the kind of optimal
plan.
And then when a plan is chosen,
execution happens.
Executor executes according to
the plan.
And there are like many, many,
many possible plans.
Many possible plans.
Sometimes I think infinite number
of possible plans.
Right?
Michael: Well, it depends, right?
Like If you're doing a single table
select and there's no indexes,
there maybe is only 1 possible
plan.
But once you include...
Nikolay: What about prioritization?
There's a couple of possible plans,
right?
Michael: Yeah, but all I mean is
as the...
It gets important, like, for example,
when you start joining
multiple tables and you start to
realize that you have multiple
scan choices, multiple join orders,
multiple join types that
you could...
Nikolay: Multiple indexes are present.
Planner needs to choose them properly.
So, yeah.
Michael: And these things are combinatorial.
These things multiply to create
the number of total possible
plans.
And it quickly, like as the number
of joins, for example, increases,
that can explode exponentially.
So there are some things in Postgres
too.
Nikolay: There's also a genetic
optimizer, right?
Michael: Yes.
So because it can...
Well, I guess the simple version
is that the planner isn't always
looking for the best plan in those
cases.
It's sometimes looking for a good
enough plan in a reasonable
time frame.
Because if we're taking way longer
to plan the query, if we take
multiple seconds to plan a query
that's probably going to execute
in a few milliseconds, it's probably
a complete waste of time.
So there's a balancing act here
for complex queries.
But for simple queries, it doesn't
make sense to be paying dozens
of milliseconds for a 0.2 millisecond
primary key lookup.
Nikolay: Right, So the process
of finding ideal plan is called
optimization, plan optimization.
And the planner deals with basically
several things.
First of all, decisions are of
course heavily depend on the version
of Postgres you use because all
the time improvements are being
made.
Second of all, it uses statistics
currently present in Postgres.
That's why it's important to keep
autovacuum tuned so it maintains
the statistics.
And this is not only pg_statistic,
it's also estimated number
of tuples in Postgres rel pages,
so it's number of pages the
table or index have.
This is super important.
And finally, it also takes into
account the settings Postgres
currently has.
An interesting point, as I also
mentioned many, many times, those
who listen to us not recently,
they know it, right?
It's Interesting that the planner
has no idea about the real
physical capabilities of your machine.
It doesn't know what type of processor
you have, how many cores,
how fast disks are, or how many
gigabytes of RAM you provided.
It has no idea.
It relies on the settings.
First of all, costs.
So, random page cost, seq page
cost, and so on, like CPU, tuple
cost, and so on.
And also, settings like work mem
and some estimate of available
memory called effective cache size.
And you can put any number there
even if you don't have those
gigabytes of RAM.
This is bad because if you forgot
to tune your server, Postgres
might not know that you have a
lot of RAM, like a terabyte of
RAM, but Postgres still thinks
you have only 1 gigabyte, for
example.
It's easy to be in this situation
and it's bad.
But at the same time, it's good
for us who deal with a lot of
experiments, because it means that
we can fool the Postgres and
say, oh, you know, we have the
same number of gigabytes as production
has.
And this helps us to reproduce
the problem in lower environments
when we have a much weaker machine.
This is great for experimentation.
So good and bad.
So that's it.
This is everything that matters
when optimization happens, when
the planner chooses the plan the
executor will be using.
Michael: Yeah, anybody that's familiar
with Explain, I've had
success describing planning time
as the time it takes Postgres
to come up with that explain plan.
That's what it's doing.
And you can even see this.
By default, explain on its own
doesn't show planning time.
But if you do explain summary,
So explain with the parameter
summary, it will show you planning
time as part of that.
So it's quite a cool way of thinking.
Yes, exactly.
At the bottom.
Nikolay: Or if you ask executor
to be involved and you run explain
analyze, then summary is being
turned on automatically, right?
Michael: Yeah.
And you'll see the planning time
and the execution time then.
And the planning time is roughly
the time it took to run Explain
on its own, and the execution time,
in addition to the planning
time, is the time taken roughly
to run explain analyze.
So that's quite a cool way of thinking
about the difference.
Nikolay: Yeah.
And the worth mentioning, again,
those who listen to us for long
know it very well, the word buffers.
Since some version, like several
years ago, it was implemented,
I think Postgres 13 maybe.
You can use buffers, expand buffers
even without analyze, only
planning phase, and you can see
how many buffers were involved
into planning.
And this is super cool because
usually bad timing is because
of a lot of IO, which is buffers,
right?
IO is number 1 reason why Postgres
can be slow in anything.
Michael: Yeah.
And you might think like, what's
Postgres reading during planning
time?
Nikolay: Yeah.
Yeah.
Actually, you know what, I explained
some picture, which is like
maybe true in 99%, but I now remember
a case where something
else was happening.
It was some interesting query.
I don't remember how many tables,
but definitely it was a join,
maybe a couple of joins.
And I remember planning time was
completely out of normal.
It was like half a minute or so.
Michael: I think I remember this
case.
Was it the merge-join?
Nikolay: Exactly.
Yeah.
It was not merge-join.
Merge-join was not considered the
best plan, it was not chosen,
but it was the reason why it was
so slow.
Considering merge-join path, the
planner used not only what I
just described, but additional
information.
It tried to find the minimum or
maximum values for some subsets
of data, edges, real values from
table.
So it needed to, during planning
time, it needed real values.
And this, due to some reason, was
super slow.
So planning became many, many,
many seconds exceeding statement
timeout and failing.
It was extremely hard to diagnose,
actually.
And I actually...
Michael: Do you remember why?
Nikolay: Yeah.
Why what?
Michael: Why it was hard to diagnose?
Nikolay: Because the explain doesn't
show other options.
It shows only the winner.
Michael: Well, and also extra reasons
it was difficult.
I remember talking to you at the
time, auto_explain doesn't show
planning time, and therefore, when
you're trying to diagnose...
It's not
Nikolay: a problem for us because,
well, we didn't use it at
that time for this project.
We didn't use auto_explain at all,
but we had DBLab Engine installed
and I knew when these problems
started happening, so I quickly
rewinded, created a snapshot and
a clone.
So yeah, I worked on the clone
and reproduced the problem easily.
Interesting that like 1 hour later
it was different.
And statistics changed somehow
and planner stopped considering
merge path, merge join path.
Yeah, so it auto healed, but it
was super scary what happened
1 hour ago, right?
But thanks to DBLab Engine, we
easily reproduced it.
So I agree with you, auto_explain,
not showing planning time
at that time.
It was an issue, but not in this
project particularly.
What was an issue is you reproduced
it, you see super slow planning
time, but you have no idea what's
happening.
And you try to understand if we
had a way to see second best,
third best plans.
In this case, we probably, yeah,
second best probably would be
merge join, it lost eventually,
but during consideration of it,
it was like Postgres planners spent
a lot of time considering it
because it needed real values.
I think what helped in that case,
actually, is a friend's advice,
because Kukushkin first said, have
you tried setEnableMergeJoin
to off?
This is weird.
But once I did it, planning time
went to almost 0.
Like, boom.
It was interesting case.
And of course, when you try to
dig deeper, you can use like Perf
or BPF and understand what's happening
under the hood and in
which functions Postgres spends
time.
And you quickly find its merge-join
path.
And then read the code, because
it's open source, this is cool.
Read the code and understand that
Postgres, analyzing merge-join
sometimes needs real values from
table.
Wow.
It was exotic, honestly.
Michael: Yeah, that is exotic,
but it's also very helpful in
understanding how the optimizer
is working.
Because it isn't exhaustively calculating
every plan that is
possible.
It can give up on plans once it
gets to a sufficiently high cost.
So Because the way enable_mergejoin
off is working, it's penalizing
merge join.
It's not saying you can't ever
have a merge join.
It penalizes it cost-wise, at which
point the planner gives up
on that.
Nikolay: Yeah, it discards very
quickly because cost is already
super high.
Michael: But I don't think that's
how everybody thinks of how
the planner works.
They think it's going all the way
to the end of each option.
But it can abort early.
And this is 1 of those trade-offs
it's making.
It will sometimes make a mistake
because it seems like it's gonna
be an expensive plan, but then
it isn't.
So it isn't always looking for,
in my opinion, the fastest plan,
it's looking for a fast enough
plan.
Like that's quite like, well, maybe
I'm wrong in that.
Like it is looking for the fastest,
but it uses these shortcuts
to avoid spending excessive time
to do so.
Nikolay: Yeah, yeah, exactly.
As usual, I like exotic and hard
problems, but let's maybe switch
to simple things.
Michael: Yeah, good idea.
Nikolay: Yeah, you sent me a very
good post by depesz, Hubert
Lubaczewski, depesz, right?
And it was 3 years ago.
He was curious about, is it really
so that Postgres doesn't allow
more than 65,000 partitions?
And it turned out it's not so.
He reached 100,000 partitions.
But at the same time, Hubert checked
the planning time.
How exactly the planning time depends
on the number of partitions.
And this is back to
Michael: the initial question,
the initial reason you were looking
into planning time.
Nikolay: Exactly.
Yeah.
We don't want a lot of partitions,
many thousands of them, because
planning time can be already high
— dozens of milliseconds, maybe
hundreds of milliseconds, and so
on.
Basically, the growth is linear.
If you double the number of partitions,
you double planning time.
Likely.
So of course it depends on the
query you use, I guess.
But the simple rule is just if
you have a lot of partitions,
you have bad planning time.
It means we need to be...
I also think Postgres versions
matter a lot here, many optimizations
were made, right?
Michael: Well, I'm aware of 1.
So, depesz's test, he says in the
comments that he tested on
version 14, which is important
because there's a commit in version
15 a couple of years ago from David
Rowley that says that in
the release notes, it said, improve
planning time for queries
referencing partition tables.
This change helps when only a few
of many partitions are relevant,
which is the case here where we're
doing a primary key lookup.
Like, if we're just searching for
a single row in a single partition,
all of the 100,000 partitions,
only 1 of them is relevant to
this query.
We can prune that at planning time.
In those cases, planning time should
be much reduced as of version
15 onwards.
Nikolay: Well, you know, I used
different table structure and
queries, but it also was primary
key lookup.
But basically, I reproduced the
same experiment using our AI
system.
I'm just checking right now, depesz
had for 10,000 partitions,
he had like 40 milliseconds or
so, right?
30, 40 milliseconds.
It's pretty much the same as what
we observed with our AI system.
But I also used so-called shared
environment experiments, so
I was not alone on the machine.
So That's why I involved buffers
in my analysis.
Because this linear time growth,
according to my benchmarks,
is explained by linear growth of
buffer hits associated with
planning.
So if you have 10,000 partitions,
it might be several dozens
of milliseconds.
So what do you think?
What are you looking for?
Michael: I was looking at depesz's
post just to see if he gave
enough details around how, like,
did he pick the best of, you
know, sometimes when you're doing
this kind of experiment, you
would run the query a few times
and pick the fastest or the average.
He
Nikolay: did.
Several.
I didn't.
Yeah.
He used several, not many, I think
3 or maybe 5 runs only.
I used only 1 run for now.
I think it's a good idea to check,
to convert this experiment
to more like full-fledged experiment
maybe on separate machine
and maybe even with pgbench and
have many many competing like
not competing concurrent SELECTs
the idea is it's again about
buffers many many buffer hits for
planning and this explains
the growth of planning time.
Michael: Yeah, I like how in your
experiment, I like how clearly
linear the chart with buffers is,
whereas the 1 with timing,
it just, It looks like it might
be linear, but there's enough
noise that it's tricky to see.
Although there are a couple of
dips in the buffers chart, so
there's something interesting going
on there.
Nikolay: Yeah, I'm also interested.
We also discussed before this call
that it really matters, is
it first query when you just connected
or even not involving
prepared statements.
If you run the very first query,
you have extra penalty because
in this connection, information
from system catalogs is not cached
at all.
And this adds extra to like extra
buffer hits during planning
time.
Michael: Yeah, that's what we thought
it was.
I don't know for sure.
And it seems really interesting
like what it could be that's
going on there.
The reason I thought it might be
that is I've seen in the past
when running experiments, if I
run ANALYZE, like nothing to have
explained in this case, just gathering
new statistics for the
table, I've seen planning buffers
spike on the next query execution
and then come back down on the
second execution.
So that makes me think it might
be statistics related.
But yeah, I've seen VACUUM drastically
reduce buffers as well,
not VACUUM ANALYZE, just VACUUM
on its own, and definitely not
VACUUM FULL.
So whilst buffers are much more
reliable and stable than timings
for this kind of experiment, there
are a few things that can
affect buffers still.
Nikolay: Yeah, yeah, yeah.
So anyway, I couldn't reproduce
VACUUM thing and ANALYZE thing,
but I can imagine if you have complex
query, rebuilding statistics
leads to many more hits from pg_statistic,
for example, of new
data just collected.
Especially if you have a lot of
buckets, if the default statistics
target is increased and so on.
It's a lot of data to hit additionally.
I don't know, it's interesting.
But anyway, back to partitioning.
The current recommendation would
be, let's not allow the number
of partitions to grow to many,
many thousands, unless we are
okay with increased planning time.
We can be okay with increased planning
time if we use, first
of all, pooler matters.
So the situation when it's the
very first query in session just
established, it's very rare, right?
It's good if it's really rare,
and the pooler helps.
Even application-side pooler helps.
And second, of course, prepared
statements just help us shave
off a lot of planning time situations.
You remember Dirk from Adyen discussed
problems even if you use
prepared statements, still there
might be interesting problems
and we had a very interesting episode
number 100, right?
It was an interesting discussion,
but it's already an advanced
topic and at bare minimum it's
good if you know planning time
is going up.
It's good to consider prepared
statements and PgBouncer
supports them now, so it's great.
And I must say, like, dozens of
milliseconds is already super
slow overhead for planning.
If we aim to have all queries fast,
fast for me means in OLTP
as usual.
This is episode number 1.
What is slow query?
Slow query is if it exceeds 100
milliseconds, 200 milliseconds,
because this is human perception,
200 milliseconds.
Most people already see it's quite
slow.
And if 1 HTTP request consists of multiple SQL statements, this
means we should be below like 10 milliseconds already.
But if planning time adds 50 milliseconds, for example, if we
have 20,000 partitions, for example, wow, it's not a good situation
to be in, right?
So prepared statements should be a good tool to fight with this
problem.
Or just keep partition numbers low.
Michael: Well, a few times you've said, and I completely agree
with you, that our primary server's CPU is our most limited resource,
and planning time is CPU.
It's a pure use of that, and if we can shave it, why not?
Nikolay: It's CPU, but I insist it's gain buffers.
It's just buffer hits, it's related to memory, but yes, it's
CPU, yes.
But still it's I.O.
I consider I.O.
In a wide sense when we involve communication to memory also,
consider it I/O.
Not disk I/O, of course, in this case.
Most of the time.
It can be buffer reads as well, but in rare cases.
Sometimes we see buffer reads involved into planning as well.
Michael: You mean like shared read rather than shared hit?
Nikolay: Yeah, I call them shared buffer reads because...
Michael: Shared...
Nikolay: Yeah, we discussed it.
I consider this bad naming because it's I/O operations, not amount
of data.
Right?
Right.
Yeah.
So, and what else?
Indexes, right?
If we have a lot of indexes, planning time is increasing, increasing,
increasing also.
And I suspect it's not linear even, but it's worth double checking.
So if we have, for example, 10 indexes, that's some penalty and
planning time in a primary key lookup can be already like 1
millisecond or so, half of millisecond.
But if we go, like if our table has many columns, we are tempted
to have dozens of indexes.
And that's really bad.
And also, not only planning time increases itself due to buffer
hits, again, buffer hits also grow here, but also locking.
When planning happens, Postgres applies access share lock to
each object involved.
All indexes of a table are locked by access share lock.
And this is quite expensive, I would say.
If you have thousands of queries per second for this primary
key lookup, it might be a problem,
especially if you exceed 15
indexes, because the total number
of objects locked already reaches
16, which is a threshold where
when Postgres cannot, like above
16, it cannot use FastPath for
locking in LockManager anymore.
And we discussed this as well.
And this is the number.
Michael: Is that number being increased
in?
Nikolay: I'm not sure.
There are discussions to make these
things configurable.
Actually, I think I raised this
question.
It turned out not to be so simple.
I'm not sure.
I think in 2017, nothing happened
with this.
So what helps here?
Like, add more CPU, more cores,
or decrease number of indexes,
or again, prepared statements, right?
Prepared statements.
What else?
What else helps in this situation
when you need?
Yeah, I don't know.
Michael: Maybe even denormalizing.
If you've got a table of 15 indexes,
why do you have 15 indexes?
Nikolay: Or this also helps, decrease
QPS of this kind, caching
on application side.
If you had 5, 000 queries per second,
I see it all the time.
So every click, an application
reads from user's table checking
who is this.
Nothing changed.
So cache it.
Why are you reading this all the
time?
Or Rails, recently we discussed
with 1 customer.
Rails code is constantly bombing
Postgres system catalogs,
checking some pg_class, pg_attribute,
and so on.
Checking schema all the time, all
the time, like thousands per
second.
Why?
On replicas, nothing changed.
It's changing, but not so fast.
So lack of caching everywhere,
and this quickly can become a
problem, especially if you don't
use prepared statements again.
But interesting that we can combine
this problem and they can
amplify.
If you have, say, 10 indexes, it's
enough to have 3 partitions.
Even 2 partitions is enough if
you haven't checked partition
pruning and some queries involve
both partitions.
You already have a situation where
22 objects need to be locked
by access share lock and lock manager
contention might strike
you.
Jeremy Schneider had a great overview
of various works in this
area from various companies.
It's both about execution time
but mostly about planning time
because planning time is worse
because this is when Postgres
locks all the objects.
During execution, it locks only
what is really needed.
So yeah, the worst case, you have
partitioning, not many partitions,
but also indexes, and then a lot
of QPS, and it can put your
server down completely because
of LockManager, lightweight lock
contention.
Michael: Yeah, I think you've talked
about people who could be
doing things for good reasons still
getting caught up.
But I think there are also a couple
of cases where, like around
partitioning, people running a
lot of queries without the partition
key being supplied in them.
Like that kind of thing can...
Nikolay: How to catch those situations?
What would you do?
How to verify that?
For example, imagine we are prepared.
We prepared already a good partitioning
approach, like with 0
downtime conversion, everything.
And we need to double check that
all queries will have partition
pruning involved.
How to check it?
Michael: Holistically.
ALEXANDER Good question.
Well, actually, that brings me
back to an idea.
We've got pg_stat_statements and
we can now look at some planning
related data as of version 13.
It was a big, big release.
Nikolay: It's not on.
You need
Michael: to enable it.
Yeah.
Why is it off?
Nikolay: Good question.
Since it's off, many people, including
actually myself, still
have some fears that the observer
effect might strike you in
some cases.
So yeah, honestly, yesterday we
discussed with 1 of our clients,
we discussed that we should definitely
consider enabling it.
It's a good thing to have.
Michael: It would be good to hear
what the observer effect is
in that case.
Nikolay: I think we had experiments
checking this with synthetic
benchmarks and it was very low,
this overhead, but it doesn't
eliminate, synthetic benchmarks
don't eliminate all the fears,
right?
Michael: Of course, of course.
Nikolay: So yeah, I'm curious if
people have planning time.
Well, I know projects heavily loaded,
huge clusters, planning
time enabled.
Michael: So you mean pg_stat_statements.track_planning?
Nikolay: Not planning time, sorry.
Yes, yes.
It's not only time, it's also I,
right?
I mean, buffers for planning phase.
They are fine, but I'm curious
about opposite when people you're
not fine and decided to keep it
off otherwise I will call it
another default bullshit
Michael: right and it's
Nikolay: we have a collection of
them like random page cost or
what
Michael: this one's I think this
one's even worse because pg_stat_statements
isn't on by default.
So people already have to turn
that on.
And They
Nikolay: already agreed to pay
overhead price.
Michael: Yeah, but also it means
that in a default ship, like
if Postgres ships by default, even
if this parameter was on,
by default it would be off still
because pg_stat_statements isn't
on.
So I think this one might be one of
the easier ones to change.
Nikolay: Yeah, yeah.
Well, we need to check discussion.
I might ask our AI to check what
was discussion and provide summary.
Michael: But you asked how to monitor
for that.
I would expect to see issues there
quite quickly.
Nikolay: I'm interested in such
issues.
So anyway, I agree this is important,
observability for planning
overhead.
Michael: Also just P95, P99, like
query latencies, I would expect
to suffer.
Nikolay: But I think also I have
in mind an approach we discussed
with a couple of clients actually
over the last few weeks.
If you enable partitioning, it's
worth checking with auto_explain
in lower environments, enabling
it for everything and just having
it enabled and playing functionality.
It's about testing coverage, right?
If you cannot reproduce the whole
workload, of course, there
are chances that you miss something.
But it's good, then you can just
analyze logs and see if there
are plans which involve multiple
partitions.
But you need to partition properly
as well.
You need to have multiple partitions
for all tables, which are
partitions.
Michael: Yeah, true.
And if you were doing this and
you had thousands of partitions,
that would be a lot of information
in the logs.
And still no planning time information.
Nikolay: Yeah, yeah.
But we are interested in plan structure
in this case.
And in lower environments, we usually
like if it's a smaller
database, for example, it's not
thousands of partitions.
Like for example, we say, okay,
we want to have at least 10 partitions
for each partitioned table, and then
we just check plans and with
something, some analysis, we can
analyze them.
And it's a separate question how
to analyze them, but anyway,
we can find holistically find bad
plans.
But I like your idea about pg_stat_statements
as well.
I wish it had, you know, it had
information about plans more,
not only timing and buffers, but
for example, how many objects
were locked, for example, or involved
in the plan, or I don't
know, like some additional things
here.
For example, just was partition
pruning involved?
How many times was it involved
in plans?
You know, there's a column called
plans.
How many plans?
Partition pruning, question.
Because partitioning is hard.
It has so many surprising aspects,
I would say.
So many surprising aspects.
Michael: I have some sympathy with
them.
I think pg_stat_statements already
has a lot of columns.
Nikolay: Yes, I agree.
Still not enough.
We have interesting work happening
in the area of monitoring,
and we discussed not only pg_stat_statements,
but query analysis.
And the holistic table of query
analysis is so wide, because
we take every metric and have 3
derivatives for all metrics.
Time-based differentiation, calls-based,
and also percentage,
except column calls.
For this column, it doesn't make
sense.
Calls per calls is always 1.
So it's like 4 times more minus
1.
It's so many columns to present.
Yeah, we don't need them all the
time, but I can find cases when
some of them, like almost every
one is useful in some cases.
So query analysis becomes harder
and harder, wider and wider
in terms of how many metrics we
have.
And it's still not enough.
That's it.
So yeah, good.
I think we covered this topic quite
well.
Michael: I have 1 last question
for you.
Do you ever change people's from
collapse limit or join collapse
limit?
Nikolay: Oh, interesting.
I honestly like last, for example,
12 months, I don't remember
cases of it at all.
Somehow.
In the past I had cases where we
considered it and I think even
applied it, but it was so long
ago.
Somehow.
Michael: I think the default is
quite good.
The default for both of them is
8.
I think it works pretty well.
Nikolay: It works pretty well.
And also, I don't know, like when
I see 12 tables involved in
plan joins and like I already like,
oh, genetic optimizer already
here, right?
It has to turn up to 12 threshold,
no?
Michael: Yeah, I only learned this
today.
But I think because of from collapse
limit and during collapse
limit, GECO never kicks in.
Nikolay: Wow, okay.
I have old memory.
Okay.
Michael: Me too.
Like, well, or maybe I misunderstood
originally, but unless you
raise those to above the GECO threshold,
This is my new understanding
based on reading the docs.
Unless you raise them above the
Jack Ray threshold, it will never
kick in.
Nikolay: Well, yeah.
Somehow recently I mostly deal
with different problems when only
a few tables involved into plans.
Makes sense.
Yeah, but my experience is not
everything, right?
So I'm sure there are cases where
collapse limits matter and
worth considering, but just didn't
happen to me recently.
Michael: No, I was just interested
from like a it's 1 of the
things we look out for high planning
time It's 1 of the things
we recommend looking at in case
someone has tweaked them For
example, if somebody's increased
them too much that can massively
balloon planning time Interesting,
but I wanted to know like
in the real world for you.
Is it something you see us?
It sounds like me
Nikolay: Yeah, cool.
Well, there might be more cases
we even didn't see yet.
Michael: Oh, of course, always.
Nikolay: Always, yeah.
So because Postgres planner is
complex.
It's not as complex as SQL Server
or Oracle planners.
Michael: Not yet, no.
Well, hopefully never.
Nikolay: Already complex enough,
I would say.
So yeah.
Good.
Oh, by the way, do you know if
we use pg_hint_plan?
Michael: Oh yeah.
pg_hint_plan.
Nikolay: Right.
Because we tell the planner what
to do directly.
Does it help fight high planning
time?
Michael: I don't know.
Nikolay: Good question.
It would
Michael: make sense that it would,
but I don't know where it's
hooking in.
Nikolay: Yeah.
Yeah, I don't have this used in,
I don't observe it used in production
at all, only non-production.
So I also
Michael: don't see.
Okay, good.
Question to our listeners.
Nikolay: Yeah, it's homework.
Michael: And also, the other thing
I wanted to ask is if anybody
using some of the more complex
extensions that hook into planning,
like for example, Citus or Timescale.
Yeah, I've seen more planning time
issues.
But it just feels like a coincidence
at the moment.
I haven't looked into if it's definitely
the causal rather than
just, you know, what's the correlation
versus
Nikolay: causation.
But
Michael: yes, that's the 1.
So I don't know if it's just coincidence,
but I tend to see more
planning related issues with the
more complex extensions.
Nikolay: Maybe some extensions
help improve planning time, not
make it worse.
Michael: Well, you mentioned pg_hint_plan
would be the 1 that would
make most sense on that.
Nikolay: Well, yeah, it's just
a random guess.
I'm not sure at all.
Worth checking.
Michael: Me too.
Well, let us know.
There's definitely people out there
that know more
Nikolay: about it.
Let's do a summary.
So, try to avoid high partition
number, try to avoid a lot of
indexes, especially try to avoid
this combination of these 2
problems and use prepared statements.
Right?
Michael: Yeah.
You don't think avoiding
Nikolay: lots of
Michael: joins as well?
Nikolay: simple ones?
Maybe, yeah.
Again, like, somehow I deal with
very simple plans lately.
I don't know.
Yeah, Good point as well.
Good point.
I try to avoid a lot of tables,
many joins, and then we try to
expect good performance because
I always hunt for single index
scan and better index only scan,
always.
And if you have even 1 join, even
2 tables, you cannot build
index on 2 tables.
It's always 1, like indexes belong
to their tables always.
And materialized views, as we maybe
talked, out of scope for
my recommendations, always.
Regular materialized views.
So that's why I tried to think,
how can we simplify things and,
okay, nest loop and so on, but
make queries sub-millisecond.
But yeah, if many, many tables,
planning time might be huge.
Anyway, I wanted 3 lessons, like
low index number, low partition
number, but not super low.
So partitions should be small,
relatively.
And prepared statements.
And I think these 3 might take
years to implement in large projects.
Michael: Yeah, true.
Nikolay: Yeah, unfortunately.
Michael: Cool. Nice one,
Nikolay, thanks so much.
Nikolay: Bye-bye.
Michael: Catch you next week.