Advanced EXPLAIN

Nikolay and Michael discuss some more advanced topics around EXPLAIN, including some tips for complex query plans, some recent improvements, and an idea or two that might make it even better.
 
Here are some links to things they mentioned:

• Michael’s solo episode on EXPLAIN basics https://postgres.fm/episodes/explain
• Our episode on auto_explain https://postgres.fm/episodes/auto_explain
• Which cloud providers support auto_explain? (Blog post by Michael) https://www.pgmustard.com/blog/which-cloud-providers-support-auto-explain
• psychopg https://www.psycopg.org
• Our first episode on BUFFERS https://postgres.fm/episodes/buffers-by-default
• Our second episode on BUFFERS https://postgres.fm/episodes/buffers-ii-the-sequel
• track_io_timing https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING
• compute_query_id https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID 
• EXPLAIN: beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Creators & Guests

Host

Michael Christofides

Founder of pgMustard

Host

Nikolay Samokhvalov

Founder of Postgres AI

What is Postgres FM?

A weekly podcast about all things PostgreSQL

Nikolay: Hello, hello, this is
Postgres.FM.

My name is Nikolay, Postgres.AI,
and with me as usual my co-host

Michael, pgMustard.

Hi Michael, how are you doing?

Michael: Hello Nikolay, I'm good,
how are you?

Nikolay: Very good, very good.

So it was my turn to choose the
topic.

And I chose the topic naturally.

I thought we didn't discuss EXPLAIN.

Never, ever we discussed EXPLAIN,
right?

We didn't do it.

But then I googled and found out
that you discussed it without

me.

So I thought, it doesn't count.

Let's discuss once again, but maybe
going deeper in some places.

Michael: Yeah, I covered the basics and
I think it's a good suggestion

to cover more advanced topics.

I think EXPLAIN is one of those topics
that you can just keep getting

more advanced.

And there's definitely a level
at which I'll get way out of my

depth.

But yeah, there's definitely room
for a second episode where

we go into more advanced topics
for sure.

Nikolay: Right.

And just to those who don't know,
you spend most of your professional

time dealing with plans because
pgMustard is helping people analyze

and improve queries, particular
queries, looking at EXPLAIN plans,

right?

Michael: Yeah, exactly.

It's a visualization tool for EXPLAIN
and then also tries to

provide performance advice based
on that.

So we get normally people's more
complex query plans.

So I guess this is an area that
I've got more practical experience

than most.

Obviously, everyone knows when
you're building something, people

that build healthcare software
aren't always intimately familiar

with everything about healthcare.

There's certain things you get
really familiar with, but you

also end up having to deal with
things that are not to do with

that at all.

But yeah, I have over the past
few years come across probably

most variations of how performance
problems can be shown via

EXPLAIN plans.

I have some blind spots for sure
but yeah it's been a big thing

for me but also for you right like
this is a big part of your

work's been performance consulting
and

Nikolay: yeah well It's

Michael: hard to do that without
EXPLAIN.

Nikolay: For me, EXPLAIN is not
the number 1 tool usually, except

cases when a customer asks us to
help with a specific query,

which happens as well.

But usually we deal with analysis,
like top-down analysis of

whole workload.

We start with pg_stat_statements
and wait event analysis, performance

insights, and ideas or something.

Then we go down and when we identify
first aggregated query or

normalized query in official pg_stat_statements
terminology.

Only then we find examples of bad
behaving query.

Then we discuss how to improve,
where to, we need a playground,

a proper playground, and maybe
we will want to discuss how to

make it so EXPLAIN plans match what
we see in production.

And only then we go and check the
actual plan and try to improve

it and so on.

Sometimes we have auto_explain,
right?

auto_explain is a great tool.

Michael: Yeah, we did an episode on that
too.

Nikolay: Yeah, yeah, I remember.

And yeah, if you have it, it's
great.

But it's not always so.

It's not enabled by default.

It requires some effort to be enabled.

Although it's present, it's available
everywhere.

So I wish I saw it more often.

Michael: Did you see I did a blog
post on this this year on which

cloud providers provide access
to EXPLAIN?

I don't

Nikolay: remember honestly, I need
to check it.

Michael: I'll share it, it's not
the most interesting read, but

it's like a good reference and
I find it useful for if somebody

mentions a cloud provider to me.

Nikolay: So everyone has it right?

Michael: Yes, pretty much.

Not quite everyone but pretty much
everyone.

And Most of them provide access
to most parameters, so EXPLAIN

and auto_explain have a bunch of
parameters and I found it hard

to check which ones offered which
parameters.

And I've got good news as well,
actually.

I got a nice direct message from
somebody who works at AWS and

the parameters they didn't offer,
there's like 2 or 3 that they

