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
Continue reading Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.
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.
Bricklen Anderson reported a problem with explains that were using parallel processing.
TL;DR: in case of parallel plans make “inclusive" and “exclusive" be wall-clock times, and not “how much time CPU did spend on it".
Continue reading Changes 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
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:
though this commit does not use that code.)
Author: Tomas Vondra. Some code rework by Álvaro.
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
On 9th of December, Robert Haas committed patch:
Allow EXPLAIN (ANALYZE, VERBOSE) to display per-worker statistics.
The original parallel sequential scan commit included only very limited
changes to the EXPLAIN output. Aggregated totals from all workers were
displayed, but there was no way to see what each individual worker did
or to distinguish the effort made by the workers from the effort made by
Per a gripe by Thom Brown (and maybe others). Patch by me, reviewed
by Amit Kapila.
Continue reading Waiting for 9.6 – Allow EXPLAIN (ANALYZE, VERBOSE) to display per-worker statistics.
Some time ago I was contacted by Adam Smith – he pointed out that subquery names in “Subquery Scan" nodes were not properly anonymized.
Now, they are, which you can see in here:
While working on it, I also added (helpful?) links from node types to my blogposts about reading explain output – Explaining the unexplainable.
Continue reading explain.depesz.com changes and new stats
Some time ago Karl Bartel asked me to add ability to parse plans that were done using “ANALYZE ON, TIMING OFF". Initially I didn't see the point, but he said that explain.depesz.com allows him to hide parts of the tree, and other columns (aside from actual time) are extracted and presented in more readable way.
OK. Got his point, but was busy. Finally today committed:
Now – plans made using analyze without timing work nicely. In process also fixed display of nodes that never were executed.
And now time for some bragging, a.k.a. statistics:
Continue reading New functionality on explain.depesz.com, and some statistics
There are some cases where you might want to get your own copy of explain.depesz.com. You might not trust me with your explains. You might want to use it without internet access. Or you just want to play with it, and have total control over the site.
Installing, while obvious to me, and recently described by John Poole, is not always 100% clear. So, I decided to write about how to set it up, from scratch.
Continue reading How to install your own copy of explain.depesz.com
Uploaded new version to the server – straight from GitHub. There are two changes – one visible, and one not really.
The invisible change, first, is one for people hosting explain.depesz.com on their own. As you perhaps know you can get sources of explain.depesz.com and install it on any box you want (as log as you can get there PostgreSQL, Perl, and some perl modules). While working on it on my own, I figured I could use a way to tell which version of module-xxx the site is running right now. So I build /info page (which is inaccessible to everyone, but manually-marked admins), which lists versions and interesting paths.
The second change – the one visible to users, is that I made explain.depesz.com commify numbers. Sometimes it can be hard to read value like 12325563, but now it will be displayed as 12,325,563 making is simpler to grasp.
This second change was suggested by Jacek Wielemborek, so if you hate it – blame him. Of course if you love the change – it's all on me 🙂
Hope you'll find it helpful.