Re: progress report for ANALYZE - Mailing list pgsql-hackers

Hi Amit-san,


>>> 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.
>>
>> In the upthread discussion [1], Robert asked to *not* do such things,
>> that is, resetting some values due to phase change.  I'm not sure his
>> point applies to this case too though.
> 
> Yeah, I understood.
> I'll check target relid of "computing stats" to re-read a code of
> analyze command later. :)


Finally, I understood after investigation of the code. :)
Call stack is the following, and analyze_rel() calls "N + 1" times
for partitioned table and each partitions.

analyze_rel start
  do_analyze_rel inh==true start
   onerel: hoge2
    acq_inh_sample_rows start
     childrel: hoge2_10000
     childrel: hoge2_20000
     childrel: hoge2_30000
     childrel: hoge2_default
    acq_inh_sample_rows end
    compute_stats start
    compute_stats end
    compute_index_stats start
    compute_index_stats end
    finalizing start
    finalizing end
  do_analyze_rel inh==true end
analyze_rel end
...


Also, I checked my test result. ("//" is my comments)


# select oid,relname,relkind from pg_class where relname like 'hoge2%';
   oid  |    relname    | relkind
-------+---------------+---------
  36081 | hoge2         | p
  36084 | hoge2_10000   | r
  36087 | hoge2_20000   | r
  36090 | hoge2_30000   | r
  36093 | hoge2_default | r
(6 rows)

# select relid,
          current_child_table_relid,
          phase,
          sample_blks_total,
          sample_blks_scanned,
          ext_stats_total,
          ext_stats_computed,
          child_tables_total,
          child_tables_done
   from pg_stat_progress_analyze; \watch 0.00001

== for partitioned table hoge2 ==
//hoge2_10000
36081|36084|acquiring inherited sample rows|45|20|0|0|4|0
36081|36084|acquiring inherited sample rows|45|42|0|0|4|0
36081|36084|acquiring inherited sample rows|45|45|0|0|4|0
36081|36084|acquiring inherited sample rows|45|45|0|0|4|0

//hoge2_20000
36081|36087|acquiring inherited sample rows|45|3|0|0|4|1
36081|36087|acquiring inherited sample rows|45|31|0|0|4|1
36081|36087|acquiring inherited sample rows|45|45|0|0|4|1
36081|36087|acquiring inherited sample rows|45|45|0|0|4|1

//hoge2_30000
36081|36090|acquiring inherited sample rows|45|12|0|0|4|2
36081|36090|acquiring inherited sample rows|45|35|0|0|4|2
36081|36090|acquiring inherited sample rows|45|45|0|0|4|2
36081|36090|acquiring inherited sample rows|45|45|0|0|4|2

//hoge2_default
36081|36093|acquiring inherited sample rows|45|18|0|0|4|3
36081|36093|acquiring inherited sample rows|45|38|0|0|4|3
36081|36093|acquiring inherited sample rows|45|45|0|0|4|3
36081|36093|acquiring inherited sample rows|45|45|0|0|4|3

//Below "computing stats" is for the partitioned table hoge,
//therefore the second column from the left side would be
//better to set Zero to easy to understand.
//I guessd that user think which relid is the target of
//"computing stats"?!
//Of course, other option is to write it on document.

36081|36093|computing stats     |45|45|0|0|4|4
36081|36093|computing stats     |45|45|0|0|4|4
36081|36093|computing stats     |45|45|0|0|4|4
36081|36093|computing stats     |45|45|0|0|4|4
36081|36093|finalizing analyze  |45|45|0|0|4|4

== for each partitions such as hoge2_10000 ... hoge2_default ==

//hoge2_10000
36084|0|acquiring sample rows   |45|25|0|0|0|0
36084|0|computing stats         |45|45|0|0|0|0
36084|0|computing extended stats|45|45|0|0|0|0
36084|0|finalizing analyze      |45|45|0|0|0|0

//hoge2_20000
36087|0|acquiring sample rows   |45|14|0|0|0|0
36087|0|computing stats         |45|45|0|0|0|0
36087|0|computing extended stats|45|45|0|0|0|0
36087|0|finalizing analyze      |45|45|0|0|0|0

//hoge2_30000
36090|0|acquiring sample rows   |45|12|0|0|0|0
36090|0|acquiring sample rows   |45|44|0|0|0|0
36090|0|computing extended stats|45|45|0|0|0|0
36090|0|finalizing analyze      |45|45|0|0|0|0

//hoge2_default
36093|0|acquiring sample rows   |45|10|0|0|0|0
36093|0|acquiring sample rows   |45|43|0|0|0|0
36093|0|computing extended stats|45|45|0|0|0|0
36093|0|finalizing analyze      |45|45|0|0|0|0



>>> 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.
>>> ---------------------------------------------------------
>>
>> When a partitioned table is analyzed, its partitions are analyzed too.
>> So, the ANALYZE command effectively runs N + 1 times if there are N
>> partitions -- first analyze partitioned table to collect "inherited"
>> statistics by collecting row samples using
>> acquire_inherited_sample_rows(), then each partition to collect its
>> own statistics.  Note that this recursive application to ANALYZE to
>> partitions (child tables) only occurs for partitioned tables, not for
>> legacy inheritance.
> 
> Thanks for your explanation.
> I understand Analyzing Partitioned table a little.


It would be better to modify the document of "finalizing analyze" phase.

   # Before modify
    The command is updating pg_class. When this phase is completed,
    <command>ANALYZE</command> will end.

   # Modified
    The command is updating pg_class. When this phase is completed,
    <command>ANALYZE</command> will end. In the case of partitioned table,
    it might be shown on each partitions.

What do you think that? I'm going to fix it, if you agreed. :)

Thanks,
Tatsuro Yamada