Postgres FM

Nikolay and Michael return to the topic of using the buffers explain parameter — with a new analogy, some (conspiracy) theories of why it's still not on by default, and some related chat about the serialize parameter coming in 17.
 
Here are some links to things they mentioned:

~~~

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 brought to you by:
With special thanks to:

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
PostgresFM, episode number 94.

And we are going to repeat episode
number 4, but not really.

So today we're going to talk about
buffers again.

So let's call it buffers 2.

But I forgot to say hi, Michael.

I was thinking about buffers already
too much.

This topic is super, super important,
I think.

Michael: Yeah.

So last time we called the episode
buffers by default, because

you especially, and I'm a convert
to this, think that buffers,

which is an EXPLAIN parameter should
be on by default, at least

with EXPLAIN ANALYZE.

And there have been a couple of
patches over the years, trying

to put this in place or trying
to make steps towards this as

a goal.

And we were in support of that,
but we also wanted to explain

why we thought people that are
doing query optimization work,

so developers, should include the
BUFFERS parameter when they

run EXPLAIN ANALYZE manually, or
with auto_explain, so that they

can more effectively do query tuning.

Is that a good summary?

Nikolay: Yes, it's a good summary.

Have you ever thought that in EXPLAIN
plans, BUFFERS are not

turned on by default?

Although there is consensus, I
think, and the fact that it was

not changed so far hasn't been
changed.

It's only purely technical reasons,
but I'm sure since you checked

it, probably you'll explain details
here.

But don't you find it's interesting
that EXPLAIN plans by default

have BUFFERS off, but pg_stat_statements
has it on, right?

I mean, just interesting.

Why?

Because if you want to start connected
data, I think we should

do it.

We should look at the whole picture,
macro level, business assessments,

and we also should work with individual
plans provided by EXPLAIN

command, EXPLAIN ANALYZE BUFFERS
command.

And if you forget BUFFERS, just
do EXPLAIN ANALYZE, which many,

many, many, many blog posts still
suggest, unfortunately.

In this case, it's hard for you
to connect the dots between the

individual plans and macro level.

Michael: Yes, an interesting point
about pg_stat_statements.

I reread the 2 threads, the 2 most
recent threads, about turning

BUFFERS on by default.

And the first one was started by
Vic Fearing.

And it actually was He took a step
backwards and said I personally

would like to turn BUFFERS on by
default.

But because I realize that might
not be what everybody wants

and that might be a wider discussion,
here's a patch proposing

that we allow multiple new parameters
for Postgres.

Each one was like it was allowing
you to turn each EXPLAIN parameter

on or off by default.

So, set the default yourself for
your instance.

So you could set BUFFERS on by
default for your own instance

and other people could leave it
off by default.

And that was widely thought of
as people other people wanted

BUFFERS on by default, but because
he also included settings

to enable and analyze by default
and enable every other single,

you know, verbose, right to head
logins, settings actually can't

remember which ones, but basically
all of the EXPLAIN parameters

that existed at the time of the
thread was started also came

with one of these settings.

People fixated on the fact that
an administrator turning on the

ANALYZE parameter by default would
lead to you running EXPLAIN

on its own and that executing the
query, which was deemed to

be a foot gun that would be too
dangerous.

Nikolay: So generalization did
a trick, bad trick here.

And honestly, I think it's not
like I'm not interested in the

ability to turn it for specific
servers.

I want real default for everyone,
global default.

Because I deal with many, many
servers and I cannot tell them

all.

This is what we do right now.

We tell them all right now.

Use BUFFERS by default when you
provide plans.

It works only partially.

It never works.

Efficiency here is not 100% coverage,
right?

So if we can do it by some additional
settings, okay.

Maybe some cloud providers will
do it, I don't know.

But it's this partial solution
I don't like at all.

Michael: I do think we're in a
new world with cloud providers.

I think now we have this, you know,
for example, pg_stat_statements

is off by default.

But in the cloud, most cloud providers
have it on by default.

So some of them have users.

Nikolay: Some of them even learned
about template databases,

template 0, template 1, right?