didn't offer, they've submitted
a pull request for.

So I'm looking forward to the next
release of the RDS products,

which I think will include those.

Nikolay: That's good.

So yeah, of course, we also deal
with explain, but it's just

1 of the things and we probably
don't dive too deep as you do.

So where do you want to start

Michael: this discussion?

It was your suggestion, what do you want to start?

Nikolay: Oh, but you're an expert, right?

Michael: Okay, alright, maybe.

I'm not sure, I still feel really uncomfortable with the word

expert.

Nikolay: I'm feeling very uncomfortable with the word analyze.

It should be execute, not analyze, right?

In the EXPLAIN command.

Michael: I mean, naming...

I've been thinking about naming for a while with this because

part of our tool, we give people something to copy and paste

so they can easily copy it and put it in front of their query

if they're using a like PSQL or an editor or something.

And it's getting longer and longer.

The more parameters that get added to EXPLAIN the more like and

and the more information the better for when you're using a tool.

If you don't have to worry about the output getting long and

your visualization tool should be pointing people at the right

place anyway, the amount of data is like the more the better

really.

The more information you get, the more likely it is you can point

out the problem.

However, that it's become like EXPLAIN and then in parenthesis,

ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT, JSON, and there's

more.

There's like, wow, now there's serialize and memory, but most

of these are off by default so you have to specify all of them

to get all of the information.

So ANALYZE is 1 of them but the reason I was thinking about naming

was I wondered if we could I was wondering about suggesting something

like EXPLAIN all But the problem with that is there's so many

pieces of advice out there in the wild about when you run EXPLAIN,

ANALYZE, be careful because your query will be executed.

If your query's data modifying, it will modify the data.

If your query's long, it will take that amount of time to run.

And all of that advice would become not obsolete, but if we change

the name, all of that advice becomes difficult to like, you have

to go back and change all of the advice, or you have to add,

if you want to EXPLAIN, ANALYZE, or EXPLAIN all.

Nikolay: For 5 years, the old word could be supported,

but it's so confusing to explain people that ANALYZE is actually

executing the query and it's not the same ANALYZE as ANALYZE

table name, which also affects probably plan, but you need to

understand this.

If you run ANALYZE table name, probably after that you will see

different plan in EXPLAIN and EXPLAIN ANALYZE, but it's not because

these 2 words are the same words.

They are very different words.

Remember we discussed Postgres 17, it was confusion resolution.

I wish this confusion 1 day would be resolved as well.

Michael: I agree in principle, I just think in practice it's

gonna be painful.

Nikolay: Why painful?

The word execute is not used yet.

First we introduced synonym and then in 5 years we ditched the

old…

Michael: It's used in prepared statements, right?

Like prepare, execute.

Nikolay: Yeah, but well, execute means execute, and analyze and

explain also means execute.

Okay, by the way, Let me confess about something related to prepared

statements.

A few weeks ago, we had an episode about planning time, which

also related to today's topic because planning time we check

using explain mostly.

Of course we can check it in pg_stat_statements but again not everyone

enabled track planning in pg_stat_statements so usually we just

check in auto_explain or regular manually in ad hoc way we check

using explain.

Not auto_explain, sadly.

It cannot show planning time?

Wow.

Okay.

Good to know.

Michael: But in pg_stat_statements, it's if track planning, yeah.

Nikolay: Yeah, if track planning.

So If we, for example, deal with partitioning, with high number

of partitions, planning time increases as we discussed.

But I just blog posted about it and right after that we had a

discussion on Twitter and it turned out that my experiment was

wrong and my ideas were not fully wrong, but not purely accurate,

because planning time is affected only in the very first time

when queries is executed in the session.

If you execute it once again, we already have relation metadata

cache, rel cache, and overhead becomes almost 0.

At least for simple queries.

Michael: Yes, simple queries that can prune down to a single

partition or like a low number of partitions.

And it was David Rowley, who

Nikolay: pointed out… Yes, of course, If there's no pruning,

then it's a different story.

Of course, with the number of partitions growing, more work needs

to be done at planning time.

But yeah, I'm talking about partition pruning happening at planning

time.

The idea is only the very first execution suffers.

Michael: It's so, I mean I'm really impressed David noticed that

so quickly.

Yeah.

I mean he knows his stuff around
this topic, I'm pretty sure

he was involved in the fixes, I
think it was in Postgres 12 to

improve that exact metric.

So yeah, kudos to him for pointing
that out so politely as well.

I think he said it was misleading,
not wrong.

And also a really good reminder,
do you remember when we had

Melanie on to talk about benchmarking,
how difficult benchmarking

was to make sure you're measuring
the thing you think you're

measuring.

It's just a good reminder that
these things are so difficult.

