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
On 6th of April 2020, Amit Kapila committed patch:
Add the option to report WAL usage in EXPLAIN and auto_explain.
This commit adds a new option WAL similar to existing option BUFFERS in the
EXPLAIN command. This option allows to include information on WAL record
generation added by commit df3b181499 in EXPLAIN output.
This also allows the WAL usage information to be displayed via
the auto_explain module. A new parameter auto_explain.log_wal controls
whether WAL usage statistics are printed when an execution plan is logged.
This parameter has no effect unless auto_explain.log_analyze is enabled.
Author: Julien Rouhaud
Reviewed-by: Dilip Kumar and Amit Kapila
Continue reading Waiting for PostgreSQL 13 – Add the option to report WAL usage in EXPLAIN and auto_explain.
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
Recently got two bug reports:
- plans with “COSTS OFF" do not parse, and error out (bugreport by Marc Dean Jr)
- WorkTable Scan is not properly parsed (bugreport by Ivan Vergiliev)
Additionally, I was kinda upset because plans that include trigger calls did not display properly.
All of this has been fixed today:
Continue reading Changes on explain.depesz.com
On 3rd of April 2019, Tomas Vondra committed patch:
Add SETTINGS option to EXPLAIN, to print modified settings.
Query planning is affected by a number of configuration options, and it
may be crucial to know which of those options were set to non-default
values. With this patch you can say EXPLAIN (SETTINGS ON) to include
that information in the query plan. Only options affecting planning,
with values different from the built-in default are printed.
This patch also adds auto_explain.log_settings option, providing the
same capability in auto_explain module.
Author: Tomas Vondra
Continue reading Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.
On Friday, Zr40 reported on irc that explain.depesz.com doesn't show table/index names for Parallel Seq Scan nodes.
Checked it and found couple of other omissions of the same kind with other Parallel* scans.
Fixed (I hope) all of them in:
The change is not really big, but just figured I'll let you know.
Bricklen Anderson reported a problem with explains that were using parallel processing.
TL;DR: in case of parallel plans make “inclusive" and “exclusive" be wall-clock times, and not “how much time CPU did spend on it".
Continue reading Changes on explain.depesz.com