Postgres FM - What's new in EXPLAIN

Episode Date: November 21, 2025

Nik and Michael discuss the various changes to EXPLAIN that arrived in Postgres 18. Here are some links to things they mentioned: EXPLAIN (official docs) https://www.postgresql.org/docs/current/sql-explain.htmlUsing EXPLAIN (official docs) https://www.postgresql.org/docs/current/using-explain.html EXPLAIN glossary (pgMustard site) https://www.pgmustard.com/docs/explainPostgres 18 release notes https://www.postgresql.org/docs/release/18.0/Enable BUFFERS with EXPLAIN ANALYZE by default (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebOur (first) BUFFERS by default episode https://postgres.fm/episodes/buffers-by-default Show index search count in EXPLAIN ANALYZE (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0fbceae841cb5a31b13d3f284ac8fdd19822ecebOur episode on Skip scan with Peter Geoghegan https://postgres.fm/episodes/skip-scanWhat do the new Index Searches lines in EXPLAIN mean? https://www.pgmustard.com/blog/what-do-index-searches-in-explain-meanpg_stat_plans presentation by Lukas Fittl https://www.youtube.com/watch?v=26coQV3f-wkImprove EXPLAIN's display of window functions (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8b1b34254Show Parallel Bitmap Heap Scan worker stats in EXPLAIN ANALYZE (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5a1e6df3bAdd information about WAL buffers being full (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=320545bfc ~~~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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork

Hello, hello. This is POSGUS FM. My name is Nick, PostGUS AI. And as usual, my co-host is Michael, Pige Master, Hi, Michael. Hello, Nick. How's it going? Good, good. So you proposed, I proposed this topic, honestly, right?

Let's, I saw you, you worked on, on updating your glossary on the PG Master's website, glossary for Explain. And I thought maybe it's a good idea because I have, 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? Let's talk about that. Yeah, it's a good idea. It is what I've been working on the last couple of weeks, which is, yeah, 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,

seven, eight 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 one, and then started making a note of it. And that started as a blog post and ended up as about 160 pages of, like, this glossary. How many? I think it might be a bit more now, actually. It's definitely over 150. Okay. So, yeah, it's, it's quite, extensive and as a result actually now have to update well 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 that's great yeah so it also highlights how complex explained plans are right 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 bit complex. I see. Yeah. So what's new in 18? Right, yeah. Well, the first, the first one I put on the list is one 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 Shed Buffer Cash, or not so yeah that was my the change I was most happy about making thing 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 it's not as bad so in also playing if you want buffers you have to specify yeah so so for a regular explain analyze it means that we can expect since buffers first are by default we can expect they can rely more on that those numbers are present in analysis. So it should shift methodologies a little bit towards direction we discussed over more than two years, right? 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 can't solve their problem and they have to ask for help on the mailing lists 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 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 it's we had several episodes about that right yeah and only only four or five years until we can just stop telling people to run it with buffers at all

Yeah, yeah, yeah. So like TLDR version is like just use buffers because in most cases, query is slow because of IO. And buffers brings you IO focused analysis. Yeah. Without it, it's like you don't see the important piece. Yeah, great. So and I think you do you plan any like no, I think I question you, I have a question to you. Do you plan to focus on buffers even more?

in PG Master to do something some changes with PostGus 18 release or not? Not with buffers but with all the others yes

buffers because we have a string in PG Master 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 going to massively change that. The only change I'm looking forward to making is dropping it from that string. In about four or five 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 analyzed, they'll get buffers.

So that's the one change I'm looking forward to making. Yeah, great, great, yeah, yeah. Okay, that's, yeah, pretty straightforward. let's let's move on what else yeah this I knew very well yeah yeah like you know I knew yeah there's two that I'm not sure which is the most important or most exciting let's go with most important first I think actual rows being now rounded to or being reported sorry to two decimal places is really really big so until this version so in Postal 16 the number of actual rows

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 zero and one so if that 10,000 loops is returning fewer than 5,000 rows in total

then it would be reported as zero actual rows. And that's a big difference from 5,000. So, yeah, that for me is a huge change in terms of seeing the details into 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 the best index available. So that one, I think, will really help in terms of giving a more sensible number once you multiply actual rows by a number of loops. Is it only in the nest loops or somewhere else? It's only in nest loops, right? Definitely in parallel plans as well. So if you've got like two workers, then the number of actual rows you need to multiply it by three to get, you know, leader plus the two workers. because it gets average per worker process as well.

