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
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.
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
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 🙂
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.
Quite a lot of people have been bugging me about it, and finally got some time, and worked on it.
Long story short – it works.
You can add your query below plan (in separate text area), and then it will be displayed as one of tabs
For example, check this plan.
It also works sensibly-ish, if you want to obfuscate your plans, as you can see here.
In case you're interested in technical details …
Continue reading You can now add query to your plan on explain.depesz.com
Just pushed some new changes to expain.depesz.com and it's underlying parsing library.
The changes are:
- If you upload plan in JSON/YAML/XML format, and then you'll look at “TEXT" tab, you will see explain in text format, generated using data from JSON. This explain had slight error in number formatting (3 decimal digits for estimated costs, while text formats use only 2 decimals). THis was reported by ysch on irc. Example plan
- Numbers in node details (for example: Filtered, or Buffers info) are now commified to make it easier to read. Example plan
- Fixed parsing of Planning time in JSON, YAML, and XML formats of explains from PostgreSQL 13. Example plans:
JSON, YAML, and XML.
Some stats, for curious ones:
- site is up for over 11 years and 6 months
- so far there have been 843,215 plans added
- within last 30 days, there have been 484 new plans added daily, on average
- there are 1295 registered users, but only 780 of them ever added any plans
I just released new version of Pg::Explain Perl library that is handling parsing of plans for explain.depesz.com.
There are quite a lot of changes, but mostly internal, but one thing is pretty interesting – Pg::Explain, and because of this also explain.depesz.com should be able to parse plans with arbitrary values of border, linestyle, format, unicode_border_linestyle, unicode_column_linestyle, and unicode_header_linestyle psql options.
You can see five simple examples already uploaded:
Continue reading Changes on explain.depesz.com
Ever since I released explain.depesz.com over 11 years ago there have been cases were people would upload a plan and it didn't parse.
There were many reasons, but the most common was – plan was line-wrapped by injecting new-line characters where there shouldn't be one.
Continue reading Initial support for fixing of line-wrapped plans
Couple of days ago RhodiumToad reported, on irc, a bug in explain.depesz.com.
Specifically – if explain was done using JSON/XML/YAML formats, and node type was Aggregate, the site didn't extract full info.
Continue reading Fix for displaying aggregates on explain.depesz.com