explain.depesz.com – now with HINTS!

I just pushed change to explain.depesz.com that allows for processing and displaying hints for plans.

For example, take a look at this plan, and check if you'll notice subtle “HINTS" tab.

In there you will see example hints – one about sort and memory, and the other about missing index.

It is not much, but it's a step in (hopefully) right direction, when the explain tool will also provide, automatically, some ideas on what to do to make the thing faster.

Hope you'll find it useful.

Configuration changes across Pg versions

Back in 2018 I wrote why-upgrade.depesz.com – aggregator of changelogs between various versions of Pg.

Want to know what you will get when upgrading from 12.1 to 12.9? Here you go. Longer time changes? Like, from 9.5.20 to 14.1? I've got your back.

It even has a way to list every change that relates to anything related to indexes.

Today I updated the code, again to show which GUCs (configuration parameters) have changed between given versions. For example, getting diff from 13.5 to 14.1 shows you that:

  • two parameters were removed, and are no longer there
  • there are 17 new parameters
  • three parameters had their default values changed

And each GUC that is listed is (well, is supposed to but in some cases it can't) linked to relevant part of docs that describe what it is.

To make it work I compiled every version of Pg, since 7.2 (there have been 410 of them!), and extracted list of config params, and their default values.

Then, I fetched docs for all major versions of Pg, and extracted list of documentation fragments that relate to each config parameter.

This will require more work on each subsequent release, but I think I can manage it.

Any way – hope you'll find it helpful.

New changes on explain.depesz.com – fixed calculations of exclusive times

When you see plan on explain.depesz.com, one of the columns visible is exclusive.

In principle it's simple – take time of current node, subtract times of all sub nodes, and you get how much time was spent in this node alone.

Reality is not that simple. Two things especially make the calculations complicated. CTEs, and InitPlans.

Now, with the just pushed change to Pg::Explain (plan parsing library), and site itself, some of the bad calculations are gone.

Let's see some examples:

Continue reading New changes on explain.depesz.com – fixed calculations of exclusive times

Display “settings” from plans on explain.depesz.com

Some time ago I wrote about new options for explains – one that prints settings that were modified from default.

This looks like this:

Aggregate  (cost=35.36..35.37 rows=1 width=8)
  ->  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.27..34.29 rows=429 width=0)
Settings: enable_seqscan = 'off'

Finally, today, I pushed a change that displays them on explain.depesz.com. To make it more usable, it will also provide links to documentation about each such options.

For example, check this plan.

Hope you'll find it useful.

explain.depesz.com – now with JITs

Ever since PostgreSQL 11 we have JIT (Just-In-Time compilation).

Information about JIT is displayed in explain analyze plans, but up to this moment, explain.depesz.com didn't display it properly. But not anymore. Thanks to release 1.05 of Pg::Explain and subsequent change in templates for the site, you can now see nice JIT info.

Hope you'll find it useful 🙂

New functionality on explain.depesz.com

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.

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