And put it there.

So each new database created, this
is how it's implemented, right?

Michael: So we're in this new world
where we don't necessarily

have to have things on by default
in Postgres core for it to

be on by default for most users
in the real world.

So There's a slight weird subject
here, but to move on to the

second...

Nikolay: But it's still a partial
solution.

I want Postgres 18, for example,
to have this by default.

What should we do?

But actually, we jumped straight
to this question why it's still

not default, but we didn't explain
what it is, right?

So maybe we should explain first
because it's also important

why we care so much about this.

Why, If a plan doesn't have buffers
data, so what?

Right?

Michael: Yeah, let's do a short
version because I think we did

quite a good job of that in the
first episode we did on this.

Okay.

Which we can point people at, episode
4.

But do you want to give a short
version?

Nikolay: Yeah, let's do a short
version.

So I see this data, as I mentioned,
this data is present in pg_stat_statements

always, so if pg_stat_statements is installed, you

have buffers, shared blocks read,
shared blocks hit.

There are also local and temp pair,
2 additional pairs of numbers,

but it's not like...

Interesting.

Here we will talk only about, let's
talk only about the buffer

pool numbers.

There are also 2 more columns,
dirtied and written.

So if some query changes data,
it's dirtied, if it's written

out of the buffer pool, it's written.

And the Hidden red are interesting,
of course, because this can

give us an idea about cache efficiency
in terms of buffer pool

cache efficiency, because there
is also underlying page cache,

additionally, always still.

And in the EXPLAIN plan, if you run
EXPLAIN, it's only the plan.

If you run EXPLAIN ANALYZE, it's
the plan with execution.

But if you don't put explicitly,
you don't put the BUFFERS word,

and if you put it in parentheses
additionally.

So EXPLAIN (ANALYZE, BUFFERS) in parentheses.

In this case you don't see this
buffer data so you only see timing

and you understand, you cannot
for example understand cache efficiency

in your particular execution of
this particular query.

How much was taken from the buffer
pool, how much was taken outside,

maybe from memory, maybe from disk.

If it's read, it's outside.

You have no idea.

Of course, you can enable timing,
I/O timing, and not regular timing,

which is always enabled in the
EXPLAIN plans, actual timing,

right?

But if you enable I/O

timing, this can give you an idea
about how much time was spent

on I/O.

And as I understand, I/O

in that sense means I/O

outside of the buffer pool, right?

It's so tricky, by the way.

What is I/O?

Does I/O include communication
to what?

To disk?

To memory?

Memory, what memory?

Page cache or buffer pool, both
or just page cache?

In case of TrackIO timing, I guess
everything but communication

to the buffer pool is counted or
not.

Yes, but I would count everything.

So it's tricky.

Anyway, you can see timing again,
but cache efficiency wouldn't

be possible to see.

If you're curious, you do need
buffers.

But in my opinion, cache efficiency
is not the number 1 benefit

that buffers provide.

The number 1 benefit is the amount
of work.

If we know timing to understand
the real efficiency of our mechanism,

we need to understand how much
work was done.

And if we see only the number of
rows returned, it's of course

kind of like this is the final
result, right?

Delivered.

But what's inside?

Inside we need to understand how
much work was done.

And if we see only number of rows
returned, it's of course kind

of like, this is final result,
right, delivered.

But what's inside?

Inside we need to understand how
much actions were needed.

And these actions in the database
world, it's I.O.

Databases are all about I.O.

Of course, we don't do a lot of
calculations.

Usually, we deal with I.O.

The goal is to find the data we
need as quickly as possible to

efficiently change it, rewrite
it, store it, and so on.

So only buffers can show the actual
amount of work that was performed

during the achieving final result.

And this is again, a hit, read,
dirtied, written, shell buffers.

Of course, temporary files are
also interesting if you see temp

buffers written and read.

And this means that this query
needed to deal with...

Work memory was not enough, we
needed to write to disk.

It's also an amount of work and this
can explain some of your timing,

overall latency increased significantly.

So the amount of work.

Let's do some maybe new analogy.

