Hello and welcome to PostgresFM,
a weekly show about

all things PostgreSQL.

I am Michael, founder of pgMustard
and today I am not joined

by Nikolay.

He is finally taking a well-earned
break.

So this week you've just got me.

I am in the process of getting
a couple of guests as well, which

I'm excited about.

So hopefully for the next couple
of weeks we'll be able to give

you those conversations.

But in the meantime I'm going to
record an episode on pretty

much the only topic I feel comfortable
sharing knowledge on my

own, which is EXPLAIN.

And due to the nature of the product
I've been working on for

the past few years, I have spent
a lot of time looking at EXPLAIN

plans and helping folks with those.

So yeah, I've given a couple of
talks on this in the past, but

I realized we hadn't actually done
a podcast episode on it.

We've done a few related ones,
so long-term listeners will be

familiar with some of the things
we've talked about previously,

but we haven't done a very, an
intro level one or even I have a

process I'd like to recommend people
use.

I haven't discussed that yet, so
I'm hoping to give you a couple

of those things.

But on the topic of guests, before
I move on, if you have any

recommendations or requests on
either the guest front or the

topic front, those are always welcome.

Please send those to us either
privately or publicly.

They'd be very, very welcome.

So EXPLAIN first, what is EXPLAIN
and what does it do?

So EXPLAIN is a statement that
you put in front of your query

and PostgreSQL will return the query
execution plan.

So by default you just get the
plan.

So what PostgreSQL would do, what
choices it would make to execute

that query.

It doesn't execute the query if
you just use EXPLAIN and it provides

back the estimated costs involved.

So you might have heard that PostgreSQL
has a cost-based optimizer.

This gives you some insight into
the costs that it's calculating

and why it's making certain choices.

So why it's choosing a certain
scan type over another scan

type or the join order it's
chosen or the join type it's

doing for a specific join.

So all of those are based on what's
possible and also what it

thinks will be fastest.

Now, the costs are in an arbitrary
unit, but the idea is the

lower the cost, the faster the
optimizer thinks it would be.

So, it is correlated to time, but
they're not in a unit of time.

So, EXPLAIN on its own can be useful
to quickly see, for example,

if you have a query that's timing
out or taking absolutely ages,

you can quickly have a look at
the query plan and just check

maybe why it might be slow.

But if we want to know for sure
why a query is slow and it's

executing in a reasonable amount
of time, chances are we're going

to want to use some EXPLAIN parameters
to not just get the plan

back but also run the query behind
the scenes and get execution

statistics.

So the most common of those parameters
that you might have heard

alongside EXPLAIN is ANALYZE.

So and this is different from the
ANALYZE command for getting query

statistics.

This is EXPLAIN ANALYZE specifically,
but there are loads more

parameters as well.

In fact, we did a whole episode
on why we believe, or Nikolay

and I believe, that BUFFERS should
be turned on by default with ANALYZE.

So BUFFERS is another parameter
that gives you extra runtime

or execution statistics alongside
the timings that you get with

ANALYZE.

So with BUFFERS, we also get information
about the blocks read,

written.

So the amount of work being done
by the query, not just the timings.

So that can really help.

But there are a bunch of other
parameters as well.

I like to recommend people use
as many of them as they can.

Most of them don't have that much
additional overhead.

ANALYZE can.

In fact, that's something worth
bearing in mind, that there are

a couple of caveats when it comes
to EXPLAIN ANALYZE.

It can both overreport and underreport the amount of time that

a query would take in the wild.

So for example, it's been referred
to as the observer effect

before, but if we have a slow system
clock, the overhead of checking

the timings can add significant
overhead to the total execution

time when we're running EXPLAIN
with ANALYZE and specifically

with timings on.

So, that's how it can overreport.

That's the easiest way it can overreport
on the execution time.

But it can also underreport if
for example, when we're

running EXPLAIN ANALYZE, Postgres
doesn't have to transmit the data

to us.

So if we're requesting thousands
of rows, and we're not close

to the database, so perhaps it's
a user doing a big report from

a different continent than the
database is on, your monitoring

might be showing that that query
is taking a few seconds to execute,

whereas maybe EXPLAIN ANALYZE would
report it only taking a few

hundred milliseconds because it
doesn't have to transmit that

data across the network.

So it can overreport and underreport,
but generally it's generally

that's pretty uncommon and also
directionally you'll still see

where the slowest parts of that
query plan are, even if the numbers

aren't precise.

So yeah, so those are the caveats.

In terms of the process I recommend,
the number 1, I think I've

mentioned it before in the podcast,
but it's so important, it's

worth repeating.

You need to be running on a realistic
size dataset to get realistic

performance data.

Now, it doesn't have to be the
exact same data.

The important part is the number
of rows.

So for example, if you were testing
out a new feature and your

local dataset only has a couple
of dozen rows, but production

has a few million, chances are
the Postgres plan is going to

be making different choices on
join order or join algorithm,