So I think there are other cases as well. Maybe not. So 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. Exactly, exactly.

More accurate in a lot of cases and a huge improvement for specific performance issues. Does it help with some kind of analysis when, like, for example, PG-Mastert recommends users what to do, no? A little bit, like... Numbers, right? I mean, 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 it doesn't, like 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's, 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 it makes 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 five which you need to need to focus first on. 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 the returning five rows is huge.

Like that, depending on the size of the table, it might be that a totally different index scan is, like, for one, a bitmap heat scan might be really efficient, and for the other, you might actually want a dead, like 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 five there's a different maybe an order of magnitude more like optimization potential so yeah that's a good point yeah so more like more precise numbers can help you to have more more precise prioritization when you're dealing with a lot of plans and can like you know my my point of view more and more we should think about more automated way of processing and because yeah we have a lot of instances

and so on and yeah and plan flip analysis we you know we have some sometimes using PJ master for that so it's it's great if you have more precise numbers and yeah it can 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 i think so let's move on then right index searches i've got next Do you remember the conversation we had with Peter Gagin about SkipScan? Of course. Yeah. Two meanings of it also, right?

Yeah, yeah. Well, yeah, you mean with loose index scans? Yes. This is not, this is the other meaning. This is skip scan. 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.

is definitely redundant case, you can safely drop index on column A. But if it's column B and you have A, B, you cannot drop 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 two column index on columns A and B to work only with queries which only have filters on B, not on A, at all. 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 dissents of the index there were. So how many times did it loop through the index to, like, check each value of A? So one example could be like in a, you know, like 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 Nikolai 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 IK 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 I like how you avoided pronouncing my my last name. It's a good idea. Okay. Yeah, yeah, to get to the Samachvalovs.

Good, yeah. So it's so, but I don't understand what's happening and explain plans for in this area. So we now see how many dissents 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 one if it's not using the optimization index searches will equal one good good and how does it help for like analysis 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 query is relatively fast, generally. Now 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. You mean when you see this mechanism being involved and you think, okay, this may be a sign that we don't squeeze the best performance possible because we could have different index which doesn't have doesn't use this feature but uses regular like regular index scan mechanics so yeah so you're but you're right if you see this in place i believe if index searches is above one you're almost guaranteed because this only works for btri index at the moment i think you're almost guaranteed that there is a better index definition for that

query you will be able to have no yeah yes exactly so i think I think it is a way of saying, look, there is potentially more to squeeze out of this. Mm-hmm. But it's efficient enough in a lot of cases and only, 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. You 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. I haven't seen this, like, where this is the problem, be the, be the bottleneck yet.

But it will be. And for very, very simple queries, it's very easy to show it. In fact, I've blogged about it just last week, showing how you can then optimize it further. And Skips can also introduce in Postgrease 18. So the feature comes with observability bit. This is a good demonstration how developers should think about development. They should think about, like, not only features, but also how they will be observed. right i mean as a regular devops approach you think how you will monitor your feature and in this

case it's not monitoring it's a micro level optimization like micro optimization but still it's great that for transparency i guess it helps all in including like to develop this feature you need to see it right so yeah in tests maybe as well yeah regression in the integration tests post this has yeah we also this is used I guess yeah good cool so that by the way on the monitoring front there is you can actually see these so already yeah it's just a sentence yeah I missed that not added yeah not added in 18 this was always this was available previously this was available previously yeah what's the call let me find it

Oh, actually, no, maybe not PG-Stat statements. Sorry, PG-State user indexes and similar, you know, those views. Yeah, you can see... All indexes also. It's just at all indexes. Yeah, exactly. And there's an IDX underscore scan column. But this is old one?

Yeah, but it counts these individual dissents. Ah, so if for one call we have multiple index scans. 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 how, like, what do you think about how this could be used? Like if usually we just display two things in our approach at least.

Top ends. So top end indexes by some metric, by index scans, for example. Okay. 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 index 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 one to one, it's one to n, right? But how? Yeah, but it depends what the problem. image you're trying to solve doesn't it like what at macro level i don't see how i i guess it's possible but we need to we need somehow to understand all the query ideas and their calls and pitches statements that there is this index is involved there how to do that it's it's a

