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
Just now pushed new version of Pg::Explain Perl library that is handling parsing for explain.depesz.com.
There have been many changes, but the short summary is:
- Fix display of heap fetches from json (https://gitlab.com/depesz/explain.depesz.com/issues/15)
- Move global data (planning/execution/total time, trigger info) from top_node to explain object itself
- Add method to easily get real total, wall-clock, runtime of query.
- Add Pg::Explain::Analyzer, so far it can extract node types and their “paths"
- Add scripts to run perltidy, start development tmux env, and explain schema dumper
- Fix handling of parallel queries (hopefully)
- Remove edge-case deep recursion error
- Speed optimization for large explains
Hopefully it will not break anything for you 🙂
And as a final note: explain.depesz.com has been used to add almost 740kplans (out of which 120k were deleted). Currently database contains over 311k public (visible in history), not deleted, 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
Some time ago I wrote a site to paste SQL queries with reformatting/pretty-printing using pgFormatter library.
Today, I figured out that I should update the library since it has quite some changes recently, so it would be good to incorporate its fixes to paste site.
Unfortunately – new version is not backward compatible, and I currently have no time to figure out what has changed and how to work around it.
So – until I will have time to work on it, paste.depesz.com is no longer working. Sorry.
Couple of times, in various places, I was asked: what is the benefit from upgrading to some_version.
So far, I just read release docs, and compiled list of what has changed.
But this is not necessarily simple – consider upgrade from 9.3.2 to 10.2. That's a lot of changes.
So, to be able to answer these questions faster in future, I created a site: Why upgrade PostgreSQL?.
Usage should be simple – pick from which version you want to upgrade, to which version you want to upgrade, and press gives me… button.
Hope you'll find it useful.