Tag: explain

Waiting for PostgreSQL 18 – Enable BUFFERS with EXPLAIN ANALYZE by default

On 11st of December 2024, David Rowley committed patch:

Enable BUFFERS with EXPLAIN ANALYZE by default
 
The topic of turning EXPLAIN's BUFFERS option on with the ANALYZE option
has come up a few times over the past few years.  In many ways, doing this
seems like a good idea as it may be more obvious to users why a given
query is running more slowly than they might expect.  Also, from my own
(David's) personal experience, I've seen users posting to the mailing
lists with two identical plans, one slow and one fast asking why their
query is sometimes slow.  In many cases, this is due to additional reads.
Having BUFFERS on by default may help reduce some of these questions, and
if not, make it more obvious to the user before they post, or save a
round-trip to the mailing list when additional I/O effort is the cause of
the slowness.
 
The general consensus is that we want BUFFERS on by default with
ANALYZE.  However, there were more than zero concerns raised with doing
so.  The primary reason against is the additional verbosity, making it
harder to read large plans.  Another concern was that buffer information
isn't always useful so may not make sense to have it on by default.
 
It's currently December, so let's commit this to see if anyone comes
forward with a strong objection against making this change.  We have over
half a year remaining in the v18 cycle where we could still easily consider
reverting this if someone were to come forward with a convincing enough
reason as to why doing this is a bad idea.
 
There were two patches independently submitted to achieve this goal, one
by me and the other by Guillaume.  This commit is a mix of both of these
patches with some additional work done by me to adjust various
additional places in the documentation which include EXPLAIN ANALYZE
output.
 
Author: Guillaume Lelarge, David Rowley
Reviewed-by: Robert Haas, Greg Sabino Mullane, Michael Christofides
Discussion: https://postgr.es/m/CANNMO++W7MM8T0KyXN3ZheXXt-uLVM3aEtZd+WNfZ=obxffUiA@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Enable BUFFERS with EXPLAIN ANALYZE by default

Waiting for PostgreSQL 17 – Invent SERIALIZE option for EXPLAIN.

On 3rd of April 2024, Tom Lane committed patch:

Invent SERIALIZE option for EXPLAIN.
 
EXPLAIN (ANALYZE, SERIALIZE) allows collection of statistics about
the volume of data emitted by a query, as well as the time taken
to convert the data to the on-the-wire format.  Previously there
was no way to investigate this without actually sending the data
to the client, in which case network transmission costs might
swamp what you wanted to see.  In particular this feature allows
investigating the costs of de-TOASTing compressed or out-of-line
data during formatting.
 
Stepan Rutz and Matthias van de Meent,
reviewed by Tomas Vondra and myself
 
Discussion: https://postgr.es/m/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1@gmx.de

Continue reading Waiting for PostgreSQL 17 – Invent SERIALIZE option for EXPLAIN.

Display “settings” from plans on explain.depesz.com

Some time ago I wrote about new options for explains – one that prints settings that were modified from default.

This looks like this:

Aggregate  (cost=35.36..35.37 rows=1 width=8)
  ->  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.27..34.29 rows=429 width=0)
Settings: enable_seqscan = 'off'

Finally, today, I pushed a change that displays them on explain.depesz.com. To make it more usable, it will also provide links to documentation about each such options.

For example, check this plan.

Hope you'll find it useful.

Buffers I/O information on explain.depesz.com

I just released first version of change to explain.depesz.com that displays buffer I/O information, as described recently.

You can see it in here.

There are two new columns in there, showing how much data given node read from disk, and how much it wrote.

It's definitely not 100% OK now, as you can see, speed and time is not always there, but generally it should work.

There is also summarized info in stats page of the explain.

If you'd notice anything wrong, please let me know, either by mail, or just make an issue on GitLab.

Many changes on explain.depesz.com

Some time ago Eugen Konkov mailed me that he'd like to have some changes on explain.depesz.com.

One of the changes was actual bug, but the rest were improvements to functionality.

I kinda didn't want to do it, but when I looked closer it appeared to me that there are some subtle bugs, and when I'll be fixing them, I can add some of the things Eugen requested:

Continue reading Many changes on explain.depesz.com

Million explain plans…

Back in 2007 I wrote a simple script to add total time to explain analyze output.

It was very helpful, for me.

Then, around a year later figured that it could be useful for others, so wrote a simple site that got plans, and displayed them with extra info. It didn't look great.

Two years later I figured it would be good to make it look nicer. Asked a friend – Łukasz Lewandowski about it, and together we made new version, that was easier on eyes.

Since then there were no layout changes, just some new functionality: deleting plans, anonymizing/obfuscating them, user accounts, plan stats.

The site seemed to catch. In the first month (December of 2008) there were 391 plans added. Almost exactly 10 years later, in October 2018, we got 394 plans added, on average, each day.

Lately the average daily count of new plans (monthly average) is 400-550.

The best day was 21st of February 2019 where we got 5320 new plans. Most likely due to link to site being posted on some news aggregator or forum.

And, just yesterday, at around 4:30pm UTC, there was millionth plan pasted.

That is amazing and I would like to thank all of you – it really brightens my day when I see that people are using the site, and it (hopefully) helps them.

Changes on explain.depesz.com – extracted query from auto-explain plans

Some time ago James Courtney reported missing functionality.

Specifically, when one uses auto-explain, logged explains contain query text. So, when such explain is then pasted on explain.depesz.com, it stands to reason that it should be able to extract the query on its own, without having to manually extract it and put it in query box.

It took me a while, but finally, got it working today. And you can see it in all four explain format:

Also, while I'm writing – it seems that somewhere next month, there will be 1 millionth plan uploaded to the site 🙂 Hope you all find it useful 🙂