Nikolay: Hello, hello, this is Postgres FM.
My name is Nik, Postgres.AI, and as usual, my co-host is Michael,
pgMustard.
Hi, Michael.
Michael: Hello, Nik. How's it going?
Nikolay: Good, good. So, you proposed, I proposed this topic, honestly, right?
I saw you worked on updating your glossary on the pgMustard website glossary for EXPLAIN and I thought maybe it's a good idea because I have some feeling I'm missing something in understanding
what's new in Postgres 18.
So I would like to learn about details and if you have fresh
look at this, why not, right?
Let's talk about that.
Michael: Yeah, it's a good idea.
It is what I've been working on the last couple of weeks which
is updating our EXPLAIN glossary.
So the documentation on EXPLAIN is good in the Postgres docs
but it's not very extensive and when I was first learning about
all this you know 7 8 years ago it was really hard to learn what
various of the phrases, like various node types exactly were,
what some of the fields were, and we basically started looking
it up for each 1 and then started making a note of it and that
started as a blog post and ended up as about 160 pages of this
glossary.
Nikolay: How many?
I think
Michael: it might be a bit more now actually it's definitely
over 150.
Nikolay: Okay.
Michael: So yeah it's quite extensive and As a result actually
now have to update what I don't have to update each year But
try to update each year when the major versions come out so that
it's up to date.
Nikolay: That's great Yeah, so it also highlights how complex
explained plans are right? Michael: Yeah, yeah exactly So yeah, we've got a page for each
parameter, a page for each operation type and a page for each
field type.
So it is, they're not all operation types thankfully, but yeah
it is a little bit complex.
Nikolay: I see, yeah.
So what's new in 18?
Michael: Right yeah well the first 1 I put on the list is 1 we've
spoken about many times which is BUFFERS being on by default
with ANALYZE.
Long-term listeners will be very familiar with this topic but
until this version we had to specify EXPLAIN (ANALYZE, BUFFERS)
if we wanted to get information about the data read written whether
it came from shared buffer cache or not so yeah that was the
change I was most happy about making.
I think I posted about that.
The only thought that I might not have mentioned before is that
this isn't true for auto_explain.
So in auto_explain, if you want buffers, I don't mind it.
I think it's not as bad.
So in auto_explain, if you want buffers, you have to specify.
Nikolay: Yeah, so for regular EXPLAIN ANALYZE, it means that
we can expect, since BUFFERS are by default, we can expect to
rely more on those numbers are present in analysis.
So it should shift methodologies a little bit towards direction
we discussed over more than 2 years right?
Michael: I think so I think hopefully people will be curious
why these numbers are showing up if they're used to just running
EXPLAIN ANALYZE or if they're reading a guide or a blog post
that only suggests running EXPLAIN ANALYZE if they're on version
18 or above which is not many people at the moment but obviously
will grow over time then hopefully people will be curious what
those numbers are and what they mean and not only will it help
them but if they if they can't solve their problem and they have
to ask for help on the mailing list or to a consultancy or to
a tool or Yeah, wherever the people that are more familiar with
those numbers or the tools that use those numbers to give tips
can get them automatically.
So get them on the first try without having to ask the person
to run it again and maybe having to guess at what the problem
is.
So yeah, I think it's a big win for people who are trying to
solve their own problems, but also people that are asking others
for help.
Nikolay: Right.
That's great.
Yeah.
Those who listen to our podcast for quite some time know how
we appreciate buffers.
So such big relief it's now by default.
We had several episodes about that.
Michael: Yeah, and only 4 or 5 years until we can just stop telling
people to run it with buffers at all.
Nikolay: Yeah, yeah, yeah.
So like TLDR version is like just use buffers because in most
cases query is slow because of I/O and buffers brings you I/O focused
analysis.
Yeah.
Without it, it's like you don't see the important piece.
Yeah, great. And I think you, do you plan any, like, no, I think, I question,
I have a question to you.
Do you plan to focus on buffers even more in pgMustard to do something,
some changes with Postgres 18 release or not?
Michael: Not with buffers, but with all the others, yes.
Buffers, because we have a string in pgMustard where you can copy
from, we automatically suggest people gather buffers, and most
do.
Maybe like, it's not quite 95% but it's close.
So we already focused quite a lot on buffers and I don't think
this is gonna massively change that.
The only change I'm looking forward to making is dropping it
from that string.
In about 4 or 5 years' time, once Postgres 17 is out of support,
I think we'll drop buffers from the string because we'll be fairly
confident that if they've got, well, if they've got ANALYZE
they'll get buffers.
So that's the 1 change I'm looking forward to making.
Nikolay: Yeah, great, great, great, yeah, yeah.
Okay, that's, yeah, pretty straightforward, let's move on, what
else?
Yeah.
This I knew very well.
Michael: Yeah, yeah.
Nikolay: Like, you know I knew, yeah.
Michael: There's 2 that I'm not sure which is the most important
or most exciting.
Let's go most important first.
I think actual rows being now rounded to, or being reported,
sorry, to 2 decimal places is really really big so until this
version so in particular 17 the number of actual rows is a is
an average number per loop and that's mostly fine except when
you have lots of loops and specifically like many many many loops
so for example a nested loop that has 10,000 loops because if
it's being rounded especially at low numbers that number can
be quite far off, especially between 0 and 1 so if that 10,000
loops is returning fewer than 5,000 rows in total then it would
be reported as 0 actual rows And that's a big difference from
5000.
So yeah, that for me is a huge change in terms of seeing the
details in some quite common performance issues.
So it's quite common that a performance issue would show up where
you're getting lots of loops either because looping was a bad
idea in the first place or because there isn't like the best
index available so that 1 I think will really help in terms of
giving a more sensible number once you multiply actual rows by
a number of loops.
Nikolay: Is it only in nest loops or somewhere else?
It's only in nest loops, right?
Michael: Definitely in parallel plans as well.
So if you've got like 2 workers, Then the number of actual rows
you need to multiply it by 3 to get You know leader plus the
2 workers because it gets average per work process as well So
I think there are other cases as well Maybe not
Nikolay: so this this basically bottom line is just this helps
us reduce the error, right?
Yeah.
Yeah, that's it, right?
Because it can be big if a lot of loops happening.
Michael: Exactly, exactly.
More accurate in a lot of cases and a huge improvement for specific
performance issues.
Nikolay: Does it help with some kind of analysis when, for example
pgMustard recommends users what to do no
Michael: a little bit like
Nikolay: numbers right
Michael: I mean if if for example you were so it helps in a few
ways right you can get a more accurate difference between the
estimate and the actual rows slightly but estimated rows are
always an integer so it's not a that's not a huge deal the times
where it's most useful is like the tips making sense like if
you're trying to tell people this many rows were filtered and
if you're telling them all of the rows were filtered that doesn't
quite make sense if they're getting some rows returned by the
query so it helps a little bit but you're still going to get
the tip regardless of this, It's just more a case of like
Nikolay: making sense.
Maybe it should be helpful at different level when you have a
bunch of queries, like say hundreds of queries, and you need
to prioritize and choose top 5 which you which you need to focus
first on.
Michael: Yeah that's a good point.
Yeah so let's take our case where we had 10,000 loops.
The difference between returning 5,000 rows and returning 5
rows is huge.
Like that.
Depending on the size of the table, it might be that a totally
different index scan is like, for 1, a bitmap heap scan might
be really efficient, and for the other, you might actually want
an index scan.
So it might actually matter for how much optimization potential
there is as well if you're returning 5,000 rows there's only
so fast you can make that if you're returning 5 there's a different
maybe an order of magnitude more like optimization potential
so yeah that's a good point
Nikolay: yeah so more like more precise numbers can help you
to have more precise prioritization when you're dealing with
a lot of plans.
You know, my point of view, more and more we should think about
more automated way of processing and because we have a lot of
instances and so on.
And plan flip analysis, we have sometimes using pgMustard for
that.
So it's great if you have more precise numbers.
And yeah, it can give you precision and prioritization as well.
What's what matters more?
What matters less?
Good, good, good.
Okay, what?
That's it in this topic or something else
Michael: I think so
Nikolay: let's move on then right
Michael: index searches I've got next do you remember the conversation
we had with Peter Geoghegan about skip scan of course
Nikolay: yeah 2 meanings of it also
Michael: right yeah yeah well yeah, you mean with loose index
scan?
Nikolay: This is not, this is the other meaning.
Michael: This is skip scan.
Nikolay: Basically let me, like, what I remember, In the topic
of redundant indexes, if, before we always said, if you have
index on column A and another index on column A and B, this is
definitely a redundant case, you can drop, safely drop an index
on column A.
But if it's column B and you have AB, you cannot drop an index
on column B because it's on the second place on that second index.
It won't be helpful.
Here in some limited number of cases, it can be helpful.
And it can skip the first column, basically, right?
And use index on column A, not on 2 column index on columns A
and B to work only with queries which only have filters on B,
not on A at all.
Michael: Yeah, exactly.
If there's relatively few values in A or if it knows the set
of values of A, like via a different condition, an index on A,
B can relatively efficiently be used for a query on B without
any information about A or with a set of values for A.
And there's a couple of optimizations in the last couple of major
versions of Postgres.
So this index searches tells you how many unique descents of
the index there were.
So how many times did it loop through the index to check each
value of A.
So 1 example could be like, you know a phone book where you have
it listed by last name and then first name.
If you live in a country with not that many last names, you can
imagine saying like, how many people are there called Nikolay
in, I don't know, San Diego.
And you could look at all of the, you could look per last name
and just jump straight to N each time, straight to N-I-K and
see how many... and then you don't keep searching through all of
the Smiths before you move on to the, I don't know.
Nikolay: I like how you avoided pronouncing my last name.
It's a good idea, okay.
Michael: Yeah, yeah, to get to the Samokhvalovs.
Nikolay: Good, yeah, so it's, So, but I don't understand what's
happening in EXPLAIN plans for in this area.
Michael: So we now see how many descents happened.
So in 17, in 17, we had some of these optimizations, but we couldn't
see where the Postgres was using them.
I mean we got clues like the execution time dropped and the buffers
were fewer, but in exactly indirectly and now we can see like
if it is using this then index searches will be greater than
1.
If it's not using the optimization index searches will equal
1.
Nikolay: Good, good.
And how does it help for like analysis?
Michael: Good question.
So we at the moment most of the time like by the nature of this
work these are optimizations put in right so most of the time
if you're getting these your queries already faster than it would
have been in previous versions of Postgres so it tends to come
up when the queries relatively fast generally now that's not
that's not always going to be true But I haven't seen that many
cases of this where this was the problem yet.
And I expect to eventually come across quite a few.
But for now, I haven't.
Nikolay: You mean when you see this mechanism being involved,
and you think, OK, this may be a sign that we don't squeeze the
best performance possible, because we could have different index,
which doesn't use this feature, but uses regular index scan mechanics.
Michael: So yeah, but you're right.
If you see this in place, I believe if index searches is above
1 You're almost guaranteed because this only works for pg_index
at the moment I think you're almost guaranteed that there is
a better index definition for that query.
You will be
Nikolay: able to have more.
Michael: Yes exactly so I think it is a way of saying that there
is there is potentially more to squeeze out of this but it's
efficient enough in a lot of cases and the idea of the feature
is only to be used when it's a better option, that I haven't
yet seen query plans.
Imagine a query plan is actually quite complex and you imagine
this part, this is going on in your query, But there might be
a different part that's actually the bottleneck.
So I haven't seen this, like, where this is the problem be the
bottleneck yet.
But it will be, and for very, very simple queries, it's very
easy to show it.
In fact, I blogged about it just last week, showing how you can
then optimize it further.
Nikolay: And Skips can also introduce Postgres 18.
So the feature comes with observability bit.
This is good demonstration how developers should think about
development.
They should think about like not only features, but also how
they will be observed.
I mean, in a regular DevOps approach, you think how you will
monitor your feature.
In this case, it's not monitoring, it's micro-level optimization,
micro-optimization, but still it's great for transparency.
I guess it helps all, including to develop this feature, you
need to see it, right?
So, in tests maybe as well, yeah?
In regression tests, Postgres has, maybe also this is used, I
guess.
Yeah, good, cool.
Michael: By the way, on the monitoring front, there is you can
actually see these
Nikolay: already yeah yeah I missed that
Michael: not added yeah not added in 18 this was always this
was available previously.
Nikolay: This was available previously?
What's the column?
Michael: Let me find it.
Oh actually, No, maybe not pg_stat_statements, sorry.
pg_stat_user_indexes and similar, you know, those views.
Yeah, you can see.
Nikolay: All indexes also, it's just at all indexes.
Michael: Yeah, exactly.
And there's an idx_scan column.
Nikolay: But this is old 1.
Michael: Yeah, but it counts these individual descents.
Nikolay: So if for 1 call we have multiple index cans yeah that's
interesting that's super interesting because you know we develop
quite advanced monitoring lately, and we touched this piece very
recently.
And I now think, what do you think about how this could be used?
Usually we just display 2 things, in our approach at least.
Top ends, so top end indexes by some metric, by index scans for
example.
These are top 10 indexes by index scan.
And also details about for individual index.
Okay, we have this index and we have a lot of graphs including
in the scans and all other stuff.
So I don't understand how we could use what you're saying.
We need basically to understand how many queries involve this
index, and somehow highlight that it's not 1 to 1, it's 1 to
n, right?
But how?
Michael: Yeah, but it depends what the problem is you're trying
to solve, doesn't it?
Nikolay: At macro level I don't see how.
I guess it's possible but we need to somehow to understand all
the query IDs and their calls in pg_stat_statements that this index
is involved there.
How to do that, it's an interesting question.
Because pg_stat_statements basically, like it forgets about plans.
Maybe something Lukas Fittl, pg_stat_statements is working on lately.
I know they just recently presented yet another attempt to have
plans properly tracked, persistent plans.
Maybe there, if we look at this and here, this is interesting
direction.
Quite advanced, I would say.
But at micro level it's pretty obvious, like we have 1 call.
Explain it's always 1 call.
So if we see multiple index scans.
Okay thank you for some advanced stuff here, it's food for additional
thoughts.
Michael: Yeah while we're thinking about the macro level, 1 idea
I had was, if you have, let's say, like a really right heavy
workload, and you you really want to minimize the number of indexes
you have, you might want to expand your search of these overlapping
indexes.
So you mentioned for example what you define as an overlapping
or redundant index.
You might want to expand that search to consider indexes that
have the same columns but in a different order.
So if you've got an index on ABC and on BAC to serve different
reads, especially if any of those columns like A or B are low
cardinality, so like don't contain that many unique values, There's
a chance that you won't have to pay too high a cost on your reads,
and you might be able to reduce the number of indexes you have,
which I thought was interesting.
Nikolay: So you want what I think, if idx_scan column in pg_stat_all_indexes,
pg_stat_user_indexes, If this is registering multiple,
like it increments by not 1 but by some number when we have a
call which uses skip scans.
In this case it tells us that probably there should be another
column which would show distinct queries were there.
So in this case, we would have 2 counters.
1 would be just how many queries, how many statements use this
index, and another is how many index scans were there.
If it's, if these numbers are different, more than 1, ratio is
more than 1, that means there is a room for, for like some.
Interesting that our direction of thought is to avoid skip scans,
so avoid using this feature somehow.
Michael: Well, I was just proposing a time when you might want
to go the other direction, actually embrace skip scans, and you
would lose some performance on some reads, but you'd gain some
performance on some writes.
So I think there are some folks who would happily pay that trade
off in certain workloads, like, you know, Internet of Things,
ingesting like loads of data, just wanting to have minimal indexes
on a table.
You probably already got minimal, or you've got very few, but
you might be able to reduce it by 1 or 2 more with this.
But yeah, with most workloads being read-heavy, I think it makes
sense.
Normally we can afford 1 extra index if it makes a really important
query or really important endpoint much faster.
Nikolay: Yeah definitely there's like some feeling that things
like observability can be improved here.
We are far from
Michael: it.
Currently, if it helps, I've thought about it a little from the
micro level.
Like you're thinking from the macro level.
From the micro level, I'm currently thinking there are wins here,
but they're unlikely to be like super common.
They're unlikely to be that huge.
And I'm still seeing loads of queries out there that can't use
any indexes or you know have really abysmal performance so like
this yeah so it depends like it depends how often this is coming
up and how much optimization potential there is when it does.
Nikolay: Yeah, well, depending on the project stage, if the stage
is early, usually people focus on adding indexes.
We deal more with slightly grown startups, and they come to us
with problems, and we observe a lot of unused redundant, and
index bloat, and so on.
That's why we focus right now in opposite direction.
Michael: Yeah, but redundant, like I've seen, you probably see
this all the time, but the exact same index, like 3 times.
So there's like, there's often even lower hanging fruit.
Nikolay: But you see it at micro level and explain how to identify
this problem in aggregated state like at macro level yeah this
I don't understand yeah
Michael: probably probably it's going to show up in number of
buffers.
Nikolay: Which, sorry, what I do understand, idx_scan is now
what, like, meaning of it shifted for me.
So it's not how many queries, like...
Michael: Well, it kind of
Nikolay: used to
Michael: be, right?
It used to, because index searches, I'm not sure of the exact
semantics.
I think there have been some optimizations in the past that might
have used this, but in the past it was pretty much one-to-one.
Well,
Nikolay: it might happen multiple times even, it might, it could
be, it was possible even before if the same index is used in
different parts of queries independently, multiple index scans.
So it can be, for example, CTE, right?
So different stages can use the same index multiple times.
Union.
So it's definitely not 101, but now it's even more not 101.
So it's interesting.
Anyway, I will be thinking about this, thank you.
As I said, this is the food for additional thoughts.
Yeah, yeah.
There should be some process here, yeah.
Michael: Shall we go through a few more?
Yeah.
Window functions.
So in the past, if you specified verbose, you get output at each
stage of a query plan.
And window functions would just say like, over question mark.
So you, if you've got multiple of them in your query it could
start to get a little bit tricky to see which 1 was which.
Obviously that's quite a niche case but if you're sharing it
with somebody else they might want to see what the...
I think verbose is especially helpful when you're sharing plans
with other people or tools.
Now that has been improved.
So we still get, we get over like W1 now and then there's a separate
field that reports what W1 is, which window function that is.
Nikolay: So if in the past, I remember we said,
EXPLAIN (ANALYZE, BUFFERS).
But on your website, I saw actually, EXPLAIN (ANALYZE, BUFFERS)
were both settings on, right?
Settings or just settings?
You can skip that.
Michael: Just settings, yeah.
Both work.
Nikolay: And what else?
WAL?
Michael: Yeah, WAL and WAL's good but look I don't find it
useful that often like most of the queries.
Nikolay: It's useful.
It's useful.
Michael: Serialize, I think, is...
Nikolay: Ah, this is Postgres 17 feature, right?
Or 16?
17.
Michael: Yeah, 17.
Nikolay: So now mantra is EXPLAIN ANALYZE
Buffers not needed, buffers not needed.
Settings, verbose.
Serialize?
WAL?
Michael: Depends, right?
Like, I think if you're typing them out by hand and you're in
a rush and there's a production incident, sure, just go ahead
and grab whichever is quickest to type.
But if you're not, then why not just paste them all?
Like...
Nikolay: Which all?
Like, there's...
Like everything.
This should be a pre-explain everything.
We discussed that and I think you proposed something in hackers.
Michael: Yeah, I ended up settling on proposing that
EXPLAIN (ANALYZE, VERBOSE) should give you everything.
Like everything should be on by default with verbose because
it kind of means like I want like verbose output.
It didn't go very far.
But yeah.
Nikolay: So what, like, there should be some 1 recommendation
you usually tell people when they come with problems.
Michael: Yeah, the 1 that you copy and paste from our app is
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) settings.
I think we haven't included the 17 ones, because not enough people
are on 17 yet.
Nikolay: In format JSON.
Michael: I think we dropped that.
Nikolay: That's great, Because I hate dealing with JSON.
People send us JSON and okay, I can go to some tools, but I cannot
read it myself.
We have actually a converter.
We have a converter from JSON to text, which is good because
I, myself as a human, consume text, not JSON.
But for tools, I understand JSON is better.
OK, good.
So now it's explaining why it's verbose settings.
OK?
4 words only, right?
Michael: Yeah, not too bad.
And you might want to use the others, like WAL, if you're dealing
with data modification queries or you suspect it's an issue like
if you're running out of ideas why not try them all.
Nikolay: And verbose also brings compute query ID thing, query
ID.
Yeah, query ID.
Michael: And schema qualifications.
Nikolay: You don't recommend serialize yet?
Michael: Only because not many people are on 17 yet.
Nikolay: Okay, so you think you will switch some future.
Michael: Yeah, in a few years time.
Nikolay: I would definitely include WAL.
There's also memory.
Michael: Again it's not all supported versions have WAL.
Nikolay: I see, yes.
Michael: I think.
Or maybe they do now.
Maybe it's maybe it's that I can't remember which version that
was added in.
Nikolay: Well last week we just said goodbye to version 13, right?
So now it's version 14, which is the latest, and WAL is included
there.
Michael: Nice, okay.
Nikolay: I think a WAL is worth having.
Michael: Great, and it's nice and short.
Nikolay: Good, okay, so back to the thing you told us about VERBOSE.
While we were chatting about all these options, I already forgot.
So VERBOSE brings what exactly?
Michael: The actual 18 changes don't require VERBOSE which is
a little bit confusing because it adds another field for window
definitions or the yeah the function the over
Nikolay: okay
Michael: oh sorry what's it called yeah okay I have to go back
to my glossary
Nikolay: there's memory which is new but I think it's from 17
maybe
Michael: yeah that's 17 oh but but this is related We do now
get memory information on like a...
So that was...
So memory in...
As an EXPLAIN parameter gives you information about the memory
used during planning time specifically.
Nikolay: Mm-hmm.
And it's from 17.
Memory is from 17.
And that's
Michael: from 17.
But we do now get...
There was another piece of work that adds memory or disk usage
details to a bunch more operation types.
So we already got them, for example, you might be used to seeing
them in sorts and a few other operations.
But now You get them in Materialized nodes, in window aggregates
as well, so for window functions, and CTEs, maybe others as well.
Nikolay: So memory was extended as well?
Michael: Not the parameter memory, but memory information in
EXPLAIN, yes.
Nikolay: Well I mean the memory in 17 brings you less in 4 than
in 18 right this is this is the right thing to say right
Michael: they're unrelated yeah
Nikolay: unrelated so it's only about their boss
Michael: This actually I think is on with ANALYZE, not even...
Nikolay: Why is it so difficult?
Michael: It is difficult, this is why I have to, like, I don't,
I struggle to remember all of this, so this is why I've written,
like, why I write it down.
Nikolay: There is UX issue here, definitely.
Michael: Yeah, the field is called window.
So you'll get a window ag operation with a field called window
within the definitions of each of your windows.
So anything you've got in the over clause.
Nikolay: Why is it called window?
Michael: So window functions are over something.
Nikolay: Ah, so you need to see details about window, ok.
Michael: Exactly.
Nikolay: And this is now, if you use verbose you see it, without
verbose you don't see it.
Michael: So, with verbose you see the output.
Nikolay: Ok.
Michael: I thought you would only see window with the Bose as
well.
So you see the output and the window.
But in testing I found that you could see the window without
the output by just running EXPLAIN ANALYZE
Nikolay: Okay,
so it's unclear.
Michael: Yeah, it's In fact, I've written it perhaps confusingly the window
field is always shown but output is only shown when the proposed
parameter to choose
Nikolay: I don't understand why we don't have give me everything.
Michael: Yeah, it's becoming clear.
Nikolay: There should be some give me everything.
I don't want to, I want short, EXPLAIN ANALYZE everything.
It should be done.
Because if it's development server, I mean, not production server,
I would like to see everything.
And in my optimization process to compare all the details.
Even those which I don't understand yet, because maybe if you
ask LLM or some tooling, it will understand it, right?
Michael: Yeah.
Or pgMustard.
Nikolay: pgMustard, exactly.
So why not bring everything if it's possible, if it's not production.
I understand that production, like maybe observer effect might
be an issue and you probably don't want everything sometimes.
Michael: Well yeah, I don't know, like yeah, I can see an argument
either way.
Nikolay: Okay.
Should we crack on?
Yeah, it was hard.
Michael: Do you remember we discussed that the enable like enable
seqscan and enable index scan like these...
Nikolay: There are changes there.
Instead of
Michael: a big
Nikolay: penalty now it's actual disabling right?
Michael: Yes well...
Nikolay: And now you see it on plans if it's disabled.
Michael: Yes.
That's, yeah.
So super quick 1.
But you'll only see in text format, you'll only see disabled
true for nodes that have been explicitly, I say disabled, but
let's say enable seqscan equals off, you'll see on any sequential
scans in that plan, you'll see disabled true.
Okay.
So simple 1.
Nikolay: Yeah, but I remember some thought I have, I had when
I was reading about this.
Some concern compared to old behavior.
I don't remember which thought it was.
You don't see any downsides of this change?
Michael: I really like this.
I think this mitigates the downsides of the previous functionality.
Nikolay: Especially if you use settings, so you see settings,
and you understand the reason why it's disabled, it's great.
Michael: Yes, exactly.
So if anybody doesn't know, settings parameter adds to the bottom
of your query plan any non-default planner related settings and
what they're set to.
Nikolay: Yeah and if you want to for example if you really somehow
like the old behavior you can just play with costs right maybe
maybe no.
There is a problem there is different problem, like we don't
see the second plan.
What the planner had while the planner was choosing, right?
So we only see the winner.
And I think old behavior of enable seqscan set to off, getting,
like, putting huge penalty to seq scans, it helped sometimes
understand that, like, planner behavior, like, why it switches
from this to that From the sky
Michael: still yeah, you can still use it for that
Nikolay: Okay Maybe yeah, and I I want different I want just
to see second and third plan maybe to understand like the difference
in costs between the winner and the losers Yeah, but I think
you like UX and like it's not easy to
Michael: Wait, this makes it easier.
This makes it easier to see the difference in the costs.
Nikolay: Okay.
Because you
Michael: don't have those huge numbers.
Nikolay: But it's not what I want.
I want to maybe sometimes always see the second and third plans.
Michael: Fine, fine, fine.
Nikolay: If it's possible, like somehow, like add some 0.1 more
flag in EXPLAIN ANALYZE, show me not 1 plan, but multiple plans.
So, yeah.
Okay, too much, maybe.
Michael: Let's go through the other couple of changes that we
have got already.
Yeah 1 I think is a kind of a bug fix which is for parallel bitmap
heap scans You would get exact heap blocks and lossy heap blocks
to tell you whether the work mem was big enough for that bitmap
basically.
And it turns out they were only reporting the leader process,
not the worker processes.
So now that's been changed so that you now see per worker details
by default, without, with ANALYZE, not just with VERBOSE.
So Parallel Query workers are normally only shown, like details for
them is normally only shown with verbose this is an exception
and if from memory the leader reported numbers don't include
the worker numbers still so that's a difference in back So that's
kind of maintaining behavior from previous versions but it's
different to how most operations report their details.
So most of the time operations include the details, include the
information of their children or from their work processes.
So, yeah, this is a slightly confusing 1, but a big improvement
for people that know what they're looking for.
Nikolay: TLDR is parallel awareness of EXPLAIN plans improved.
Right?
Michael: Oh, and accuracy.
Nikolay: Yeah, it sounds like a bug to me a little bit.
Michael: Yeah it is, it is.
But it's good that we've got that and you might notice a change.
And then the probably the least likely to come up but for completeness
did you see we've got a new WAL buffers full field as well?
So if the WAL buffers happen to have become full during the
query execution, or the number of times they become full now
gets reported.
Nikolay: Ah, I remember reading about this, Yeah, that's great.
Nice.
Michael: So yeah, simple 1.
Nikolay: Yeah, simple, but interesting how we are going to use
it because usually this kind of analysis is really like we do
it at macro level and see usually dealing with wait event analysis
and see, are your WAL buffers and lightweight lock related to
WAL buffers, WAL write, right?
WAL write and so on.
This is how we indirectly understand that probably there's a
problem here and contention and so on.
And then we'll usually go to in tune siblings and like a group
commit basically.
So let it commit, let it wait a little bit, slightly, and do
fsync or analog for multiple transactions for multiple commits
in right heavy, right intensive workloads it's a very good approach.
But when I think about micro level like here, explain well, it's
hard because I like usually in EXPLAIN ANALYZE I do it dependently,
maybe some multiple times, but I don't think about macro state
of our server.
Michael: Yeah, I actually I don't imagine this is going to come
up much when you're running it manually.
You'd have to go on last point have to be, I think, or imagine
an auto_explain you've got a query that's intermittently very
slow.
So normally it's relatively okay.
And then sometimes it's extremely slow.
This would help you find out on this really slow runs oh it might
be related to this issue
Nikolay: this is great this is great and but it's it's immediately
provokes a couple of thoughts 1 is p99 for for everything could
not explain
Michael: yeah so on it's we talked about right
Nikolay: yeah and another thing is what about checkpoints and
BUFFERS written and seeing sometimes by backends and like initiated
by backends because something like because guys called BG writer
and checkpointer, they don't do their job properly.
So our precious backends need to do it sometimes.
So I'm thinking maybe explain should start showing that info as
well, and also in the context of auto_explain maybe more.
Yeah, that's, again, like I feel some seeds for future plans
to be grown, you know, in observability.
It's interesting.
And yeah, thank you so much for sharing all the details here.
Michael: Oh, you're welcome.
Nikolay: I'm glad it was helpful.
For me, it's very helpful.
We are very deep in observability recently.
So that's great.
That's great.
And I agree with you.
It's more, maybe it's more for to explain not for occasional
explained plans, but good.
Yeah, maybe.
Anyway, my recommendation is still like, let's keep WAL included
because we, we, we deal with a lot of, a lot of startups which
suffer from writing too much WAL.
And again, this brings us to the topic of let's clean up indexes
because indexes write more WAL.
If you have unused, redundant, and so on, and then Bloat as well,
you are writing more WAL than you could.
Eventually, I think it would be so great to say like you know
like let's clean up this and that and we predict how much WAL
writes will be reduced but at micro level having WAL info is
very helpful as well.
Although we always should remember that it depends on the current
situation, how far from checkpoint we are.
And also, it depends on parameters, because you take different
parameters, you will do very different thing, including WAL
writes will be very different, so maybe.
Michael: Well, I think it's difficult for Microsoft because you
need concurrent workload as well for it to be super inter-
Nikolay: Not concurrent, but the state needs to be fixed.
Michael: Same each time.
Nikolay: Same, yeah, But this is solved by Database Lab.
We can reset and be in the same state, right?
So that's great.
So Database branching helps.
So you start always, you have various ideas or iterations, and
you always start from very same state including everything, including
tuples, including the distance from checkpoint, everything.
So this is not a problem.
The problem is, I see, for example, if you forgot that parameters
matter a lot.
In one case, you can get a full-fledged update.
In another case, you can have a hot update.
And you will see very different WAL numbers there, right?
Michael: Yeah, true.
Nikolay: But WAL buffers full, it's interesting.
Like, why don't report number of dirty buffers in each table
involved in this case?
Like, you know, like having a pg_buffercache here.
Well, I'm joking.
Michael: I think you do get dirty buffers.
If you include buffers, you get dirty pages.
Nikolay: What concerns me in this, what you said, it's not only
what we did, but also what others did before us recently, right?
In this case, let's report dirty buffers for each page, each
table fully?
How many, like, buffers loaded to the buffer pool, how many of
them dirty for this table, for this index, for each relation?
Oh, this complicates, like, it's too much.
But anyway, like, I'm curious what was the reason to introduce
this?
I think there was a specific reason, right?
Michael: I guess so.
Sometimes these things get added for completeness.
So like it was getting added to some system view and it's like,
oh, why don't we also report this in expense so I'm not sure
which way around this one was.
But yeah, normally these things get added because they're needed
for some production incident and they weren't available.
Nikolay: Well in defense of this there are things that also depend
heavily on previous actions for example the status of caches
right.
If you use buffer numbers, you see reads, but next time you see
hits.
Yeah, true.
This is maybe like in the same route of thinking.
Yeah, OK.
Anyway, great.
I think it was very advanced.
I doubt everyone should know everything, remember everything,
but in general I feel like the depth of everything is increasing.
It's more and more nuances.
This is great.
Appreciate it.
Michael: Yeah, absolutely.
Nikolay: Yeah, appreciate all this.
Thank you so much.
Michael: Good to speak with you and catch you soon.