For very long time plans with parallel execution showed bogus values. Not any more.
Continue reading Finally – fixed time calculations for parallel plans in explain.depesz.com
Tag: explain
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:
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
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.
Changes on explain.depesz.com
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".
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 🙂
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.pgsqlAfterwards, 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
Waiting for 9.6 – Allow EXPLAIN (ANALYZE, VERBOSE) to display per-worker statistics.
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.
explain.depesz.com changes and new stats
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.
New functionality on explain.depesz.com, and some statistics
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