Tag: perl

You can now add query to your plan on explain.depesz.com

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

Changes 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

Changes on explain.depesz.com

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

Fix for displaying aggregates on explain.depesz.com

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

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.