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?
3 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.