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

Waiting for PostgreSQL 13 – Add the option to report WAL usage in EXPLAIN and auto_explain.

On 6th of April 2020, Amit Kapila committed patch:

Add the option to report WAL usage in EXPLAIN and auto_explain.
 
This commit adds a new option WAL similar to existing option BUFFERS in the
EXPLAIN command.  This option allows to include information on WAL record
generation added by commit df3b181499 in EXPLAIN output.
 
This also allows the WAL usage information to be displayed via
the auto_explain module.  A new parameter auto_explain.log_wal controls
whether WAL usage statistics are printed when an execution plan is logged.
This parameter has no effect unless auto_explain.log_analyze is enabled.
 
Author: Julien Rouhaud
Reviewed-by: Dilip Kumar and Amit Kapila
Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Add the option to report WAL usage in EXPLAIN and auto_explain.

Initial support for fixing of line-wrapped plans

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

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

Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.

On 3rd of April 2019, Tomas Vondra committed patch:

Add SETTINGS option to EXPLAIN, to print modified settings.
 
 
Query planning is affected by a number of configuration options, and it
may be crucial to know which of those options were set to non-default
values.  With this patch you can say EXPLAIN (SETTINGS ON) to include
that information in the query plan.  Only options affecting planning,
with values different from the built-in default are printed.
 
This patch also adds auto_explain.log_settings option, providing the
same capability in auto_explain module.
 
Author: Tomas Vondra
 
Discussion: https://postgr.es/m/e1791b4c-df9c-be02-edc5-7c8874944be0@2ndquadrant.com

Continue reading Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.

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.

New change on explain.depesz.com

Some (long) time ago, someone on irc suggested that I add option to keep track of optimizations of queries.

Sorry, I forgot your name, and the mails disappeared in some crash.

Anyway – right now, when you are on some plan page, you can press “Add optimization" button, and you will be redirected to index page, but when you will add plan there, it will be understood to be plan from optimization of the query. Like this one.

You can have any number of optimizations per plan, and when viewing plan that has optimizations, or is an optimization of earlier plan – you will see this above plan table.

Whether you'll use it – it's up to you. Someone wanted it, and it looked like sensible thing to add, so there it is 🙂

Continue reading New change on explain.depesz.com

Waiting for PostgreSQL 10 – Implement multivariate n-distinct coefficients

I missed it completely, but on 24th of March 2017, Alvaro Herrera committed patch:

Implement multivariate n-distinct coefficients
 
 
Add support for explicitly declared statistic objects (CREATE
STATISTICS), allowing collection of statistics on more complex
combinations that individual table columns.  Companion commands DROP
STATISTICS and ALTER STATISTICS ... OWNER TO / SET SCHEMA / RENAME are
added too.  All this DDL has been designed so that more statistic types
can be added later on, such as multivariate most-common-values and
multivariate histograms between columns of a single table, leaving room
for permitting columns on multiple tables, too, as well as expressions.
 
This commit only adds support for collection of n-distinct coefficient
on user-specified sets of columns in a single table.  This is useful to
estimate number of distinct groups in GROUP BY and DISTINCT clauses;
estimation errors there can cause over-allocation of memory in hashed
aggregates, for instance, so it's a worthwhile problem to solve.  A new
special pseudo-type pg_ndistinct is used.
 
(num-distinct estimation was deemed sufficiently useful by itself that
this is worthwhile even if no further statistic types are added
immediately; so much so that another version of essentially the same
functionality was submitted by Kyotaro Horiguchi:
https://postgr.es/m/.173334..horiguchi.kyotaro@lab.ntt.co.jp
though this commit does not use that code.)
 
Author: Tomas Vondra.  Some code rework by Álvaro.
 
    Ideriha Takeshi
Discussion: https://postgr.es/m/.4080608@fuzzy.cz
    https://postgr.es/m/.ixlaueanxegqd5gr@alvherre.pgsql

Afterwards, there were couple more commits related to it:

  • On 5th of April 2017, patch committed by Simon Riggs
  • On 17th of April 2017, patch committed by Alvaro Herrera
  • On 12nd of May 2017, patch committed by Alvaro Herrera

Continue reading Waiting for PostgreSQL 10 – Implement multivariate n-distinct coefficients