New changes on explain.depesz.com – fixed calculations of exclusive times

When you see plan on explain.depesz.com, one of the columns visible is exclusive.

In principle it's simple – take time of current node, subtract times of all sub nodes, and you get how much time was spent in this node alone.

Reality is not that simple. Two things especially make the calculations complicated. CTEs, and InitPlans.

Now, with the just pushed change to Pg::Explain (plan parsing library), and site itself, some of the bad calculations are gone.

Let's see some examples:

Continue reading New changes on explain.depesz.com – fixed calculations of exclusive times

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 🙂

Fixed display of Backward scans on explain.depesz.com

Yaroslav Schekin (ysch) reported on irc that Index Scans Backward do not display properly.

After checking I found out that if explain is in JSON/YAML/XML – node type is changed to “Index Scan" (or “Index Only Scan" if it was originally “Index Only Scan Backward").

Continue reading Fixed display of Backward scans on explain.depesz.com

explain.depesz.com – now with JITs

Ever since PostgreSQL 11 we have JIT (Just-In-Time compilation).

Information about JIT is displayed in explain analyze plans, but up to this moment, explain.depesz.com didn't display it properly. But not anymore. Thanks to release 1.05 of Pg::Explain and subsequent change in templates for the site, you can now see nice JIT info.

Hope you'll find it useful 🙂

New functionality on explain.depesz.com

Just committed some new changes to explain.depesz.com.

The less visible is that, in case of plans generated by plain EXPLAIN (without ANALYZE), site will no longer show “actual time=.. rows= loops=", which was clearly bogus info.

The more visible is that if you add query to your plan, you can also have it reformatted to (hopefully) nicer format, shown in “REFORMATTED QUERY" tab. For example, take a look at this example.

Reformatting is done using pgFormatter library by Gilles Darold.

I picked this library for the very simple reason – it's in Perl, just like explain.depesz.com site, which made using it a breeze.