有没有一种方法可以监视PostgreSQL中索引创建的进度。我正在大表上创建索引，我想看看这种情况发生的速度如何。

评论

如果使用的是psql，则应尝试\ timing

#1 楼

根据Postgres Wiki的“索引维护”页面，您可以使用以下信息找出所有索引的当前状态：

SELECT
  t.tablename,
  indexname,
  c.reltuples AS num_rows,
  pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
  pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
  CASE WHEN indisunique THEN 'Y'
    ELSE 'N'
  END AS UNIQUE,
  idx_scan AS number_of_scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_tables t
  LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
  LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
      JOIN pg_class c ON c.oid = x.indrelid
      JOIN pg_class ipg ON ipg.oid = x.indexrelid
      JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
  ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;


num_rows列指示索引覆盖了多少行并且index_size将随着索引的建立而增长。

评论

我怀疑这可能不适用于新索引，除非提交创建索引的事务提交，否则该索引可能在目录中不可见。

–必须
17年1月23日在19:35
@mustaccio你是正确的。我正在创建一个需要很长时间的索引，并且上面的命令仅显示已经创建的索引；它不会显示仍在进行“ CREATE INDEX”的索引。

–orange80
17年4月15日在7:21
REINDEX TABLE阻止此查询。至少，当我在9.6上运行时，它确实如此。

–罗恩·约翰（RonJohn）
19年5月5日在21:03
这不是一个非常有用的查询。它既不会告诉您当前正在重建索引还是需要重建索引。它只是告诉您拥有多少索引，而这并不是Op所要求的。

–塞林
20 May 22 '14：34
在将备份还原到第二个实例并比较两个表时，这对我来说非常理想。

– vallismortis
20年8月21日在14:39

#2 楼

自PostgreSQL 12版本（于2019年10月3日发布）以来，这是可能的。
SELECT 
  now()::TIME(0), 
  a.query, 
  p.phase, 
  p.blocks_total, 
  p.blocks_done, 
  p.tuples_total, 
  p.tuples_done
FROM pg_stat_progress_create_index p 
JOIN pg_stat_activity a ON p.pid = a.pid;

有关详细信息，请参阅pg_stat_progress_create_index视图的文档和depesz的博客文章。

#3 楼

因此，没有很好的方法来执行此操作，但是如果您真的需要知道...首先根据数据大小*行+开销计算索引应占用的空间量。然后，您可以使用pfiles或pgtruss之类的东西来查找正在$ PGDATA内写入的文件。如果您的索引超过1GB，它将是一系列文件，例如nnnnn.n，其中第一个n集是一致的，每个GB文件的后n个增量。一旦知道创建了多少文件，就可以观察增长情况，并确定完成工作的距离。粗略估计，但这也许有所帮助。

#4 楼

不，即使您以CONCURRENT模式构建它，也没有。尽管过去我一直关注数据库目录中文件的大小，但这并不是真正有用的，因为您只能猜测它的大小。