1、基础查询（全局所有数据库）

-- 基础查询（全局所有数据库）
SELECT 
    datname AS 数据库名称,
    blks_hit,
    blks_read,
    CASE 
        WHEN (blks_hit + blks_read) = 0 THEN 0
        ELSE ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2)
    END AS 缓存命中率百分比
FROM 
    pg_stat_database
WHERE 
    datname NOT IN ('template0', 'template1', 'postgres')  -- 排除系统数据库
    AND blks_hit + blks_read > 0
ORDER BY 
    缓存命中率百分比 ASC;  -- 从低到高排序，找出问题数据库
    
 ------------+----------+-----------+------------------   
 数据库名称 | blks_hit | blks_read | 缓存命中率百分比 
------------+----------+-----------+------------------
 monitor    |  3526646 |      6658 |            99.81
 test_db    |  1477676 |      1787 |            99.88
 db_666     |  1231842 |      1361 |            99.89
(3 rows)

2、显示核心信息

-- 显示核心信息
SELECT 
    datname AS 数据库名称,
    blks_hit AS 缓存命中,
    blks_read AS 磁盘读取,
    CASE 
        WHEN (blks_hit + blks_read) = 0 THEN 0
        ELSE ROUND(100.0 * blks_hit / (blks_hit + blks_read), 2)
    END AS 缓存命中率
FROM 
    pg_stat_database
WHERE 
    datname NOT IN ('template0', 'template1')
    AND blks_hit + blks_read > 0
ORDER BY 
    缓存命中率 ASC;

------------+----------+----------+------------
  数据库名称 | 缓存命中 | 磁盘读取 | 缓存命中率 
------------+----------+----------+------------
 monitor    |  3520081 |     6657 |      99.81
 postgres   |  1428522 |     2515 |      99.82
 test_db    |  1476308 |     1787 |      99.88
 db_666     |  1230528 |     1361 |      99.89
(4 rows)

3、更详细的监控查询（包含统计信息）

-- 更详细的监控查询（包含统计信息）
SELECT 
    datname AS 数据库名称,
    blks_hit AS 缓存命中次数,
    blks_read AS 磁盘读取次数,
    (blks_hit + blks_read) AS 总读取次数,
    CASE 
        WHEN (blks_hit + blks_read) = 0 THEN 0
        ELSE ROUND(100.0 * blks_hit / (blks_hit + blks_read), 2)
    END AS 缓存命中率,
    -- 计算每次磁盘读取对应的缓存命中次数
    CASE 
        WHEN blks_read = 0 THEN '∞'
        ELSE ROUND(blks_hit::numeric / NULLIF(blks_read, 0), 2)::text
    END AS "命中/读取比",  -- 使用双引号
    tup_returned AS 返回元组数,
    tup_fetched AS 获取元组数,
    tup_inserted AS 插入元组数,
    tup_updated AS 更新元组数,
    tup_deleted AS 删除元组数
FROM 
    pg_stat_database
WHERE 
    datname NOT IN ('template0', 'template1')
    AND blks_hit + blks_read > 0
ORDER BY 
    缓存命中率 ASC;
    
 数据库名称 | 缓存命中次数 | 磁盘读取次数 | 总读取次数 | 缓存命中率 | 命中/读取比 | 返回元组数 | 获取元组数 | 插入元组数 | 更新元组数 
| 删除元组数 
------------+--------------+--------------+------------+------------+-------------+------------+------------+------------+------------
+------------
 monitor    |      3519999 |         6657 |    3526656 |      99.81 | 528.77      |   26936184 |    4629821 |      47286 |       2833 
|       3928
 postgres   |      1428359 |         2513 |    1430872 |      99.82 | 568.39      |   16619163 |     290272 |          7 |          0 
|          0
 test_db    |      1476232 |         1787 |    1478019 |      99.88 | 826.10      |   22717443 |     283534 |          0 |          7 
|          0
 db_666     |      1230455 |         1361 |    1231816 |      99.89 | 904.08      |   14614477 |     246020 |          8 |         14 
|          1
(4 rows)

4、仅看当前库

-- 仅看当前库
SELECT 
    current_database() AS 当前数据库,
    blks_hit,
    blks_read,
    ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS 缓存命中率
FROM 
    pg_stat_database
WHERE 
    datname = current_database();

5、查看历史趋势（需要统计扩展）

-- 查看最近5分钟的增量变化
WITH stats_snapshot AS (
    SELECT 
        datname,
        blks_hit,
        blks_read,
        now() AS snapshot_time
    FROM 
        pg_stat_database
    WHERE 
        datname NOT IN ('template0', 'template1')
)
SELECT 
    datname,
    blks_hit,
    blks_read,
    ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS 当前命中率
FROM 
    stats_snapshot
WHERE 
    blks_hit + blks_read > 0
ORDER BY 
    当前命中率 ASC;

6、常用监控指标

-- 简洁版：只显示关键指标
SELECT 
    datname,
    ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_ratio,
    blks_hit,
    blks_read
FROM 
    pg_stat_database
WHERE 
    datname NOT IN ('template0', 'template1')
    AND blks_hit + blks_read > 0
ORDER BY 
    cache_hit_ratio;

7、结果解读

- **缓存命中率 > 95%** ：优秀，内存配置合理    
- **缓存命中率 80-95%**：良好，可以考虑增加 shared_buffers    
- **缓存命中率 < 80%** ：需要关注，可能存在内存不足或查询优化问题    
- **缓存命中率 < 50%** ：严重问题，需要立即检查内存配置和索引