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