For example, you need to replace
cabinets in the kitchen.

I don't know.

And we're not interested in details.

You need the final result.

And then you hire some contractor
and pay this contractor or

like some team of contractors.

You pay them some money and
they say, oh, you know, this took

1 month.

And you say, well, okay, I see
the final result.

I understand 1 month I pay.

And they said bye-bye.

Maybe it's too much.

How do we understand that it's
too much?

We can compare similar cases.

But it's better to dive in, to
understand what exactly are you

going to do.

Let's check step by step.

You will go buy this thing, you
will put this, you will remove

something.

You start understanding the real
amount of work to be done.

And then you think, really?

You guys spent 1 month for this
kind of work?

This is strange.

But in some cases, they can really
explain, oh, you know, you

have a difficult case here, you
need to change this and that,

and this is really a lot of work.

And then you start understanding,
okay, In this case, probably

1 month is really reasonable.

Similar in databases.

Let's go back to databases.

If you see that to return some
rows, database needed to perform

sequential scan, it's reasonable
to have bad latency, very high

value, because it needed to scan
a very big table.

It's like a lot of work.

And then if it can be optimized,
of course, yes, we can create

index, right?

Maybe we can buy tools for our
contractors, better tools than

they use and so on.

I don't know.

But that inside, this is the key.

You understand the work to be done.

Does it make sense?

Michael: Yeah, it does.

I don't think the analogy is great.

For example, there's 1 parallel
that could make sense.

How many people were working for
the month?

Like was it 4 people flat out for
a month?

Yeah, so parallelism is like an
interesting analogy there.

But I think there's like, you can
see all the steps that Explainer

is doing.

You can see what operations it's
doing even without buffers.

Nikolay: No.

Well, yes, we can see sequential
scan or index scan.

But when you see, For example,
sequential scan, you see rows,

you see index scan, okay, you see
rows are filtered out, but

for example, let's move slightly
to deeper topics.

If the database is bloated, so
to read a thousand rows, we needed

to read a thousand, for example,
sequential scan.

A Thousand rows, they can be packed
in a few pages or they can

be scattered, sparsely distributed
to thousand pages, thousand

blocks.

And you can immediately see, okay,
to read these thousand rows,

we needed to deal with thousand
pages.

Thousand shared buffers hits ideally,
not reads, right?

In this case, you think, okay,
something is not good here.

There is no data locality here.

What can I do about it?

Maybe we deal with bloat or maybe
we just need to think about,

I don't know, like cluster our
data or something.

Not very popular choice, but I
mean, reorganize physically the

order of storage.

But this is good.

I mean, you can understand and
without buffers you don't see

it, right?

Michael: Yeah, I mean, I'm completely
on board with this.

I was just trying to push back
on the analogy.

Like we've discussed this before.

We both agree it should be, we'd
love it on by default.

I make a tool where it really benefits
from when people use buffers.

We give a tip in exactly that scenario
when we call it read efficiency,

when we say the read efficiency
is not good.

And it might be bloat, it might
be data locality, there might

be a different reason.

But yeah, you can't spot read efficiency
issues without buffers.

Or other read related issues like
read speed.

So, if you've got bad disks or
your mentioned like being

throttled by a cloud provider or
something like that. You can't

spot that, you maybe can
get a clue if you're really tuned

into your data volumes and the
time being taken.

But if you've got the number of
blocks or pages, and you can

convert that in your head to, you
know, we're reading 100 megabytes

and it's taking, let's say, 20
seconds.

That's only doing 5 megabytes a
second.

That's terrible throughput.

You know, you've got an idea that...

Nikolay: You touched several interesting
points here.

First of all, sequential scan,
if it...

For example, we have sequential
scan, some filters, and limit

1.

We need just 1 row.

It can be very quick if this row
was found quickly or it can

be very slow.

It takes a lot of time if it needs
to scan through a lot of irrelevant

rows.

And without buffers we have no
idea in the plan, right?

Because we don't see how many rows
were filtered out.

Index will report how many irrelevant
rows are.

Michael: You do see rows removed
by filter in a sequential scan

