New Pg::Explain and 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.

Changes on explain.depesz.com

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

paste.depesz.com is no more

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.

What is the benefit of upgrading PostgreSQL?

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.

Fix for parallel scans on explain.depesz.com

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.

A way to share SQL queries – paste.depesz.com

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…

Getting list of unique elements in table, per group

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?

Continue reading Getting list of unique elements in table, per group

explain.depesz.com – new change and some stats

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.

Continue reading explain.depesz.com – new change and some stats

Fixed a bug in OmniPITR

Just thought I'll share a “fun" story. Friend reported weird bug – OmniPITR reported that xlogs are sent to archive, but they actually weren't.

After some checking we found out that he was giving custom rsync-path (–rsync-path – path to rsync program) – and the path was broken.

In this case – OmniPITR was not reporting error, and quite happily was working under assumption that it works OK.

Continue reading Fixed a bug in OmniPITR