But by publishing them, by publishing
your findings, and by the

Postgres community being so open
to correcting us when we're

wrong, we've all learned something.

I think

Nikolay: that's good.

Exactly.

It was very good.

And I cannot agree it's wrong because
there was no mistake there.

There was a problem that the experiment
didn't do next step,

which of course I should do.

Actually misleading is a good term
here.

I extended blog post right away.

It turned out if you use just EXPLAIN,
no execution, no ANALYZE

work.

If you just use EXPLAIN, you see
you add partitions.

We checked from 0, 100, 200, and
up to 1,000.

You see we're using EXPLAIN, SELECT,
primary key lookup, and

partition key basically lookup.

You see first of all, you see index
scan, only 1 partition.

Partition pruning is working at
planning time.

But planning time grows linearly
with number of partitions and

number of planning buffers also
grows linearly for this kind

of query and it was range partitioning.

We didn't check others.

But once you run EXPLAIN once again
in the same session, only 3

buffer hits always, regardless
of number of partitions.

It's also good relief, right?

So you think, oh, it's great.

I'm going just to relax and I can
have 10,000 partitions for

simple queries like this.

Of course, complex queries will
be different, I'm quite sure.

But if partition pruning works
at planning time, I'm happy, right?

It just tells me I need to take
care of proper connection poolers.

I need to have them always.

The problem with our discussion
and my ideas a few weeks ago

when I said, oh, if you have high
overhead of planning time,

which obviously it's only for first
execution planning.

In this case, just use prepared
statements.

But this is definitely maybe not
only misleading, but also wrong,

because as I actually learned in
detail, if you use prepare statements,

you see that partition pruning
shifts to execution.

So the plan, a generic plan, includes
all partitions, right?

Because we cannot cache the plan,
which is dealing only with

single partition in this case,
but what if we need to execute

the prepared statements with different
parameter?

We need different partition probably.

The cached plan includes all partitions
and you run explain for

prepared statements for a specific
parameter and you see that

execution time, you see subplans
removed.

This is interesting.

Which means that overhead shifted
to execution.

It's not good, right?

But again, overhead, which overhead?

If we have real cache here, all
good.

So it means that for high number
of partitions, not prepared

statements matter.

They can help, but they don't help
fully because of this shift

of partition pruning.

But what actually helps a lot is
poolers.

We have poolers in all heavily
loaded projects.

And if you are preparing to have
huge tables, like we always

say, if you exceed 100 gigabytes,
you need partitioning.

But it also means you do need proper
connection pooling and avoid

the initial overhead of planning
basically.

With high number of partitions,
planning will be not good.

So back to explain.

Michael: Yeah, question back to
explain, kind of linking them.

When you did the second explain,
extracting the buffers and things,

did you use format JSON for that
and parse the output?

Or what did you do?

Nikolay: It was text regular.

Michael: We've just parsed it.

Nikolay: Settings, yeah.

And it was done by our AI, so it
doesn't use JSON at this point.

Just we don't want very bloated
format.

And yeah, and actually, yeah, we
needed to fix how it works because

right now it works through psycopg,
so Python.

If you run a couple of explains
in one shot, it received only one

result.

We fixed that, so now it sees both
results.

The second result is always, in
our case, three buffer hits.

Planning time remains way below
one millisecond, I think maybe

10 microseconds or so, super tiny,
almost zero.

Bottom line here is that usually
we say, if you run

EXPLAIN (ANALYZE, BUFFERS), of course.

We had a couple of episodes about
it.

If you run it, always run it a
couple of times, maybe a few times

because of caches.

If you want to check timing, if
you're too obsessed by timing,

not my case.

I'm obsessed by buffers, not timing.

You need to run it multiple times
to check the second execution

because first time probably everything
is not cached, data is

not in the buffer pool, and not
in page cache, so run it twice.

That's a good piece of advice,
right?

But what we just discussed means
also that even for planning,

run it twice in the same session,
right?

Because maybe you observe a lot
of work, if partitioning is involved,

especially if a lot of work related
to lack of real cache.

Michael: I've seen this.

The other weird thing I've seen,
I don't know if you know the

answer to, is you can do a simple
ID lookup, like primary key

lookup and you will get planning
time, but no planning buffers

reported, even if you include the
buffers thing.

What's that?

How's that?

It's a bug I think.

Yeah.

Nikolay: Inside my team, like last
week, since we worked on this

post, a couple of other… Many people
actually were involved,

but this particular problem was
very annoying.

Very annoying.

So, like, why buffers are not reported
in planning?

Okay, now they are reported.

And we cannot understand what it
depends on.

So, Once we understand, probably
it will be a bug report already,

right?

Because it's something wrong is
there, because we requested.

Michael: It's weird, but I mean
the text format is hurting you

