Postgresql system Catalog 中的系统表能告诉你什么 （二）？

接上期,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;

查询重复索引的脚本

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 的表的索引的状态.查询无用的索引的

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";