as well.

Nikolay: How come?

Ah, filter out.

Yeah, okay, sorry.

You're right, you're right.

Yeah, okay.

But it's only about, I understand,
yeah, I agree with you.

It's only about the cases when
storage is not efficient, for

example, bloat or dead tuples,
this won't be reported.

And if we see buffers, we understand,
okay, too many buffers

here are present somehow.

Let's understand why.

And second point I wanted to extract
from your speech.

It's super important not only to
use buffers, but always convert

them to bytes.

You said megabytes per second.

Buffers per second, it's hard to
understand.

Usually.

Michael: It's just, it's also not
what disks, disks don't tell

us how many pages per second they
read.

Nikolay: Exactly, we cannot compare
to our disk characteristics.

We know usually our random access,
sequential access, throughput

numbers, maximum numbers, and for
NVMe, for example, gigabyte,

2 gigabytes per second, then we
understand, okay, we have powerful

disks and like 100 megabytes per
second is already very noticeable.

But if, of course, memory is much
like several orders of magnitude

much faster, but if we say how
many buffers per second, I don't

know.

So we need to always multiply it
by the block size, which is

in absolutely the majority of cases
is 8 KB.

And I wanted to emphasize this
because I think not everyone watched

our episode number 4 about buffers.

So I wanted to emphasize how important
it is.

If you deal with these numbers
and you started using them in

plans and explaining something,
you should understand that your

colleagues might not understand
you unless you convert it to

bytes, to kilobytes, megabytes,
or kibibytes, mibibytes, gibibytes.

So you start putting these numbers,
it's magic.

I saw it so many times, it's magic.

People say, oh, we are using 1
gigabyte here for 10 rows, Okay,

what's happening here?

When we saw like boring buffer
numbers, they didn't have such

reaction at all.

Once they see gigabytes, wow, like
this is not...

I had cases when instead of diving
deep into some specifics of

the plan, it was actually a mistake
if you dive deep straight

to the details of the explained
plan.

Number 1 optimization tip is, okay
guys, how many rows do you

want reasonably to return or touch,
I mean change, and let's

see the whole work done by database
here.

This gives you a very good sense
of how inefficient it is.

And in many cases, it might be
some forgotten limit or something,

and you understand, oh, okay, this
is not what we want actually

at all.

And this is a very high-level optimization
tip.

I see you're skeptical about it,
but it works very well.

Michael: I don't mind it.

I think I've described it, I've
heard this described by others

and described it myself as the
best way of doing work faster

is not doing the work at all.

So if you can avoid doing it entirely,
if you can do less work

in the first place, or no work,
if you don't even need to run

the query, great, get rid of it.

But you're talking about if you
do decide you do need to run

the query, can you just get, like,
Are you doing the smallest

amount of work possible?

Nikolay: So what I think would
be good, you know, there's a summary

in the end of the EXPLAIN plan.

And in this summary, we should
have data in bytes.

It would be great to have it.

Like how much was read-hit?

Michael: You know I've done this.

You know it's actually… I've

Nikolay: done this.

We both did this with our code.

Michael: But I think I did it in
a controversial way.

I did it in a way, I think this
gets down to the kind of trade-off

between being accurate and

being helpful.

If you're already talking about
splitting it into the 4 types,

like, I guess there's actually
8, there's actually 10, you know,

you've got shared hits, shared
writes, shared dirtied, local

read, local hit.

So there's 10 statistics already
if you want to be accurate about

the overall, even the overall reporting.

And what we ended up doing is,
it's horrible and it doesn't make

any sense, but it's nice from like
a, well, we just sum them

all together and give an overall
number.

Nikolay: If you switch from, you
need to switch language and

maybe Postgres itself also at some
point should switch.

Instead of saying shared blocks
read in SP-GiST terminology

or hit, what if we would say shared
block reads, shared block

hits, talking about operations,
not the amount of data.

Because we both know for hits,
for blocks hit, there might be

multiple hits of the same block
many many times.

Nested loop for example, very simple
case.

For reads it's also possible if,
I don't know, it's possible,