there because by default in the
text format, if things are 0,

they don't get reported, it's like
roughly a general rule.

Nikolay: You think so?

Michael: Whereas in JSON format,
you'd still get buffers reported,

but they would have zeros.

It's not much better.

Nikolay: In our case, it should
be reported because it always

was something like some number
of hits.

But yeah.

Michael: So that's a different
bug, right?

Like all I meant is if planning
buffers

Nikolay: are 0,

Michael: I don't understand how
planning buffers can be 0 if

it's like it was actually doing
work, if it's actually, you know,

picking which index to scan.

So I found that…

Nikolay: – In the case of prepared,
cached plan, if plan is cached,

there is still some planning time,
but I suspect this planning

time occurs during execution because
of these subplans removed

and partition pruning happening.

I'm not sure, by the way.

This is some nuance.

Michael: I think maybe like
parsing might count as planning

or like I think there's other steps.

Nikolay: Well, I'm looking at
it right now.

I'm looking at the plan which checks
execution of the forced

generic plan.

Plan cache mode set to forced generic
plan and we check execution

for prepared statement.

It's cached.

The plan cached.

We see no planning-related buffers
reported at all.

It's missing.

But planning time is like 38 microseconds.

And partition pruning happened
at execution time.

So I wonder what are those 38 microseconds.

If plan is cached, maybe just additional
checks, I don't know.

It's interesting, right?

Michael: You know there's multiple
stages, like, you know, I

can't remember all of them, but
1 of them, for example, is parsing.

1 of them is like rewriting, so
like you might rewrite the query.

I think some of those get bucketed
at the moment as planned.

So

Nikolay: probably you're right,
there are no buffer hits involved

at all and that's why it's not
reported at all.

Michael: But I've seen this, I've
seen 0 reported when it's like

not a plan cache, like if it's
a primary lookup that you've

run.

Nikolay: So where there is some
work for sure, right?

Michael: Yeah, or like in my head
it would make sense that there's

work.

Anyway, it's an interesting quirk.

Nikolay: And even if it's not a
bug, the fact that you raise

this topic and we discuss this
topic inside my team, it's already

something, right, which is not
clear.

And yeah, If someone who watches
us who is more experienced than

us, please tell us.

This is super interesting piece
of information.

So what I wanted to say, You need
to check the execution of explain

for planning for the second time
inside the same session just

to exclude the fact that you observed
overhead from lack of real

cache.

This is lesson I learned last week.

This is correction to ideas I expressed
a few weeks ago in the

planning time episode.

Michael: And there's some good
practices that help mitigate

this kind of issue anyway.

Normally people when they're benchmarking
or testing things will

run something 5 times and maybe
take the best of the 5 or you

know running things multiple times I don't know if you've ever

seen some sports do like diving for example they give people

scores out of 10 and then they cross out the top scoring judge

and the bottom scoring judge.

And yeah, actually,

Nikolay: we did that.

We did it, but we like, due to some reasons, it was not like

We plan to improve this and this kind of analysis should be done

exactly that way.

You run multiple times, you check which data points are way off

and exclude them and then you take average.

That makes sense totally.

But in this case, just how we did it, It was a single session

experiment and we ran all EXPLAINs.

So bottom point, if you run EXPLAIN 5 times and every time it's

a new connection, the problem persists.

You need to run it multiple times inside the same connection.

And then I would not agree with excluding the first 1 due to

lack of real cache, because it's also an interesting fact that

With growing number of PARTITIONs, the very first execution,

it can reach dozens of milliseconds and even hundreds of milliseconds.

It's a lot.

It means connection poolers are needed and they need to be configured

properly if you have thousands of PARTITIONs.

Otherwise, it will be very often new connection established,

could execute the first time, huge number of PARTITIONs, and

you have penalty of dozens of milliseconds for heavily loaded

projects.

It's a lot, maybe 100 milliseconds.

It's like huge overhead.

This fact also interesting, right?

We learned 2 facts.

Overhead is huge, linearly growing for this particular simple,

very trivial case.

There is basically no overhead for subsequent queries.

Wow.

This is like a sequence of surprises for me in this work.

I like it actually.

Let's maybe switch to another topic.

Maybe you have some ideas what to discuss next?

Michael: Yeah, well when I think of advanced EXPLAIN, I'm thinking

more like advanced query optimisation.

Maybe you're familiar and got good at reading EXPLAIN plans when

it's a relatively simple query, maybe some normal scan types,

some normal JOIN algorithms.

You're somewhat familiar with it, you can work your way around

that.

But I've spent several years looking at this and I still from

time to time see operation types that I've never seen before.

And things get complex like in just in terms of how many operations

there are or things that complicate
things like CTEs and in it

