1）当前系统连接

SELECT * FROM pg_stat_activity WHERE backend_type = 'client backend';

2）blocking

WITH RECURSIVE blocking_chain AS (
    -- 起始：所有被阻塞的会话
    SELECT 
        waiter.pid AS waiting_pid,
        blocker.pid AS blocking_pid,
        1 AS level,
        ARRAY[waiter.pid] AS path,
        false AS cycle
    FROM pg_locks waiter
    JOIN pg_locks blocker 
        ON waiter.locktype = blocker.locktype
        AND waiter.database IS NOT DISTINCT FROM blocker.database
        AND waiter.relation IS NOT DISTINCT FROM blocker.relation
        AND waiter.page IS NOT DISTINCT FROM blocker.page
        AND waiter.tuple IS NOT DISTINCT FROM blocker.tuple
        AND waiter.virtualxid IS NOT DISTINCT FROM blocker.virtualxid
        AND waiter.transactionid IS NOT DISTINCT FROM blocker.transactionid
        AND waiter.classid IS NOT DISTINCT FROM blocker.classid
        AND waiter.objid IS NOT DISTINCT FROM blocker.objid
        AND waiter.objsubid IS NOT DISTINCT FROM blocker.objsubid
        AND waiter.pid != blocker.pid
    WHERE NOT waiter.granted AND blocker.granted
    
    UNION ALL
    
    -- 递归：查找阻塞者的阻塞者
    SELECT 
        bc.waiting_pid,
        blocker.pid AS blocking_pid,
        bc.level + 1,
        bc.path || blocker.pid,
        blocker.pid = ANY(bc.path)
    FROM blocking_chain bc
    JOIN pg_locks waiter ON waiter.pid = bc.blocking_pid
    JOIN pg_locks blocker 
        ON waiter.locktype = blocker.locktype
        AND waiter.database IS NOT DISTINCT FROM blocker.database
        AND waiter.relation IS NOT DISTINCT FROM blocker.relation
        AND waiter.page IS NOT DISTINCT FROM blocker.page
        AND waiter.tuple IS NOT DISTINCT FROM blocker.tuple
        AND waiter.virtualxid IS NOT DISTINCT FROM blocker.virtualxid
        AND waiter.transactionid IS NOT DISTINCT FROM blocker.transactionid
        AND waiter.classid IS NOT DISTINCT FROM blocker.classid
        AND waiter.objid IS NOT DISTINCT FROM blocker.objid
        AND waiter.objsubid IS NOT DISTINCT FROM blocker.objsubid
        AND waiter.pid != blocker.pid
        AND blocker.pid != bc.waiting_pid
    WHERE NOT waiter.granted AND blocker.granted
    AND NOT bc.cycle
)
SELECT 
    waiting_pid,
    (SELECT query FROM pg_stat_activity WHERE pid = waiting_pid) AS waiting_query,
    blocking_pid,
    (SELECT query FROM pg_stat_activity WHERE pid = blocking_pid) AS blocking_query,
    level,
    path
FROM blocking_chain
ORDER BY level, waiting_pid;

3)表访问统计

SELECT * FROM pg_stat_user_tables ORDER BY seq_scan DESC;

4)索引使用情况

SELECT * FROM pg_stat_user_indexes ORDER BY idx_scan;