Postgres 성능 개선할 쿼리 확인하는 쿼리

-- 프로세스 목록
SELECT pid, usename, application_name, client_addr, backend_start, query_start, state, query
FROM pg_stat_activity order by query_start desc;

-- 슬로우 쿼리 확인
SELECT queryid, calls, total_exec_time / 1000 AS total_exec_time_ms,
       mean_exec_time / 1000 AS mean_exec_time_ms, max_exec_time / 1000 AS max_exec_time_ms, rows, query
FROM pg_stat_statements
ORDER BY max_exec_time_ms DESC
LIMIT 100;

-- 성능 개선 대상 식별 (풀 스캔 쿼리)
SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
WHERE total_plan_time + total_exec_time > 2000  -- 2초 이상 걸린 쿼리
  AND query NOT ILIKE '%pg_%'  -- 시스템 쿼리 제외
ORDER BY total_exec_time DESC
LIMIT 10;

-- I/O 요청이 많은 테이블 목록
SELECT schemaname, relname, heap_blks_read, heap_blks_hit,
       idx_blks_read, idx_blks_hit
FROM pg_statio_user_tables WHERE idx_blks_read is not null
ORDER BY heap_blks_read + idx_blks_read DESC
LIMIT 10;

-- 테이블별 작업량 통계
SELECT schemaname, relname, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND idx_scan is not null
ORDER BY seq_scan + idx_scan DESC
LIMIT 10;

-- 총 메모리 사용량 확인
SELECT pg_size_pretty(
  (SELECT setting::bigint * 8192 FROM pg_settings WHERE name = 'shared_buffers') +
  (SELECT sum(setting::bigint) FROM pg_settings WHERE name IN ('work_mem', 'maintenance_work_mem', 'temp_buffers'))
) AS total_memory_usage;

-- 세션별 메모리 사용량 확인
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    backend_start,
    state,
    query,
    pg_size_pretty((SELECT setting::bigint * 1024 FROM pg_settings WHERE name = 'work_mem')) AS work_mem_setting
FROM
    pg_stat_activity
WHERE
    state != 'idle'
ORDER BY
    (SELECT setting::bigint FROM pg_settings WHERE name = 'work_mem') DESC
LIMIT 10;

-- 최근 실행된 쿼리 이력 확인
SELECT pid, usename, query_start, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC
LIMIT 10;

-- lock 확인
SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;