plans and kind of things happening,
which order things happen

in, things happening at the same
time.

I think we had Haki Benita on relatively
recently talking about

how if you have multiple CTEs,
those happen at the same time

and independently of each other.

So like, that's confusing.

And like, how those appear and
explain is interesting.

But because it's shown as a tree
and they have to kind of work

out different display mechanisms
for how, you know, for showing

that that's not necessarily misleading
because it's hard to say

how you should visualize that.

But that's what I'm thinking in
terms of like advanced topics

around explain.

How do you interpret those?

And also, what can you ignore?

If you want to do this, normally
your aim is, can I speed this

up?

And as things get complex, you
often want to simplify.

Like, what can I do to discard
a bunch of this information?

Like, which bits of this are already
fast that I can ignore?

How do I, maybe I can run a simpler
query that's only the part

of the plan that's problematic.

That's what I'm thinking in terms
of more complex topics.

Nikolay: Right, I agree.

Reading complex plans.

This is it.

Yeah, I agree.

Here actually what helps?

Experience helps definitely.

I usually like buffers for me as
a must.

Buffers is a must.

First, I start reading execution
time, planning time, but I read

quickly scan buffers and they are
like cumulative, like they

are accumulated to the root.

Root includes everything.

So you can see and go from root
to leaves basically.

It's like upside down tree, right?

So we top down analysis again.

Okay, we understand the number
of buffers, understand how many

megabytes or even gigabytes sometimes
it is if you multiply by

the block size, which is in most
cases 8 kibibytes.

Then we go down and see where exactly
this huge volume came from.

This is my default type of work
with plans, including execution,

of course.

Because if you don't have execution,
you only have cost, and

buffers are reported only for planning,
as we just discussed.

In this case, usually it helps.

In most cases it helps.

And I wish I also was able to see
lock-related information, right?

Mixed plane, plane.

Michael: T.

Yeah.

Like weights.

Yeah.

It's difficult to see how they
would do that, but...

Nikolay: Not necessarily weights.

Michael: What do you mean by locks?

Nikolay: If we...

Yeah, yeah, actually maybe you're
right.

Yeah, what's happening under the
hood slightly, but it's already

super complicated output, right?

So...

Michael: Yeah, and there's macro
versus micro issues here.

If you're on a test environment
where you're the only person

there, it's much less likely that
you're going to be incorrect.

Nikolay: When I said locks,
I was meaning heavy locks.

So at planning time, all indexes
are locked, And all tables and

all relations are locked.

And that's a lot of overhead.

I wish I saw it.

It's interesting information.

Accessory lock for all of them.

So, and if it's update, okay, what
kind of lock I have here?

Michael: Oh, interesting.

Yeah, that's not what I was thinking
at all.

Nikolay: Yeah, you thought about
lightweight logs.

Michael: Well, no, I was...

Oh, even a different...

Or weight events.

No, I was thinking, like, if you...

Easiest experiment to show this
is start 1 transaction and maybe

do some DDL or like yeah drop a
column or something yeah anything

that's doing well add a huge like
a really heavy lock on that

table, then open a second transaction,
just do a simple SELECT

query on that table, go back to
the first transaction and commit,

go back to the second 1 and see
that your query was just waiting

that entire time.

If you run that query with EXPLAIN
in that

Nikolay: second session...

If you commit it, it's not waiting

Michael: anymore.

But the execution time of that
EXPLAIN will be huge.

Nikolay: Oh yeah, yeah, yeah, yeah,
yeah, yeah, yeah, you're

right.

And this execution time can be
explained if we saw basically

a pie chart or something, or a
distribution of wait

Michael: elements.

There will be no node, there'll
be no operation in that EXPLAIN

plan that shows that it was waiting
on an exclusive lot.

It will just show that 1 of the
operations was extremely slow.

Nikolay: Simple wait event
analysis for this particular

session would help.

Michael: Yes, but it doesn't
come up that often to me.

Maybe it's 1 of the blind spots
because I don't see those, but

I don't see plans that have these
really unexpected bottlenecks.

Nikolay: I would
like to be able to, not by default,

but I would like to be able to
see these wait event analysis and

heavy locks as well because I want
to understand this query,

which locks it's going to acquire
when I go production.

When execution finished, what locks
were acquired?

I think interesting extensions,
but it's already so huge.

Michael: And as I mentioned, we're
getting more and more, I think

this is part of the reason people
are adding more parameters

to explain, so write-ahead logging
information.

Nikolay: It should not be by default,
it's too much.

Michael: Well, I actually have
a proposal here.

I haven't suggested it on any of
the mailing lists yet, but I

would like your opinion.

I thought about adding them by
default when you include the parameter

verbose.

So if you do explain, analyze verbose,
give me everything.