I think.

Let's not dive in, let's not spend
time here.

When you summarize it, so instead
of blocks hit, I would say

how many block hits happened, and
how many block reads happened.

And then you summarize and say
how many IOs happened.

I would maybe not summarize them
all together, I would maybe

still distinguish reading and writing
types of like direction

of operation you know.

Michael: I understand but the
reason I love this approach

is I see it separately of

Nikolay: I, I see it separately of

Michael: The reason I like this
is most of the time we're looking

at read queries in like that that's
just the general.

Nikolay: Don't you have write-intensive
workloads?

Michael: Yeah, of course there
are.

But if you're looking at people
trying to speed up queries, like

the vast majority of read queries.

Nikolay: Or they take up data and
extract the search part of

it and optimize it first and select.

Yeah, it also happens.

I agree with you.

Michael: But my main point was,
even if it's a write-intensive

query, it's the before versus the
after.

And it's funny that you used the
word magic a few minutes ago,

that it's the technique that's
magic.

It kind of flips the switch for
people.

I think it's all I use that word,
but in the opposite direction,

I think it takes the magic out
of query optimization.

I think sometimes junior developers
or people beginning think

that adding an index is magic and
it's gone.

We've seen the blog posts.

This query is now 10,000 times
faster.

It's like,

Nikolay: there's no magic here.

This is why.

Yeah, yeah.

So let's agree, there's no contradiction
here at all.

When I started Postgres.AI, my
idea was that Postgres database

administration and optimization
and all other areas, it should

not be black magic, it should be
white magic.

And black magic happens when people
need to spend 10 years because

of lack of transparency of observability
tools or regular tools

and understanding good materials.

Model have much better materials
than 15 years ago, for example.

A lot of good tools.

Buffers is one of the cornerstones
of tools and approaches which

really clarifies everything.

So magic, black magic disappears
and only white magic.

It's white magic when you say,
okay, I did this and now query

is a thousand times faster.

It's great, right?

But looking only at timing, you
cannot achieve it usually.

You do need to look at the work
to be done or is being done,

and this is buffers.

Michael: Well, and if you see on
the one that's a thousand times

faster, that instead of having
to read a gigabyte, or instead

of the sum of buffers being a gigabyte,
the sum of buffers is

now 2 megabytes, like something
in the order of a thousand,

then it becomes obvious why.

Nikolay: It doesn't surprise us
anymore.

Michael: Yeah, exactly.

And it doesn't seem magic to people
anymore.

You start to realize how the index
is working and why it's therefore

so much faster.

So that's the reason I love the
simplicity of it.

But I think it also like, I want
to get back to, is this now

a good time to go back to why adding
buffers by default didn't

work the second time around?

Nikolay: Yeah, please.

I'm all ears.

Michael: Well, you started the
thread.

Nikolay: Yes, maybe I forgot.

Lost.

Tests, right?

Tests.

Michael: Yeah, that was the main...

So, adding it by default, I think
there was a...

One of the problems was the patch
set that ended up getting proposed.

There were about four different patches
that all did slightly different

things.

I think that was part of the problem,
that it wasn't kept as

a simple change.

Actually, the biggest part of the
change proposed was around

tests, as you say.

So, if we turn buffers on by default,
the first question that

you asked and that the patch ended
up doing, was should it be

on for EXPLAIN on its own, as well
as EXPLAIN ANALYZE?

And I think that was a mistake.

I think that overcomplicated matters.

Because

Nikolay: also can have buffers
for play.

Yes.

Right.

Michael: Yes.

But the reason I think that was
a mistake was it changes like

there are far more EXPLAINs in
the test set in Postgres regression

tests than there are EXPLAIN ANALYZEs.

So, firstly, it complicates that.

And secondly, timing is off by
or summary, which gives you the

planning time, is off by default
in EXPLAIN.

So, it's really weird to have buffers
on by default and not summary

on by default.

So, I don't think it makes sense
logically.

But also, it complicates the patch
set and the regression tests.

So if I was redoing this, I would
have a go without that, just

have it on for explain analyze,
not for explain.

