Buffers II (the sequel)
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.