Nikolay: You just don't want to
update your website

Michael: snippet.

Yes, yeah, I don't want to update
that snippet for sure, but

also I want people to only have
to remember to do that and they'll

get whatever Postgres can give
them.

Verbose means really long.

It means everything.

It kind of means like, I'm okay
with loads of information, give

me it all.

So it's already kind of in the
meaning.

Anyway, I haven't proposed it yet,
but that would also come with

buffers, so I thought you might
be happy.

Nikolay: Okay.

And also, if you talk about what's
missing and what you cannot

get from EXPLAIN, I also like at
some point, maybe some extension

of this could be, imagine if we
see operations at disk level

as well.

And we understand not only hits
and reads for the buffer pool

but also hits and reads for page
cache for example and maybe

CPU analysis as well for this particular
execution if it is explained

and analyzed.

Michael: Right?

That's a good point.

It's not exactly what you're talking
about but loads of people

don't realize because it's not
an EXPLAIN parameter that you

can, with track_io_timing on, you
can start to get some insights

into the disk level performance
and that's improving in each,

Like there's recent, even in 17
we got some improvements there.

Like buffers are split into shared, local and temp.

IOTiming wasn't initially and is now split into all 3.

Nikolay: Yeah, that's great.

But I'm talking about like basically pg_stat_kcache, but for

EXPLAIN.

Michael: I understand, but the timings are a good start, right?

Nikolay: I agree.

Yeah.

And actually, I think it's possible to have all of this right

now already, but you need to create, like, connect many pieces.

And for example, it could be a tool which runs EXPLAIN, but also

looks at wait event analysis for this pid in pg_stat_activity

and locks as well.

And what else?

It collects everything and big report for a particular query.

And also physical.

Physical, we can, if it's, we have physical access, we can do

it from proc.

Knowing PID, this I think we had prototype for our old bot.

So it's like you just, counters, counters from proc.

Michael: You can get, because if you're the query identifier,

which is the normalized 1 that you can match to like pg_stat_statements,

I'm imagining.

This also, yeah.

Yeah, you could start to like tie these things, all of these

things together.

Nikolay: Yeah, a lot of stuff is possible.

Who's doing this, by the way?

Maybe someone is doing this.

Michael: I don't know the monitoring tools well enough to know

who's doing some of this micro level, but if they are, let us

know.

Like if you're using 1 or you if you're building 1 that does

that would be cool to hear.

To be honest though, it goes back to some of the conversations

we've had about they're like macro to micro zoom, like once you've

done your macro analysis and you've identified a few problematic

query, like the main consumer, if you've got a few bottlenecks,

EXPLAIN is a really good tool for doing that micro, like once

you've got some sensible set of parameters that you know will

be slow, it's not that hard to reproduce normally.

The difficult thing is then going through a 200 line explain

plan.

You asked what can help.

I think we've put a lot of effort into helping with that stage

and a lot of the other tool providers have as well.

1 thing we do that I think is really helpful is if a sub plan

is fast like as a proportion of your query, we collapse it by

default.

We hide like we don't hide it.

We just say there's like there's
20 nodes here.

Probably you don't want to look
here, which means that like a

300 line plan can go down to like
20.

Nikolay: That's cool.

Michael: But that like those kinds
of things I think can really

help initially.

Like not it's not good for a hacker.

It's not good for a Postgres hacker.

It's like trying to work out the
cost model for a new feature.

But for somebody just trying to
speed up a single query, you

don't need to look at the 200 partitions
that all took 0, or

that were never executed, or that
took 0 milliseconds.

Nikolay: Yeah.

And you mentioned query ID and
for example, like I think it was

last week we were working with
1 of the customers, we saw that

auto_explain was used and it's great.

And There was a task to analyze
a lot, and we still plan it.

I'm very thankful to you that we
have integration in our AI right

now, and your system helps our
system.

I plan to perform some massive
analysis at larger scale to be

able to do it.

But the interesting thing is that
if you want to analyze a lot

of queries from auto_explain, you
do need some...

You basically need to compute...

It's called compute_query_id parameter,
right?

You need to turn it on because
by default it's auto and for auto_explain

it will not work.

Michael: It does if you have
the boson.

Nikolay: Okay, in auto_explain.

Okay, in our case we didn't have
the boson, I think.

Michael: But only as of 16, I think.

Nikolay: It's better to turn it
on, actually.

Michael: Maybe, yeah.

Nikolay: The question of overhead,
and let's, by the way, talk

about overhead in a second.

But Imagine you have a lot of query
cases, various queries from

the log.

Presence of query ID first gives
you understanding how many different

normalized or aggregated queries
you are dealing with.

Second, you can then join this
information with pg_stat_statements

