
PostgreSQL's Explain Analyze Made Readable - GordonS
http://explain.depesz.com
======
arkh
Inspired from it you also have pev if you prefer something more visual :
[http://tatiyants.com/postgres-query-plan-
visualization/](http://tatiyants.com/postgres-query-plan-visualization/)

~~~
KoenDG
Came here to say this. The visualization is really nice, and it provides a lot
of explanation of what specific keywords mean.

------
Dangeranger
My favorite tool for this kind of analysis was pgAdmin3, which had a very nice
diagramming output for EXPLAIN ANALYZE which you can see here [0]. Hovering
over the various images in the diagram would display their time, rows, and
which statement caused them.

There is also this excellent post which shows how to break down an EXPLAIN and
reason about the performance [1].

[0]
[http://www.postgresonline.com/images/journal/explain_plan_5....](http://www.postgresonline.com/images/journal/explain_plan_5.png)

[1] [http://www.postgresonline.com/journal/archives/27-Reading-
Pg...](http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-
Graphical-Explain-Plans.html)

~~~
unclebucknasty
Is there a tool for Postgres that will provide optimization tips over time,
based on actual queries run against the DB?

Seems a much more efficient approach than analyzing and diagramming
(sometimes) complex explain results.

~~~
Dangeranger
Probably the closest thing to what you are asking for is pgBadger [0]. If
there is a better tool I'd love to hear about it.

[0] [https://github.com/darold/pgbadger](https://github.com/darold/pgbadger)

~~~
unclebucknasty
Thanks for the tip. Looks interesting, but still on the "analyze and do
something" side vs monitoring then making suggestions.

In other words, this could be another input for the kind of tool I'm thinking
of.

Kind of surprised this doesn't exist, as it seems doable. Given a combination
of query optimizers, slow query monitoring, and common tuning techniques, even
a rudimentary, heuristics-based recommendation tool could provide
significantly more value then manually analyzing explain and log file output.

~~~
Dangeranger
Looks like there is a service similar to what you asked for, but it only
applies to MySQL, PerconaDB, and MariaDB right now [0]. Maybe as PostgreSQL
grows in popularity more services will target it. Or go build your own, it
sounds like a viable business.

[0] [https://www.eversql.com/faq/](https://www.eversql.com/faq/)

------
guptaneil
I use Depesz's Explain tool regularly for discussing queries with distributed
teams in Slack. It's an indispensable tool.

By the way, if you think this is cool, Hubert (creator of depesz) is a DBA at
Instructure, making software for schools (Canvas) and employee training
(Bridge). We open-source a ton of stuff (code.instructure.com).

We're hiring leads + senior engineers in Chicago, Salt Lake City, Seattle, and
Budapest! If you're interested, feel free to reach out to
neil+hn@instructure.com or check out what's available at
[https://jobs.lever.co/instructure?lever-
via=NiHimSaI8r&team=...](https://jobs.lever.co/instructure?lever-
via=NiHimSaI8r&team=Engineering)

------
beefield
I am a bit frustrated with one question regarding explain cost. Namely how to
map the cost to wall time. Based on my research so far, andwer is about "you
can't". But that is difficult for me to understand. I mean, I do understand
that it does not map with high (or even relatively low) accuracy. But the
cases where I have been needing the information are more like me wondering
after 15 minutes of runtime whether the query is just heavy and takes more
time than expected (up to even hours) or did I mess up something and the query
in its current format is going to run approximately until the heat death of
the universe and I better rewrite it. It just feels weird if I can't get even
that accuracy out of the cost number.

~~~
barbecue_sauce
Postgres' cost model uses a bunch of constants that evaluate the relative cost
of queries with regard to CPU and I/O and random vs. sequential reads. You can
actually tune the cost model yourself by changing the values of the constants.
Cost does not really map to wall-time in any way besides the default constants
being chosen relative to the cost of a sequential read as a basis (and
Postgres admits these relative values are not scientific or necessarily
accurate).

~~~
beefield
> Cost does not really map to wall-time in any way

This is a claim that is _extremely_ difficult for me to believe. Because this
statement says that you really can't say that a query in one system with cost
of 10 is any more likely to be run faster than a query with a cost of 10^300
(these are made up numbers) in another environment. If there were way to get a
mapping with even accuracy within two orders of magnitude (e.g. a query with
cost x is likely to run something between 10 and 1000 minutes) on my current
system, I would be perfectly happy. Even three orders of magnitude accuracy
would likely be helpful.

~~~
barbecue_sauce
You seem to be ignoring the second half of that sentence:

> ...besides the default constants being chosen relative to the cost of a
> sequential read as a basis (and Postgres admits these relative values are
> not scientific or necessarily accurate)

The costs will always be relative to each other as defined by your constants.
The whole point of the cost model is to compare plans so that the fastest one
can be chosen. If you hyper-tune your constants according to your system
(which I feel is probably pretty difficult to do accurately), you could maybe
get something within the orders of magnitude you want.

------
_asummers
In the "possibly unknown Postgres tools" category, I'm also a huge fan of PG
Hero [0]. Gives you a nice sortable UI to look at running queries, see what
the longest ones are, the most frequently called, etc. This is all just
reading from pg_stats and such but the UI is very nice.

[0] [https://github.com/ankane/pghero](https://github.com/ankane/pghero)

~~~
aboutruby
On Heroku there is pg-extras: [https://github.com/heroku/heroku-pg-
extras](https://github.com/heroku/heroku-pg-extras)

------
ryanmcm
I've enjoyed using pev[1]. It's based on this tool but lays out the plan
graphically.

[1]
[http://tatiyants.com/pev/#/plans/plan_1550754076978](http://tatiyants.com/pev/#/plans/plan_1550754076978)

~~~
postit
I use that a lot.

FYI, the tool saves the plans on your local storage, so links won't work :)

------
bastawhiz
I've used this in the past. What I find that it's missing is the _why_, not
the _what_. It's not that my query is performing a full table scan that I care
about. It's why it's not using one of the six indexes that I want to know.

I would pay good money for an extension that could answer how the query
planner makes decisions about a given query.

------
ryantuck
+1, this tool is great.

Someone made a flame graph analyzer for Oracle queries, which seems like an
even better way of visualizing explains, and seems like it would be fairly
straightforward to translate to postgres:
[https://externaltable.blogspot.com/2014/05/flame-graphs-
for-...](https://externaltable.blogspot.com/2014/05/flame-graphs-for-
oracle.html)

------
netghost
If this tool is useful to you, it's highly recommend reading the author's blog
as well. It's a great resource for learning about postgres.

~~~
ohlookabird
Indeed, a great resouce! I really liked how he explained and benchmarked an
implementation for foreign keys to partitioned data (sadly not available
natively yet) in this multi-part series from last year:
[https://www.depesz.com/2018/10/02/foreign-key-to-
partitioned...](https://www.depesz.com/2018/10/02/foreign-key-to-partitioned-
table/)

------
petepete
There's not much I miss from the days of SQL Server 2000 but a robust, fast
official client is one.

In my opinion isqlw is the gold standard, and it had something like this built
right in.

------
tildedave
This is one of my go-to sites for optimizing Postgres queries. While all the
information is available in the text output there's something nice about the
site highlighting the rows scanned for the bad parts of the queries in a dark
red ;)

My only nits - it's kind of annoying to have to click a checkbox to make sure
my analysis is not public and to keep the window open so I can delete it after
I'm done.

------
TheTaytay
This is indeed an awesome tool. We've used it quite a bit for local profiling
of known slow queries.

FYI because I wish someone had pointed this out to me when I stumbled across
tools like this: For profiling really busy production DBs, I highly recommend
[https://pganalyze.com/](https://pganalyze.com/) Not affiliated - just an
ecstatic customer.

------
chris_wot
Kind of need to see an example...

~~~
imbradn
There is a History tab with examples, such as:
[https://explain.depesz.com/s/vzlS](https://explain.depesz.com/s/vzlS)

This is an indispensable tool for Postgres query plan analysis. Even for those
who are experienced at reading them, this can help to sort problems.

It's also a great way to share query plans when asking for help from the
Postgres community.

------
protomyth
About the only thing I miss from the old Ingres database on OpenVMS was the
query plan view. This page (with one of the most horrible backgrounds for
viewing)
[http://ariel.its.unimelb.edu.au/~yuan/Ingres/us_38697.html](http://ariel.its.unimelb.edu.au/~yuan/Ingres/us_38697.html)
has some examples of what I remember. Seeing the letters FSM still fills me
with dread.

------
paulddraper
Heard of depesz; haven't used it.

I rather like the PostgreSQL Explain Vizualizer. [1]

I can keep multiple plans around, and it has a lot of detail.

[1] [http://tatiyants.com/pev](http://tatiyants.com/pev)

[2]
[https://github.com/AlexTatiyants/pev](https://github.com/AlexTatiyants/pev)

------
perlgeek
Off topic, sorry, but it makes me happy to see a Perl-based tool in the top 5
on Hackernews :-)

------
caf
It seems like every plan, or nearly every plan, on the history page is the
same, identical sample plan from the submission page.

For example, five chosen at random:

[https://explain.depesz.com/s/9JNv](https://explain.depesz.com/s/9JNv)
[https://explain.depesz.com/s/jRXr](https://explain.depesz.com/s/jRXr)
[https://explain.depesz.com/s/cxjk](https://explain.depesz.com/s/cxjk)
[https://explain.depesz.com/s/FPur](https://explain.depesz.com/s/FPur)
[https://explain.depesz.com/s/GD8](https://explain.depesz.com/s/GD8)

I think it should de-dupe identical plans at submission time.

------
amarraja
I knew the design looked familiar. If anyone uses Solr, this does the same for
its debug output

[https://explain.solr.pl/](https://explain.solr.pl/)

------
foreigner
Anybody know of a tool that will do something like this for AWS Redshift?
Redshift is based on an old version of PostgreSQL.

~~~
Tostino
From what I understand, this will work just fine for the explain output from
redshift.

------
it
If it's better, why not submit a pull request to PostGres and try to make it
the default output style?

------
kennydude
pgAdmin has a really good EXPLAIN visualisation tool built-in which makes it
into a neat little diagram

~~~
dijit
pgAdmin by itself is a _really_ horrible program to use otherwise though.

I do wish that it was as polished as the database core itself seems to be.

~~~
sbuttgereit
Yep... the one very good thing that I got out of pgAdmin was that it finally
convinced me to get psql under my fingers so I wouldn't have to use pgAdmin.

~~~
ganomi
If you still would like a little bit of GUI take a look at
[https://dbeaver.io/](https://dbeaver.io/)

~~~
cpburns2009
The three things that I still miss from pgAdmin 3 while using DBeaver are:

1\. The server status window (when it used to work).

2\. The visual explain query view.

3\. The statistics tab. Though, DBeaver now has a simplified one for tables.

------
holtalanm
i use explain.depesz almost daily. awesome tool

------
ddebernardy
Maybe add [2008] to the title:

[https://www.depesz.com/2008/12/04/explaindepeszcom/](https://www.depesz.com/2008/12/04/explaindepeszcom/)

~~~
GordonS
I was posting a URL to the tool, not a post about the tool. A date didn't seem
appropriate.