interesting question and we need the like because pittister statements basically like it forgets about plans maybe something uh lucas uh uh fetal pigeonalize is working on lately they they i know they just recently presented yet another attempt to have plans properly tracked pedist of plants maybe there if we look at this and here it's this is interesting direction quite advanced i would say but at micro level it's pretty obvious like we have one call explain it's always one call so if we see multiple index scans Okay, thank you for some advanced stuff here. It's full for additional thoughts.

Yeah, while we're thinking about the macro level, one idea I had was if you have, let's say, like a really right heavy workload and you really want to minimize the number of indexes you have, you might want to expand your search of these overlapping index. 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 indexes 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 don't contain that many unique values, there's a chance that you won't have to pay too higher costs on your reads and you might be able to reduce the number of indexes you have, which I thought was interesting. So you want what I think, if IDX scan column in PG start all indexes, PG start user indexes, if this is registering multiple, like it increments by not one, but by some number when we have a call which users skip scans.

In this case, it tells us that probably there should be another column which would show distinct calls number, I mean distinct, like how many queries were there. So in this case, we would have two counters. One would be just how many queries, how many statements use this index, and another is how many index scans were there. If these numbers are different, more than one, ratio is more than one. That means there is a room for like some. Interesting that our direction of thought is to avoid skip scans. So avoid using this feature somehow.

Well, I was just proposing a time where you might want to go the other direction and actually embrace skip scans and you would lose some performance on some reads, but you'd gain some performance on some rights. So I think there are some folks who would happily pay that trade off in certain workloads, like, you know, Internet of things, ingesting loads of data, just wanting to have minimal indexes on a table. You probably already got minimal or like you've got very few, but you might be able to reduce it by one or two more with this. but yeah with with most workloads being read heavy i think it the it makes sense normally we can afford one extra index if it makes a really important query or really important endpoint much faster yeah definitely there's like some feeling that things like observability can be improved here like we are far 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. Yeah, well, depending on the project stage, In 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. Yeah, but redundant, like I've seen, you probably see this all the time,

but the exact same index, like three times. So there's like, there's often even lower hanging fruit, yeah. But you see it at my. micro level in explain how to identify this problem an aggregated state at macro level yeah this I don't understand yeah probably probably it's going to show up in number of buffers what's 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 well it kind of used to be right it used to because index searches, I'm not sure 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, it might happen multiple times even. It was possible even before if the same index is used in different parts of queries, and depending on multiple index scans. So it can be, for example, CTE, right? So different stages can use the same index multiple times, union. So it, Definitely not 101, but now it's even more not one-on-one. Yeah. So it's interesting, 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. Okay. Should we go through a few more? Yeah. Window functions.

So in the past, if you specified for bows, 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 one 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 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 so if in the past i remember we said explain analyzed buffers but on your website i saw actually explain analyzed buffers were both uh settings on right settings or just settings not you can just settings yeah both work and what else what wall Or it's included to the rubles. We, yeah, wool and, well, I don't find it useful that often, like most of the queries. It's useful. It's useful.

Serialize, I think, is, is... Ah, this is Postgres 17 feature, right, or 16. 17. Yeah, 17. So, now mantra is, uh, explain and analyze. Buffers not needed. Buffers is not needed. Settings, rebows.

Surveals. Ceralize. wall? It depends, right? Like, I think if you're typing them out by hand and you're in a rush and it's production incident, sure, just go ahead and grab whichever as quick as to type. But if you're not, then why not just paste them all? Which all?

Like, there's, like, everything. This should be a pretty explain everything. We discussed that. And I think you propose something in hackers. Yeah, I ended up settling on proposing that Explain, Analyzed for Bose should give you everything. Like, everything should be on by default with verbose because it kind of means, like, I want, like,

the Bose output. It didn't go very far. But, yeah. So what, like, there should be some one recommendation you usually tell people when they come with problems. I, I, I, I, I, I, I, I, I, yeah, the one that we can, the copy 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 and for more jason i think we dropped that that's great because i hate dealing yeah people send us jason and okay i can go to some tools but i cannot read myself we have actually converter we have converter from jason to text which which is good because i myself as human consume text not jason so but for tools i understand jason is better okay good so now it's explaining lies they're both settings. Yeah.

Okay. Four words only, right? Yeah, not too bad. And you might want to use the others like wall if you're dealing with like data modification queries or you suspect it's an issue. Like if you're running out of ideas, why not try them all? Yeah. And Kerbos also brings compute query ID thing.