and go to macro level back and
understand, okay, this query is

not very influential because it's
a tiny part of or maybe vice

versa, it's influential although
we see only a few occurrences

in our slow log because we have
threshold by time.

auto_explain mean duration, query
mean duration.

We usually set it to 1 second,
for example.

If we have only a few cases, but
we see in the workload, oh,

this is actually 50% of all total
exact time, oh, we do need

to think about it properly, and
influence of this optimization

will be high.

Let's do it.

This helps understand when you
have a lot of queries to deal

with, where to start.

You want to start with most influential
steps.

So query ID is a good thing.

I'm glad we have it everywhere
in EXPLAIN, in the logs, in

pg_stat_statements, you can connect all
the dots.

And pg_stat_activity as well, right?

So wait event analysis is possible
just joining by query ID as

well, if you run many, many times,
right?

So, yeah, great.

What about overhead?

I think it's also an advanced topic.

I know you blog posted about it
and OnGres did.

You talked about auto_explain,
right?

But Explain itself also has overhead.

Michael: Well, Explain can have
both, can under-report and can

over-report the timing.

Nikolay: Django, I'm not a fan
of Django.

Michael: Well, but bear with me.

I think people focus a lot...

There's different things.

The OnGres thing was about the
queries being slower, like you

actually cause some overhead by
observing and that's true when

you're running EXPLAIN ANALYZE
with time because timing is on

with Analyze by default But it's
also true if you're running

auto_explain, like there's also
some overhead to measuring and

to logging, like if you log a lot
of plans that's some overhead

as well.

So there's those overheads but
there's also the possibility that

it under reports which I

Nikolay: think

Michael: throws some.

So this is 1 of the reasons they
added the parameter serialize.

If you are doing a lot of work
after the query is executed but

in order to send the data to the
client.

Nobody really cares about the server-side
time.

The server-side time is important,
that's what we get from EXPLAIN

ANALYZE, but what they normally
care about is the user experience,

like the full client to server
to client time.

Nikolay: And- I cannot agree here,
but okay, go on.

Michael: Okay, well, what I mean
is, If you're getting a report

for my dashboard is slow, there's
no point only speeding up the

server side time.

If you're sending huge amounts
of data, and part of that work

is, so serialize is part of it,
It's getting the data ready to

transmit.

And then there's the network overhead
as well, like of actually

sending all that data.

So, yeah, I guess you're right.

Nikolay: It's not network cost.

It's not like transmission cost.

It's a cost of, not cost, cost
may be not a good word, it's overloaded

here, right?

It's time, right?

Or cost, or it's cost, it's cost

Michael: actually, right?

Serialized is reported as time
and amount of data.

Nikolay: What about cost?

Okay.

Michael: I actually don't think
it has cost to relate.

I think it's 1 of those things
that gets reported in the summary

section.

So, just-in-time compilation and
planning.

Nikolay: It's about civilization,
not transmitting.

Michael: Yes, It's a step before
getting the data ready to transmit.

Nikolay: It's still everything
on the server.

Michael: For example, uncompressing
or decompressing TOASTed data.

Nikolay: And it's Postgres 17 feature,
right?

So it just goes deeper to understand
what's happening on server

side, like execution and what part
of it is associated with civilization,

attributed to civilization, right?

Michael: Well, and not just
that.

Before, if you ran EXPLAIN ANALYZE,
SELECT some data that had

been TOASTed from this table, you
would get a really fast execution

plan because it wouldn't do the
serialization.

It would say this executes really
quickly.

So it wasn't just that you couldn't
see the time, it was like

a query that might take 100 milliseconds
when you run it from

psql, if you put EXPLAIN ANALYZE
in front of it, it could run

in 1 millisecond or 10 milliseconds
much much much faster.

So because it didn't have to do
that expensive step.

So this is 1 of those cases that
used to be and not just used

to be like by default this will
still be, this is still the behavior

in version 17.

If you want to explain, analyze
some basics.

Nikolay: SELECT star from blah
blah blah and before 17 we didn't

see this part at all.

Michael: Even in 17 you don't see
it unless you also explicitly

ask EXPLAIN ANALYZE serial.

That's what I've been saying.

Nikolay: And what about verbose?

Verbose includes it, right?

Michael: No it doesn't.

But this is my proposal that verbose
should include all of these

additional parameters.

Nikolay: It should, of course, I agree.

Well, okay.

It is as it is right now.

Michael: And I have to actually say, EXPLAIN is awesome.

EXPLAIN in Postgres in particular is the best I've seen of any

Database that I've worked with.

Other data, like MySQL for example, is catching up.

They got EXPLAIN ANALYZE in version 8, but the amount of detail

we can already get is way, like, it's beyond the other open source

Databases I've seen.

