接上期,postgresql 的system catalog 中包含了不少系统表,
pg_lock
pg_stat_user_tables 这个表是系统中收集用户表信息的VIEW ,通过这张表可以得到用户表被访问的信息.
其中包含, 这张表从建立以来被全表扫描的次数, seq_scan 还有idx_scan s索引扫描的次数,插入,更改,删除的数据的次数 n_tup_ins, n_tup_upd, n_tup_del, n_live_tup 当前活跃的行和 n_dead_tup 死行的个数,另外还包含最后一次vacuum , 以及autovacuum 的日期,autovacuum_count的次数,分析的次数等等,对于这张表来说可以获得的信息非常多,可以全方位的对系统的表进行了解.
pg_statio_all_tables 通过pg_statio_all_tables 表可以获得丰富的数据,如
heap_blks_read 读取磁盘的数量
heap_blks_hit 从内存中读取的数据量
两个数据进行对比可以找到一个表从磁盘中读取的数据量和内存的数据量之间的比值,可以发现表到底缺少不缺少索引的可能,或者内存不足的可能性
idx_blks_read idx_blks_hit 两个值可以比对索引从磁盘或者内存中或许的次数,两个相关的两个比值对比可以得到,索引命中率的比率,看看内存方面是否有问题.
pg_index
查看当前数据库的索引信息, 通过 pg_index 来进行数据库表的索引的查看大小和
select
t.schemaname,
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(indexrelname)::text)) AS index_size,
CASE WHEN indisunique THEN 'Y'
ELSE 'N'
END AS UNIQUE,
number_of_scans,
tuples_read,
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 AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched,
indexrelname,
indisunique,
schemaname
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 AND t.schemaname = foo.schemaname
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1,2;
查询重复索引的脚本
代码语言：javascript复制SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
(array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
(array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
FROM (
SELECT indexrelid::regclass as idx, (indrelid::text ||E'n'|| indclass::text ||E'n'|| indkey::text ||E'n'||
coalesce(indexprs::text,'')||E'n' || coalesce(indpred::text,'')) as key
FROM pg_index) sub
GROUP BY key HAVING count(*)>1
ORDER BY sum(pg_relation_size(idx)) DESC;
pg_stat_all_indexes 这个表是展示postgresql 的表的索引的状态.查询无用的索引的
代码语言：javascript复制SELECT
relname,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelname::regclass)) as size
FROM
pg_stat_all_indexes
WHERE
schemaname = 'public'
AND indexrelname NOT LIKE 'pg_toast_%'
AND idx_scan = 0
AND idx_tup_read = 0
AND idx_tup_fetch = 0
ORDER BY
pg_relation_size(indexrelname::regclass) DESC;
利用函数来对postgresql 数据库进行表的尺寸的统计
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
查看某个表空间的使用空间的大小
select pg_tablespace_size('pg_default')/1024/1024 as "SIZE M";