Query ID. Yeah. Career ID. Yeah. And schema qualifications. Yeah. You don't recommend serialize yet. Only because not many people are on 17 yet.

Okay, so you think it will, you will switch some future. Yeah, in a few years' time, probably add that. I would definitely include Wall. There's also memory of... Again, it's not all supported versions have Wall. I see, yes. I think. Or maybe they do now.

Maybe it's 30... I can't remember which version that was added in. Well, last week we just said goodbye to version 13, right? So now it's version 14, which is the latest, and the wall is included there. Nice. Okay. I think a wall is worth having. Great, and it's nice and short. Good. Okay, so back to the thing you told us about VIRBOS.

While we were chatting about with all this options, I already forgot. So VIRBOS brings what exactly? The actual 18 changes don't require Vibos, which is a little bit confusing, but because it adds another field for window definitions or the, yeah, the window function. They're over. Okay. Oh, sorry, what's it called?

Yeah. I'll have to go back to my glossary. There's memory, which is new, but I think it's from 17, maybe. Yeah, that's 17. Oh, but this is related. We do now get memory information on like a, so that was, so memory in, as an expect,

parameter gives you information about the memory used during planning time specifically. Mm-hmm. And it's from 17. Memory is from 17. And that's 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 material nodes, in window aggregates as well, so for window functions, and CTE.

and CTEs, maybe others as well. So memory was extended as well. Not the parameter memory, but memory information in Explain, yes. Well, I mean, the memory in 17 brings you less than 18, right? This is a right thing to say, right? They're unrelated, yeah. Unrelated. So it's only about their both.

This actually, I think, is on with Analyze, not even about it. Why is it so difficult? 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. There is your exclusion here, definitely. Yeah. The field is called window.

So you'll get a window ag operation with a field called window, with then the definitions of each of your windows. So anything you've got in that over. Why is it called window? So window functions are over something So you're going to see about details about window Okay Exactly

Yeah, okay, okay, okay And this is now If you use verbose, you see it Without verbose, you don't see it So With verbose, you see the output Okay I thought you would only see

Window with verbose as well Okay So you see the output and the window But in testing I found that you could see the window without the output by just wanting explain analyze okay so it's unclear yeah it's uh in fact I've written perhaps confusingly the window field is always shown but output is only shown when the proposed parameter to use I don't understand why we don't have give me everything yeah yeah it's

becoming clear yeah there should be some give me everything I don't want like to I i won't short explain analyze everything you know yeah it should be done i i just like because i because if it's development server i mean not not production server i would like to see everything and and and and in my optimization process to compare all the details even those which i don't understand yet because maybe if if you ask lm or some tooling it will understand it right yeah or pidge master exactly so so why not bring everything if it's possible if it's not production i understand the production like maybe observer effect might be an issue and you probably don't want everything sometimes

well yeah i don't know like yeah i can see an argument either way okay should crack on yeah it was hard do you remember we discussed that the enable like enable sex scan and enable ink scan like these There are changes there. Instead of big penalty, now it's actual disabling, right? Yes. Well, well. And now you see it on plans if it's disabled. Yes, that's, yeah.

Yeah, so super quick one. But you'll only see, in text form it, you'll only see disabled true for nodes that have been explicitly. I say disabled, but let's say enable sex scan equals off, you'll see on any sequential scans in that plan, you'll see disabled true. Okay. Simple one. Yeah, but I remember some thought I had when I was reading about this some concern compared to old behavior. I don't remember which I thought.

So you don't see any downsides of this change. I really like this. I think this mitigates the downsides of the previous functionality. Especially if you use settings, so you see settings. And you understand the reason why it's disabled. it's great. 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. 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. There is a problem. There is a different problem.

Like we don't see the second plan. plan. What the planner had while the planner was choosing, right? So we only see the winner. And I think all behavior of

enable sex scans set to off getting, like putting huge penalty to sex scans. It helped sometimes understand that planar behavior like why it switches from this to that, from

index scans. Still, yeah, you can still use for that. Okay. Maybe. Yeah. 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. Mm-hmm. Mm-hmm. Right. Yeah.

But I think you like UX and like it's not easy to like in... Wait, this makes it easier. This makes it easier to see the difference in the costs. Okay. Well,