even scan type.

So it could even choose to do a
sequential scan if your data

is small enough, even if there
is a good index available.

So that can really surprise people,
but it's really important.

As the datasets get bigger, this
becomes slightly less important.

So if you're testing on tens of
millions of rows instead of hundreds

of millions, there's less of a
chance of an issue but still a

chance of an issue getting a different
plan.

So very much worth bearing that
in mind.

If you can test on a realistic
size dataset, please do.

There's lots of tools available
for doing that and for masking

data these days.

But that's, in fact, I think if
we've done an episode on that,

I think we have on benchmarking.

So I'll link that one up.

So that's tip number 1, or step
1 in the process.

Make sure you are, or the person
that you're helping, is testing

on a realistic dataset.

Use as many parameters as possible.

I've hinted at that already.

Mentioned ANALYZE and BUFFERS,
but there are also parameters

for settings, which tells you any
non-default planner-related

settings that you have.

So for example, if you've changed
work mem or random page cost,

these are common ones to have changed,
those will show up.

But also if you, for example, been
testing a few locally and

you've forgotten that you've still
got those set, they'll show

up in your EXPLAIN plan as well
which can be helpful.

Another important one is, well, another
one I find really useful

is verbose, especially when helping
other people, you get to

see information about the schema,
for example, you get fully

qualified object names, you also
get the output of each operation

in the query execution plan, what
data it's sending to its parent

operation, which can be useful
for advising certain optimizations.

A couple of others, a new one is
the write-ahead logging information,

or I say new, I think it's a few
versions old now.

You can also specify different
formats.

So JSON format is particularly
popular these days amongst tools,

but text format is generally still
the most concise and most

readable amongst especially people
that are most familiar with

it.

They'll often ask to see the text
format plan because it's smaller

and more familiar to them.

Now there are a couple of other
parameters as well.

Less important because people generally
leave them on or leave

the defaults in place but timing
can be really useful if you

want to, for example, turn timing
information off but have ANALYZE

on.

So you still get the full execution
timing statistics but

you don't measure the per node
timing, which is where the main

overhead is so I've seen some people
with some tool vendors recommend

turning that off with auto_explain
but worth measuring if it

does actually have any overhead
on your system before doing so

because timing information could
be really useful when looking

at speeding things up.

Costs you can turn off as well.

The only time I've seen that done
and the only time I've done

it myself is when writing blog
posts or sharing query plans where

I just want to make them slightly
less intimidating or draw attention

to the more important parts. So
those are on by default and the

other one that's on by default is
or at least when you use EXPLAIN

(ANALYZE) is summary, which puts
the information at the end.

So like planning timings and execution
times, which are very

useful.

So generally people don't turn
that off, but it means you can

turn that on, for example, with
EXPLAIN, which gives you the

planning time.

Because remember when we just do
EXPLAIN, we only plan the query,

we don't, Postgres doesn't execute
that.

So you can get the planning time
just from EXPLAIN alone if you

use the summary parameter.

But generally I recommend putting
as many of those on as you

can, especially if you're using
a tool to parse it or if you

are getting help from others, the
more information the better

for them.

Cool, so that's the second step.

The third one is glance at the end.

So query plans, especially once
your queries get a bit more complex,

can get quite long.

You can get, feel a bit like a
wall of text.

But a few of the probably rarer
problems, to be honest, but a

few of the fairly common problems
are highlighted very much at

the end.

So things like, if your planning
time is 10X your execution time,

there's very little point looking
through the rest of the query

plan, looking for optimization
potential, because the vast majority

of the total time was in planning.

So that's only reported at the
end and the rest of the query

plan won't help you.

The other two things that are reported
at the end that also report

a total time are triggers.

So trigger executions, they can
be the performance issue.

If you have triggers involved,
there's going to be some overhead

there.

They report a total time.

If that's a high percentage of
your total execution time, you

found your problem already without
looking through the whole

execution plan.

Same is true for just-in-time compilation.

That can in certain cases be 99%
of execution time, which again,

makes looking through the query
plan not as useful.

Cool.

So that's the third one.

Fourth is then once you've looked
at those, then figure out,

okay, we do need to look at the
rest of the query plan.

Where is the time going?

Which parts of these are the most
expensive?

Or if you have BUFFERS on, which
of these scans is doing the

most reads?

The reason I mention that that's
like step 4 is because I think

it's very easy at this point to
jump to other, like jump to looking

to other issues and I've been guilty
of this in the past myself

as well.

So experienced people, I think
maybe even more susceptible to

this than less experienced people,
if you notice a sequential

scan, your eye gets drawn to it
immediately and you think, oh,

maybe there's an issue there, or
you notice a large filter, or

a few thousand rows being filtered,
or a lot of loops or something.

If your eye gets drawn to these
problems that you're used to

looking for, you can get tripped
up or get, you can end up going

down a bit of a rabbit hole on
this.

So my tip is to really check where
the timings go, like where

