PostgreSQL 10.0 preview 功能增强 - QUERY进度显示

标签

PostgreSQL , 10.0 , query进度

背景

query进度的显示，有一个插件pg_query_state支持。

PostgreSQL 10.0将在内核层面增加一个这样的支持。

首先会在analye命令上尝试，但是它会设计为通用的视图，支持其他命令，诸如CREATE INDEX, VAUUM, CLUSTER等。

Hello Hackers,  

Following is a proposal for reporting the progress of ANALYZE command:  

It seems that the following could be the phases of ANALYZE processing:
1. Collecting sample rows
2. Collecting inherited sample rows
3. Computing heap stats
4. Computing index stats
5. Cleaning up indexes  

The first phase is easy if there is no inheritance but in case of
inheritance we need to sample the blocks from multiple heaps.
Here the progress is counted against total number of blocks processed.  

The view provides the information of analyze command progress details as
follows
postgres=# \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    |           |          |
  heap_blks_total   | bigint  |           |          |
  heap_blks_scanned | bigint  |           |          |
  total_sample_rows | bigint  |           |          |  

I feel this view information may be useful in checking the progress of
long running ANALYZE command.  

The attached patch reports the different phases of analyze command.
Added this patch to CF 2017-03.  

Opinions?  

Note: Collecting inherited sample rows phase is not reported yet in the
patch.  

Regards,
Vinayak Pokale
NTT Open Source Software Center

这个patch的讨论，详见邮件组，本文末尾URL。

PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。

参考

《官人要杯咖啡吗? - PostgreSQL实时监测PLAN tree的执行进度 - pg_query_state》

https://commitfest.postgresql.org/13/1053/

https://www.postgresql.org/message-id/flat/c1691d3a-5b8f-ba1a-105a-3d3ef33f36ce@lab.ntt.co.jp#c1691d3a-5b8f-ba1a-105a-3d3ef33f36ce@lab.ntt.co.jp