PGSQL 查询哪些表要索引，查表行数

转自：(96条消息) PostgreSQL index monitor——监控哪些表需要创建索引_foucus、的博客-CSDN博客

在数据库的使用过程中，可能某些表随着数据量的增大而因为没有索引仍旧使用的全表扫描，我们可以使用下列脚本来监控哪些大表上需要创建索引。

1、监控哪些表需要创建索引

SELECT 
    relname AS TableName
    ,seq_scan-idx_scan AS TotalSeqScan
    ,CASE WHEN seq_scan-idx_scan > 0 
        THEN 'Missing Index Found' 
        ELSE 'Missing Index Not Found' 
    END AS MissingIndex
    ,pg_size_pretty(pg_relation_size(relname::regclass)) AS TableSize
    ,idx_scan AS TotalIndexScan
FROM pg_stat_all_tables
WHERE schemaname='public'
    AND pg_relation_size(relname::regclass)>1000000  --单位字节
ORDER BY 2 DESC;

2、监控索引大小及使用情况

使用下面脚本来查看索引的大小和索引扫描的行数等信息。

SELECT
    pt.tablename AS TableName
    ,t.indexname AS IndexName
    ,pc.reltuples AS TotalRows
    ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
    ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
    ,t.idx_scan AS TotalNumberOfScan
    ,t.idx_tup_read AS TotalTupleRead
    ,t.idx_tup_fetch AS TotalTupleFetched
FROM pg_tables AS pt
LEFT OUTER JOIN pg_class AS pc 
    ON pt.tablename=pc.relname
LEFT OUTER JOIN
( 
    SELECT 
        pc.relname AS TableName
        ,pc2.relname AS IndexName
        ,psai.idx_scan
        ,psai.idx_tup_read
        ,psai.idx_tup_fetch
        ,psai.indexrelname 
    FROM pg_index AS pi
    JOIN pg_class AS pc 
        ON pc.oid = pi.indrelid
    JOIN pg_class AS pc2 
        ON pc2.oid = pi.indexrelid
    JOIN pg_stat_all_indexes AS psai 
        ON pi.indexrelid = psai.indexrelid 
)AS T
    ON pt.tablename = T.TableName
WHERE pt.schemaname='public'
ORDER BY 1;

--估算表中行业
SELECT reltuples::bigint AS EstimatedCount
FROM   pg_class
WHERE  oid = 'public.TableName'::regclass;

--列出所有表中的行数

SELECT 
    schemaname
    ,relname
    ,n_live_tup AS EstimatedCount 
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;