There were many reasons, but the most common was – plan was line-wrapped by injecting new-line characters where there shouldn't be one.
Specifically – if explain was done using JSON/XML/YAML formats, and node type was Aggregate, the site didn't extract full info.
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 🙂
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:
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.
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.
Long time ago I wrote first version of explain.depesz.com. Since then I gradually improve it. But, what was lacking was a way to paste queries too – explain.depesz.com handles explains, but not plain queries.
Now this has changed. I created new site: paste.depesz.com which allows for sharing queries.
Thanks to pgFormatter it also does query pretty-printing (which is not something readily available on other paste sites).
Obviously, code to the site is publicly available in GitHub repo.
Now, goes my request – if you have designer skills, I would greatly appreciate someone that could make the site nicer (prettier, more responsive). My CSS/JS knowledge is pretty limited, and I'm happy anyway about what I did with the look right now, but if you could make it nicer/prettier, that would be amazing.
Have fun, and if you have any feature requests, please post them in here…
Today, on irc, someone asked interesting question.
Basically she ran a query like:
SELECT a, b, c, d, e, f FROM TABLE ORDER BY a
then, she processed the query to get, for each a array of unique values of b, c, d, e, and f, and then he inserted it back to database, to some other table.
It was a problem, because the table had many rows (millions I would assume), and the whole process was slow.
So, how to make it faster?
Quite a long time ago (in October), Oskar Liljeblad reported a bug in anonymization. Namely – group keys were not anonymized.
You can see example of such plan here.
I finally got to it, fixed the bug, pushed new version to live site, and now such plans will be correctly anonymized.
Thanks Oskar, and sorry for long delay.