Re: [PATCH] Add extra statistics to explain for Nested Loop - Mailing list pgsql-hackers

Thank you all for your feedback and reforms.
I attach a new version of the patch with the some changes and fixes. 
Here's a list of the major changes:
1) New format of extra statistics. This is now contained in a line 
separate from the main statistics.

Julien Rouhaud писал 2021-02-01 08:28:
> On Thu, Jan 28, 2021 at 8:38 PM Yugo NAGATA <nagata@sraoss.co.jp> 
> wrote:
>> 
>> postgres=# explain (analyze, verbose) select * from a,b where a.i=b.j;
>>                                                                        
>>          QUERY PLAN
>>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>  Nested Loop  (cost=0.00..2752.00 rows=991 width=8) (actual 
>> time=0.021..17.651 rows=991 loops=1)
>>    Output: a.i, b.j
>>    Join Filter: (a.i = b.j)
>>    Rows Removed by Join Filter: 99009
>>    ->  Seq Scan on public.b  (cost=0.00..2.00 rows=100 width=4) 
>> (actual time=0.009..0.023 rows=100 loops=1)
>>          Output: b.j
>>    ->  Seq Scan on public.a  (cost=0.00..15.00 rows=1000 width=4) 
>> (actual time=0.005..0.091 min_time=0.065 max_time=0.163 min_rows=1000 
>> rows=1000 max_rows=1000 loops=100)
>>          Output: a.i
>>  Planning Time: 0.066 ms
>>  Execution Time: 17.719 ms
>> (10 rows)
>> 
>> I don't like this format where the extra statistics appear in the same
>> line of existing information because the output format differs 
>> depended
>> on whether the plan node's loops > 1 or not. This makes the length of 
>> a
>> line too long. Also, other information reported by VERBOSE doesn't 
>> change
>> the exiting row format and just add extra rows for new information.
>> 
>> Instead, it seems good for me to add extra rows for the new statistics
>> without changint the existing row format as other VERBOSE information,
>> like below.
>> 
>>    ->  Seq Scan on public.a  (cost=0.00..15.00 rows=1000 width=4) 
>> (actual time=0.005..0.091 rows=1000  loops=100)
>>          Output: a.i
>>          Loops: min_time=0.065 max_time=0.163 min_rows=1000 
>> max_rows=1000
>> 
>> and so  on. What do you think about it?
> 

2) Correction of the case of parallel scan

>> In parallel scan, the extra statistics are not reported correctly.
>> 
>> This reports max/min rows or time of inner scan as 0 in parallel 
>> workers,
>> and as a result only the leader process's ones are accounted. To fix 
>> this,
>> we would change InstrAggNode as below.
>> 
> 

3) Adding extra statistics about total number of rows (total rows). 
There were many wishes for this here.

Please don't hesitate to share any thoughts on this topic.

-- 
Ekaterina Sokolova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company