So it is good, it's just always can be better, right?

Nikolay: Yeah, yeah, yeah.

If somebody is watching us thinking, oh, a couple of haters,

right?

Haters of EXPLAIN.

It's just a lot of stuff, yeah, yeah.

But a lot of stuff is helping all the time, it's true.

Do you want to discuss overhead from buffers option?

Michael: I couldn't.

When I did some really basic, really basic experiment where

I tried to, where I did very, very small queries, but I couldn't

get any additional overhead.

I couldn't, there wasn't like a measurable extra overhead of

requesting buffers.

I think you said you did an experiment once that did show it,

but I don't remember.

Nikolay: Yeah.

On timing.

It cannot, like buffers are like, they are basically invariant.

Of course, there can be hits or reads or they're like moving

back and forth.

But if everything is cached, it's very stable.

Like you're reading the same data, like same query, just SELECT

multiple times, you will see the same number of buffers all the

time.

That's one of the reasons I like them.

But if you include buffers, I did see in the past, it was some

time ago, that timing is increasing.

Because a lot of buffers need to be reported and counters inside

need to be presented.

If it's a complex query, in many places this overhead adds up

and the timing without buffers option is very different compared

to with buffers.

Michael: I've seen single queries where that's true and I've

seen I think Lukas Fittl from pganalyze has done some examples

of this.

I think I've only seen it in queries that have nested loops with

lots of loops.

That's the example everybody turns to.

For example,

Nikolay: join with nested loop.

Michael: But the reason I think
it's slightly overhyped is when

you're optimising, you don't always
care.

If the EXPLAIN analyzes 4 seconds,
or maybe let's pick 400

milliseconds, and your real execution
time is 300 milliseconds,

if you can speed the 400 milliseconds
up to 50 milliseconds,

the 300 might go down to 40, and
directionally you're still looking

for the same things.

And by asking for buffers information,
you're getting more information,

you're more likely to be able to
optimize your query.

So I don't care as much as most
that it's slightly different.

Nikolay: Maybe I only care that including
buffers don't change

buffers.

That's it.

That's it.

So For me, when I start working
with buffers, I'm less concerned

about timing.

Focus on buffers, optimize, optimize,
optimize, and then go back

to result and see, oh, timing has
improved drastically because

we reduced number of buffer hits
and reads.

That's it.

This is a very, very common path
for me.

Just reduce because you like sequential
scan, lots of buffer

hits and reads.

We are at an index, okay,

Michael: couple of, 3, 4, 5 buffer
hits, that's it.

Time to look at timing.

Oh, of course, expect it a thousand
times faster, right?

I like, I like, you know, I like
buffers a lot, but for me they'll

always be secondary because just
Because if you look at timings

first and last, that's the main
aim.

The main aim is to get things faster.

And yes, in maybe 99% of cases,
it's going to be I/O.

That's the limiting factor.

But in some queries, there'll be
a sort in memory.

There'll be something that doesn't
report extra buffers in EXPLAIN.

There'll be just-in-time compilation,
or there'll be some limit,

like maybe trigger timing.

Nikolay: Just-in-time compilation
is off.

Michael: But all I'm saying or
planning time, like actually planning

time, you now do get buffers, but
trigger times, just in time

compilation, there's like a few
other examples, sorts in memory,

hashes in memory, like if some
of these other bottlenecks don't

report buffers, I would just, why
not look at timings and then

buffers?

Also, I almost always see query
plans with both these days.

So it's kind of a

Nikolay: care.

That's great that buffers are present.

At some point I started saying
if there are no buffers I'm not

dealing with this query.

This is what I say to customers,
go and give me with buffers

because we are missing super important.

Okay, if you put it in second place,
this place should be always

present.

Yeah.

I'm sure

Michael: that's what it's like
before.

I think that's probably enough.

I've got a talk that I gave a few
years ago called explain beyond

the basics which I would have thought
we had covered some of

the bits of but actually I took
a completely different path that

I went down some of the less common
types of issue that you can

spot with explain which we haven't
really talked about so I can

include that as like a if anybody
wants to know a bit more of

the specifics, it's a bit nicer,
like that format is good because

you can actually see examples of
EXPLAIN plans, so it's probably

a bit better video wise.

And yet the reason I see buffers
more often now is everybody

suggests adding it, You, but also
some of the other tools as

well, like depesz added it to the
default thing that he suggests

people get.

So there's loads of people.

Nikolay: That's news to me.

I remember it was missing and it
was a big disadvantage.

Michael: He added it.

It's great.

Nikolay: That's good.

Yeah.

Michael: Anything else you wanted to add?

Nikolay: I think it's enough.

Michael: Me too.

Nikolay: Thank you.

See you later.

Bye.