PG DBA培训25：PostgreSQL性能分析与优化调整

-- 查询长时间运行的查询
SELECT
    pid,
    usename,
    datname,
    query,
    state,
    query_start,
    now() - query_start AS duration
FROM
    pg_stat_activity
WHERE
    state = 'active' AND query NOT ILIKE '%pg_sleep%'
ORDER BY
    duration DESC
LIMIT 5;
 
-- 查询最消耗CPU时间的查询
SELECT
    pid,
    usename,
    datname,
    query,
    state,
    query_start,
    round(total_time / execution_time, 2) AS cpu_usage
FROM
    pg_stat_activity
WHERE
    state = 'active' AND query NOT ILIKE '%pg_sleep%'
ORDER BY
    cpu_usage DESC
LIMIT 5;
 
-- 查询正在等待的锁
SELECT
    pid,
    usename,
    datname,
    query,
    state,
    query_start,
    waiting,
    now() - query_start AS wait_time
FROM
    pg_stat_activity
WHERE
    waiting = 't'
ORDER BY
    wait_time DESC
LIMIT 5;

这段代码首先查询当前活跃的长时间运行的查询，然后查询CPU使用率最高的查询，最后查询正在等待的锁。通过这些查询，数据库管理员可以识别潜在的性能瓶颈，并采取相应的优化措施。