Nikolay: Okay.

Michael: Well, that's an opinion.

I don't actually know if it'd be
any better without it.

But the second issue was then,
what do we do with explain...

Explain analyze has timings, right?

But they have a regression test
setting

Nikolay: that

Michael: turns those off so that
each time the regression test

suite runs, the timing's fluctuating
doesn't make the tests fail.

And there was an attempt to add
a similar thing for buffers.

And

Nikolay: that became so to disable
them for tests only instead

of rewriting the tests.

I think, yeah, and maybe it's better
would be to rewrite the

tests, especially because I forgot
to mention the one of the very

good things I like about buffers
is that they are, they don't

lie, they're always stable.

So you can use them in tests and
rely.

Because, well, of course...

They're not

Michael: quite as stable as...

Nikolay: Hits and reads?

Michael: Not just that, planning
buffers as well.

Sometimes you get a lot of planning
buffers, sometimes you don't.

It can depend on...

Nikolay: Okay, at least to some
extent they are stable.

Even if the query, for example,
is blocked by some different

session and is waiting, our session
is waiting for a lock to be

acquired, We can spend a lot of
time on this wait, but buffers

will be fine.

I mean, it won't affect them.

And this is one of the signs.

If you see buffers are low, but
latency is high, maybe locking

issues are involved here.

Right?

Michael: Yeah.

But tests don't, like, they shouldn't
block tests passing, right?

Like, you can handle this a different
way.

Nikolay: By the way, I would bring
my bad or maybe good analogy

back and say if your guys are wasting
a lot of time but do a

little work, maybe there is some
blocker, right?

I mean with a kitchen analogy.

I see some analogy here.

I see.

So at least it's my like I like
this approach when you dive deep

into details, what's happening,
what people are doing, really

doing.

It can be applied to anything,
like maybe lawyers or accountants

or something.

If you hide, you usually pay more.

I mean, if you don't look inside
at the amount of real amount

of work to be done.

You usually end up paying much
more.

And this is true with databases
as well.

If you don't use buffers, you will
be having inefficient plans

and you will need to provision
a bigger instance, for example,

and you will pay more.

Same.

Michael: Maybe we found the real
reason that they won't turn

it on by default.

Nikolay: So too many hackers are
working at cloud companies and

they are not interested in a conspiracy.

Michael: To be clear, I don't believe
that.

Nikolay: Yeah, we have 1, like,
before we finish, because we

have not much time left, let's
touch the very fresh topic, serialize.

Right?

Sure.

Postgres 17 is going to have yet
another option in EXPLAIN, and

it's called Serialize, and everyone
is excited about that, right?

As I said.

Michael: I have seen, I have heard
quite a lot of people quite

excited about it.

Nikolay: It's good.

Michael: I think it is good.

Like, we're getting the option
to see another case where your

query can be slow, but EXPLAIN
ANALYZE at the moment won't show

that it's slow.

So, yeah, for sure, good.

But, again, off by default.

Nikolay: And Tomasz: What
serialize is, first of all, just

briefly, it's amount of what?

Michael: Oh, come on.

What?

Back to buffers, but bytes and...

Nikolay: No, no, yeah, it's by
default shows...

Timing.

Right.

So timing, bytes, buffers, it shows
everything, especially if

you have buffers, It will show
buffers as well.

But what is it about?

It's about serialization.

So if you...

It can connect us to the last episode
when we discussed SELECT

STAR.

And I said SELECT STAR is actually
not a bad thing because I

was comparing this in my head to
the idea to explicitly list

all the columns.

And so for me it's a more convenient
way.

But of course if you need only
for example 1 column, numeric

column, for example, integer column.

But there is a JSON column next
to it which is TOASTed.

Of course, if you select it, it's
a big piece of inefficiency

in this query.

If you don't need it, you don't
use it, but Postgres will need

to deal with TOAST table.

And serialize option can very well
show this, right?

Instead of selecting just 1 number,
we need to do a lot.

Michael: Well, I think the issue
is more that the EXPLAIN ANALYZE

at the moment doesn't show the
inefficiency.

