Thread: Summary Sort workers Stats in EXPLAIN ANALYZE

Hi,

On Thu, Mar 24, 2022 at 07:50:11AM +0000, Jian Guo wrote:
> For a simple demo, with this explain statement：
>
> -- Test sort stats summary
> set force_parallel_mode=on;
> select explain_filter('explain (analyze, summary off, timing off, costs off, format json) select * from tenk1 order
byunique1');
 
>
> Before this patch, we got plan like this:
>
>
>            "Node Type": "Sort",                +
>            "Parent Relationship": "Outer",     +
>            "Parallel Aware": false,            +
>            "Async Capable": false,             +
>            "Actual Rows": 10000,               +
>            "Actual Loops": 1,                  +
>            "Sort Key": ["unique1"],            +
>            "Workers": [                        +
>              {                                 +
>                "Worker Number": 0,             +
>                "Sort Method": "external merge",+
>                "Sort Space Used": 2496,        +
>                "Sort Space Type": "Disk"       +
>              }                                 +
>            ],                                  +

> After this patch, the effected plan is this:
>
>            "Node Type": "Sort",               +
>            "Parent Relationship": "Outer",    +
>            "Parallel Aware": false,           +
>            "Async Capable": false,            +
>            "Actual Rows": N,                  +
>            "Actual Loops": N,                 +
>            "Sort Key": ["unique1"],           +
>            "Workers planned": N,              +
>            "Sort Method": "external merge",   +
>            "Average Sort Space Used": N,      +
>            "Peak Sort Space Used": N,         +
>            "Sort Space Type": "Disk",         +

I think the idea is interesting, however there are a few problems in the patch.

First, I think that it should only be done in the VERBOSE OFF mode.  If you ask
for a VERBOSE output you don't need both the details and the summarized
version.

Other minor problems:

- why (only) emitting the number of workers planned and not the number of
  workers launched?
- the textual format is missing details about what the numbers are, which is
  particularly obvious since avgSpaceUsed and peakSpaceUsed don't have any unit
  or even space between them:

+             "Sort Method: %s  %s: " INT64_FORMAT INT64_FORMAT "kB\n",
+             sortMethod, spaceType, avgSpaceUsed, peakSpaceUsed);

On Fri, Mar 25, 2022 at 05:04:53PM +0800, Julien Rouhaud wrote:
> I think the idea is interesting, however there are a few problems in the patch.
>
> First, I think that it should only be done in the VERBOSE OFF mode.  If you ask
> for a VERBOSE output you don't need both the details and the summarized
> version.
>
> Other minor problems:
>
> - why (only) emitting the number of workers planned and not the number of
>   workers launched?
> - the textual format is missing details about what the numbers are, which is
>   particularly obvious since avgSpaceUsed and peakSpaceUsed don't have any unit
>   or even space between them:
>
> +             "Sort Method: %s  %s: " INT64_FORMAT INT64_FORMAT "kB\n",
> +             sortMethod, spaceType, avgSpaceUsed, peakSpaceUsed);

Also I didn't find your patch in the next commitfest [1].  Please register it
to make sure that it's not forgotten.  Not that we're already at the end of the
last pg15 commitfest, so this should be material for pg16.

[1] https://commitfest.postgresql.org/38/

On Tue, Sep 06, 2022 at 11:37:32AM +0500, Ibrar Ahmed wrote:
> The patch failed different regression tests on all platforms. Please
> correct that and send an updated patch.
>
> [06:40:02.370] Test Summary Report
> [06:40:02.370] -------------------
> [06:40:02.370] t/002_pg_upgrade.pl (Wstat: 256 Tests: 13 Failed: 1)
> [06:40:02.370] Failed test: 4
> [06:40:02.370] Non-zero exit status: 1
> [06:40:02.370] Files=2, Tests=21, 45 wallclock secs ( 0.02 usr 0.00 sys +
> 3.52 cusr 2.06 csys = 5.60 CPU)

This has been marked as RwF based on the lack of an update.
--
Michael