有没有办法监控Postgresql中索引创建的进度.我正在一个大型表上创建一个索引,我想看看它发生的速度有多快.
有没有办法监控这个？
根据
Postgres Wiki’s Index Maintenance页面,您可以找到所有索引的当前状态：
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将随着索引的构建而增长.