because you deserve... Because you don't have those huge numbers. But it's not what I want. I want, maybe sometimes always see the second and third plans. Fine, fine, fine. If it's possible, like, somehow, like, at some point, one more set, one more flag in Explain Analyze. You know, show me not one plan, but multiple plans. So, yeah. Okay, too much, maybe. Well, let's go through the other couple of changes that we have got already.

Yeah. one I think is a kind of a bug fix which is for parallel bitmap heapscans you would get exact heat blocks and lossy heat blocks to tell you whether the where the workmen was big enough for that bitmap basically and it turns out they were only reporting the leader process not the work processes so now that's been changed so that you now see per worker details by default without with analysed not just with verbose so power their workers are normally only shown like details for them is normally 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 so that that's kind of maintaining behaviour 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 one, but a big improvement for people that know what they're looking for. TLDR is parallel awareness of

explain plans improved, right? Oh, and accuracy. Yeah, it sounds like a bug to me a little bit. Yeah, it is, it is, It is, but it's good that we've got that, and you might notice the change. And then probably the least likely to come up, but for completeness, did you see we've got a new wall buffers full field as well? So, if the wall buffers happen to have become full during the query execution,

or the number of times they become full now gets reported. Ah, I remember reading about this. Yeah, that's great. Nice. So, yeah, simple one. 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 weight event analysis and see i.o wall buffers and a lightweight lock related to wall buffers all right right and so on this is how we indirectly understand

that probably there's a problem here and contention and so on and then we usually go to and tune siblings and like come in group commit basically like it's it's so let it commit let it wait a little bit slightly and and do f sync or analog for multiple transactions for multiple commits in right heavy right intensive workloads it's very good approach yeah but when i think about micro level like here explain well it's hard because i i i i like usually in explainalize i do it dependently maybe some multiple times but i don't think about macro state of our server yeah i actually i don't imagine this is going to come up much when you're running it manually you'd have to get unlucky it'd have to be i think i imagine in auto expand 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 what runs oh it might be related to this issue this great this is great and but it's it's immediately provokes a couple of thoughts one is you know P99 for for everything including no to explain yeah so on it's we talked about right yeah yeah and another thing is what about checkpoints and buffers written and sync sometimes by back-ends and like initiated by back-ends because something like Because guys called BJ writer and checkpointer, they don't do their job properly.

So while our precious beckons need to do it sometimes, right? So I'm thinking maybe explain should start showing that info as well and also in the context of auto-explan may be 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 oh you're welcome yeah it's helpful it's 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 but good yeah maybe anyway my recommendation still like

let's keep wall included because we we deal with a lot of a lot of startups which suffer from writing too much wall. And again, this brings us to the topic of, let's clean up indexes because indexes write more wall. If you have extra unused redundant and so on, and then bloat as well, you are writing more wall that you could. So eventually, I think it would be so great to say, like, you know, let's clean up this and that. And we predict how much wall rights will be reduced. But at micro level, having wall info is very helpful as well. Although, we always should remember that it depends on the current situation, how far from checkpoint we are, right? And also, it depends on parameters because you take different

parameters, you will do very different thing, including wall rights, will be very different, so maybe. Well, I think it's difficult for Microsoft because you need concurrent workload as world for it to be like super into not concurrent but this state needs to be like fixed same each time same yeah but this is solved by db lab we can reset and be in the same step as a state right so that's great so database branching hub so you start always you have various like ideas or iterations and you always start from very same same state including like everything including tapples including the distance from checkpoint, everything. So this is not a problem.

The problem is, I see, like, for example, if you forgot that parameters matter a lot. In one case, you can get full-fledged update. In another case, you can have hot update. And you will see very different wall numbers there, right? Yeah, sure. But all 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 PG buffer cache here. Well, I'm joking. I think you do get dirty buff. If you include buffers, you get dirty pages. 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 i'm curious what was the reason to introduce this i think there's specific reason right i guess so sometimes these things get added but for completeness so like it was getting

added to some system view and it's like oh why don't we also report this and explain so i'm not sure which way around this one was and but yeah normally these things get added because they needed for some production incident and they weren't available well in defend of this uh there are things that also depend heavily on previous actions for example the status caches right if you use buffer numbers you see reads but next time you see hits yeah this is maybe like in the same route of thinking like yeah okay anyway great i think it was very advanced and i i doubt it like it's everyone should know everything remember everything but in general i feel like the depth of everything is increasing like it's more and more

nuances this is great i appreciate all this thank you so much good speak with you and catch you soon bye bye have a good week