There were many reasons, but the most common was – plan was line-wrapped by injecting new-line characters where there shouldn't be one.
For very long time plans with parallel execution showed bogus values. Not any more.
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:
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://email@example.com
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".
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 🙂
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..firstname.lastname@example.org though this commit does not use that code.) Author: Tomas Vondra. Some code rework by Álvaro. Ideriha Takeshi Discussion: https://email@example.com https://firstname.lastname@example.org
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
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 the leader. Per a gripe by Thom Brown (and maybe others). Patch by me, reviewed by Amit Kapila.
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.