So if you did SELECT * from
that table, you would get a really

slow, like you did it from your
client, you'd get a really slow

result depending on how much data
there was, you'd see in the

client that it was really slow.

Not just because of the...

Well, there's a couple of reasons.

1 is because a lot of data is being
sent across the wire, but

also because of the serialization.

So there's 2 reasons.

Nikolay: But if you didn't EXPLAIN
and ANALYZE...

Yes, Actually, you know, now I
understand that it's pretty, I

didn't see the discussion beforehand
because there's a demand

actually, and I saw it not once,
when people want to see the

plan and actually result as well.

Michael: Yeah, it's a different
conversation.

And this is a...

Nikolay: EXPLAIN, auto_explain
this, of course.

Michael: Yeah, this is only the
serialization part and it's off

by default again.

So it's more output.

I like it.

We can add it to the list of parameters
we asked people to use

in

Nikolay: the same format.

You think it should be on by default?

Michael: I mean, it's the same
argument, isn't it?

Like, we want to see the actual
work being done.

Nikolay: You know, this And that,
and also, I mean, this, all

by default, that, BUFFERS, is all
by default.

And also, ANALYZE is a super confusing
word, because it's used

in other areas of Postgres administration.

How about having new words invented
that will show proper plans?

Of course, we can do it in psql,
for example, with set.

Backslash set.

Michael: I did consider, I would
be interested in your thoughts

on this and listeners' thoughts
on the idea of EXPLAIN all or

something.

That whatever version of Postgres
you're on, it will do all of

the parameters.

I think including Analyze, full
is loaded for sure.

Because of vacuum for it, I think.

Nikolay: Or what?

Yeah, okay.

Let's not pretend we are hackers
at all.

Michael: No, but I think there
is something to it.

Like, what's the end goal here?

Is EXPLAIN going to have 300 parameters?

But like, how many are we going
to keep adding?

Nikolay: Yeah.

Yes.

So, buffers definitely need to
be civilized.

I need to taste it myself for some
time to conclude.

I would say I would have

Michael: a reservation.

I think it's helpful for showing
people where the problem is,

but because it's still off by default,
I think a lot of times

when it would have been helpful,
it won't be used, which is a

shame.

Nikolay: Yeah, that's interesting.

Everything here serializes by default.

I actually didn't give this a thought
before we recorded this

podcast.

Michael: It's funny.

Nikolay: Yeah, it's actually should
be default on.

Of course, I understand like someone
will say it will produce

observer effect, but honestly,
I don't care because timing itself

produces observer effect, maybe
number 1 player here.

And also this observer effect is
maybe not an observer effect

but an actual normal effect because
the regular client side needs

this part.

Michael: Yeah, it's true.

This is the opposite.

Observer effect, normally we're
talking about the additional

timing added by observing, whereas
this is a case where EXPLAIN

ANALYZE will under report the time
taken because of this.

Nikolay: Skipping some work that
is needed actually.

So I need to, My fingers need to
memorize.

By the way, I type EXPLAIN ANALYZE
BUFFERS super fast.

Now I need as well CLIs.

It's already too much.

Michael: I actually now use a text
expander.

So every time I can type EXPLAIN
ANALYZE and it will change it

to the full list.

But you like obviously you need
to work in lots of different

terminals all over the place.

So yeah, probably best to learn
to type fast.

Nikolay: Anyway, okay.

So we like the thing but don't
like its default off.

Maybe we should actually

Michael: Check the reasons.

It probably makes the test difficult.

Nikolay: Of course, test is difficult,
of course.

But yeah, I would emphasize once
again, in tests, in regular

tests, application tests, buffers
is gold.

Because I understand that sometimes
there may be slight differences,

because for example, data is packed
slightly different physically,

right?

Layout is slightly different.

But there is no order of magnitude
difference.

With timing, you can have it.

Disks are slower, or saturated,
or locking issues, something

like that.

And you have timing, which you
don't understand.

With buffers, it's a reliable number
you can use in testing.

Michael: Cool.

Thanks so much, Nikolay.