which of these is really taking
up the most time and then focus

your attention there.

Only once you've worked that out
focus on that part of the query

or that part of the query plan.

Is there a bottleneck we can solve?

And maybe we'll come back to the
other issue we spotted later,

but also the query might be fast
enough by that point and we

can move on.

So, yeah, that's the fourth and
fifth.

So work out where the what the
most expensive parts of the query

plan are and then and only then
what you can do about it.

So on the timings front, there
are a few things to be aware of.

So the execution time is reported
inclusive of so 1 operation's

execution time is inclusive of
all of its children.

So a little bit of subtraction
you'd think might be enough there.

to work out maybe which of the
operations is most expensive on

its own.

But you have to take into account
a bunch of other things as

well.

So, those execution times are a
per loop average.

So, for example, if one of our operations
is being looped over

multiple times, which is especially
common in join operations.

But also, parallel execution utilizes
loops as how it reports.

So, there's a few ways where we
have to then multiply it by the

number of loops before subtracting
from the parent operation.

So these things, you can see how
they can start to get a little

bit complex.

In addition, Postgres reports these
timings in milliseconds and

doesn't include thousands separators.

So I know I personally can start
to struggle with the arithmetic

of all that once it starts getting
into the multiple seconds.

As such, there's a ton of tools
that can help you with this.

I work on one, but there are some
really good free open source

ones available.

Lots of editors include them by
default and pretty much all of

them make at least some effort
to get those timings pretty good.

So some popular ones are Depesz
EXPLAIN, a really nice

thing about that is that it preserves
the text format.

So it's really expert friendly
if you want to share an EXPLAIN

plan with the Postgres mailing
lists, the one they'll be most familiar

with and most comfortable with
will be the Depesz EXPLAIN one.

But EXPLAIN Dalibo is really
popular these days, much more

visual, includes more charts on,
for example, where the timings

are going, where the BUFFERS, like
which operations do the most

buffer hits, that kind of thing.

Much more beginner friendly, and
as I would probably class my

tool as well, pgMustard is visual as
well, but also helps with that

next step a bit more on what you
can possibly do about things.

But yeah, so the main reason I want to mention those

is they can really help on the
timings front.

So you can paste your query plan
in and quickly see where the

timings are going.

I would say that a lot of them
don't do that step 3.

So a lot of them don't immediately
draw your attention if there's

triggers taking up a lot of time
or just in time compilation

taking up a lot of time or even
planning time.

So it is still worth doing that
step first and then checking

out a tool or two that you're familiar
with for the next steps.

Yeah, that's pretty much it for
the process.

Obviously, that fifth step, working
out what to do next, is a

hugely deep topic.

And it's actually where a lot of
the normal talks on EXPLAIN,

if you go to a conference and see
a talk on EXPLAIN, that's normally

where they'll pick up.

So the best one I've seen is a bit
old now.

It's from probably about 2016.

But Josh Berkus gave it a few times.

But I'll share one of the recordings
that I liked a lot.

Yeah, he spent about an hour going
through a lot of the different

join types, scan types, types of
issues you can see, things to

be aware of.

And yeah, it's a lot, even then,
probably only covers the first

10% of what you could know.

There are more recent talks, one
by Bruce Momjian actually, who'd

covered some of the rarer scan
types.

So I'll link that one up as well.

But this is a deep, deep topic
that you can dive into.

I'm a few years in and I don't
consider myself an expert yet.

So that gives you an idea.

But yeah, it's an extremely powerful
tool for query optimization

in general.

Naturally, to speed things up,
it really helps to know which

part's slow.

So please use it.

Let me know how you get on.

It'd be cool to hear.

We've done a bunch of related episodes,
so a couple of those

to check out are there's an extension
called auto_explain.

Now, that will let you run your
queries as normal, let's say,

maybe even on production, and the
users get the results of the

query back, which we don't when
we run EXPLAIN.

I don't know if I mentioned that
up top.

But the people using your database
will continue to run their

queries and get their results as
usual.

But in the background, the execution
plans will be being logged,

any beyond a certain timing threshold
that you set will get logged

to your Postgres logs.

So we've done a whole episode on
that.

Can be extremely useful.

And I'll link that up.

We've also done one on row estimates.

So that's another common issue.

So with EXPLAIN, we get to see
how many rows Postgres expected

to be returned at each stage.

And when it's wrong, when it's
wrong by a lot especially, that

can lead to all sorts of issues
with inefficient plans being

chosen.

So we did a whole episode on that.

In fact, our first ever episode
was on slow queries and slow

transactions.

So that's worth checking out if
you haven't already.

And we did a more advanced one on
query optimization, which again,

we still didn't cover some of the
basics of explaining.

So I'm glad to have done this one
now.

Welcome any questions.

And please do remember to send
your guest and topic suggestions

through.

Happy holidays, hope you all had
a good one and catch you next

time, hopefully with a guest.

Bye.

Hopefully with a guest.