Re: progress report for ANALYZE

Hi Alvaro and Amit!

On 2019/11/29 9:54, Tatsuro Yamada wrote:
> Hi Alvaro!
>
>>> Hmmm... I understand your opinion but I'd like to get more opinions too. :)
>>> Do you prefer these column names? See below:
>>
>> Here's my take on it:
>>
>>   <Columns of the view>
>>    pid
>>    datid
>>    datname
>>    relid
>>    phase
>>    sample_blks_total
>>    sample_blks_scanned
>>    ext_stats_total
>>    ext_stats_computed
>>    child_tables_total
>>    child_tables_done
>>    current_child_table_relid
>>
>> It seems to make sense to keep using the "child table" terminology in
>> that last column; but since the column carries an OID then as Robert
>> said it should have "relid" in the name.  For the other two "child
>> tables" columns, not using "relid" is appropriate because what they have
>> is not relids.
>>
>> I think there should be an obvious correspondence in columns that are
>> closely related, which there isn't if you use "sample" in one and "heap"
>> in the other, so I'd go for "sample" in both.
>
>
> Thanks for the comment.
> Oops, You are right, I overlooked they are not relids..
> I agreed with you and Amit's opinion so I'll send a revised patch on the next mail. :-)
>
> Next patch will be included:
>  - New columns definition of the view (as above)
>  - Renamed the phase name: s/acquiring inh sample rows/acquiring inherited sample rows/
>  - Update document

Attached patch is the revised patch. :)

I wonder two things below. What do you think?

1)
For now, I'm not sure it should be set current_child_table_relid to zero
when the current phase is changed from "acquiring inherited sample rows" to
"computing stats". See <Test result> bellow.

2)
There are many "finalizing analyze" phases based on relids in the case
of partitioning tables. Would it better to fix the document? or it
would be better to reduce it to one?

<Document>
---------------------------------------------------------
<entry><literal>finalizing analyze</literal></entry>
<entry>
The command is updating pg_class. When this phase is completed,
<command>ANALYZE</command> will end.
---------------------------------------------------------

<New columns of the view>
---------------------------------------------------------
# \d pg_stat_progress_analyze
View "pg_catalog.pg_stat_progress_analyze"
Column | Type | Collation | Nullable | Default
---------------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
sample_blks_total | bigint | | |
sample_blks_scanned | bigint | | |
ext_stats_total | bigint | | |
ext_stats_computed | bigint | | |
child_tables_total | bigint | | |
child_tables_done | bigint | | |
current_child_table_relid | oid | | |
---------------------------------------------------------

<Test result using partitioning tables>
---------------------------------------------------------
# select * from pg_stat_progress_analyze ; \watch 0.0001

19309|13583|postgres|36081|acquiring inherited sample rows|0|0|0|0|0|0|0
19309|13583|postgres|36081|acquiring inherited sample rows|45|17|0|0|4|0|36084
19309|13583|postgres|36081|acquiring inherited sample rows|45|35|0|0|4|0|36084
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|0|36084
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|0|36084
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|0|36084
19309|13583|postgres|36081|acquiring inherited sample rows|45|3|0|0|4|1|36087
19309|13583|postgres|36081|acquiring inherited sample rows|45|22|0|0|4|1|36087
19309|13583|postgres|36081|acquiring inherited sample rows|45|38|0|0|4|1|36087
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|1|36087
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|1|36087
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|1|36087
19309|13583|postgres|36081|acquiring inherited sample rows|45|16|0|0|4|2|36090
19309|13583|postgres|36081|acquiring inherited sample rows|45|34|0|0|4|2|36090
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|2|36090
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|2|36090
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|2|36090
19309|13583|postgres|36081|acquiring inherited sample rows|45|10|0|0|4|3|36093
19309|13583|postgres|36081|acquiring inherited sample rows|45|29|0|0|4|3|36093
19309|13583|postgres|36081|acquiring inherited sample rows|45|43|0|0|4|3|36093
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|3|36093
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|3|36093
19309|13583|postgres|36081|acquiring inherited sample rows|45|45|0|0|4|3|36093
19309|13583|postgres|36081|computing stats|45|45|0|0|4|4|36093 <== current_*_reid should be zero?
19309|13583|postgres|36081|computing stats|45|45|0|0|4|4|36093
19309|13583|postgres|36081|finalizing analyze|45|45|0|0|4|4|36093 <== there are many finalizing phases
19309|13583|postgres|36081|finalizing analyze|45|45|0|0|4|4|36093
19309|13583|postgres|36084|acquiring sample rows|45|3|0|0|0|0|0
19309|13583|postgres|36084|acquiring sample rows|45|33|0|0|0|0|0
19309|13583|postgres|36084|computing stats|45|45|0|0|0|0|0
19309|13583|postgres|36087|acquiring sample rows|45|15|0|0|0|0|0
19309|13583|postgres|36087|computing stats|45|45|0|0|0|0|0
19309|13583|postgres|36087|finalizing analyze|45|45|0|0|0|0|0 <== same as above
19309|13583|postgres|36090|acquiring sample rows|45|11|0|0|0|0|0
19309|13583|postgres|36090|acquiring sample rows|45|41|0|0|0|0|0
19309|13583|postgres|36090|finalizing analyze|45|45|0|0|0|0|0 <== same as above
19309|13583|postgres|36093|acquiring sample rows|45|7|0|0|0|0|0
19309|13583|postgres|36093|acquiring sample rows|45|37|0|0|0|0|0
19309|13583|postgres|36093|finalizing analyze|45|45|0|0|0|0|0 <== same as above
---------------------------------------------------------

Thanks,
Tatsuro Yamada