DevelopPostgreSQL

PostgreSQL - Top N 쿼리 확인 방법

pg_stat_statements를 이용한 Top N 쿼리 확인

PostgreSQL에서 가장 많이 실행되거나 느린 쿼리를 확인하는 방법을 정리합니다.

1. pg_stat_statements 확장 설치

-- 확장 설치
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 설치 확인
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

2. postgresql.conf 설정

# postgresql.conf 파일에 추가
shared_preload_libraries = 'pg_stat_statements'

# 추적할 쿼리 수 (기본값: 5000)
pg_stat_statements.max = 10000

# 쿼리 추적 모드 (top: 최상위 쿼리만, all: 중첩 쿼리 포함)
pg_stat_statements.track = all

설정 변경 후 PostgreSQL 재시작 필요:

# 재시작
sudo systemctl restart postgresql

# 또는
pg_ctl restart

3. 실행 횟수가 많은 Top N 쿼리

SELECT
    query,
    calls AS exec_count,
    total_exec_time,
    mean_exec_time,
    min_exec_time,
    max_exec_time,
    rows
FROM
    pg_stat_statements
ORDER BY
    calls DESC
LIMIT 10;

4. 평균 실행 시간이 긴 Top N 쿼리

SELECT
    query,
    calls AS exec_count,
    mean_exec_time,
    min_exec_time,
    max_exec_time,
    stddev_exec_time
FROM
    pg_stat_statements
WHERE
    calls > 10  -- 최소 10회 이상 실행된 쿼리
ORDER BY
    mean_exec_time DESC
LIMIT 10;

5. 총 실행 시간이 긴 Top N 쿼리

SELECT
    query,
    calls AS exec_count,
    total_exec_time,
    mean_exec_time,
    (total_exec_time / SUM(total_exec_time) OVER ()) * 100 AS percentage,
    rows
FROM
    pg_stat_statements
ORDER BY
    total_exec_time DESC
LIMIT 10;

6. I/O가 많은 Top N 쿼리

SELECT
    query,
    calls,
    total_exec_time,
    shared_blks_hit,
    shared_blks_read,
    shared_blks_dirtied,
    shared_blks_written,
    temp_blks_read,
    temp_blks_written
FROM
    pg_stat_statements
ORDER BY
    (shared_blks_hit + shared_blks_read) DESC
LIMIT 10;

7. 상세 정보를 포함한 Top N 쿼리

SELECT
    substring(query, 1, 100) AS short_query,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_time_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_time_ms,
    ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS percentage,
    rows AS total_rows,
    ROUND((rows / calls)::numeric, 2) AS avg_rows_per_call
FROM
    pg_stat_statements
WHERE
    calls > 5
ORDER BY
    total_exec_time DESC
LIMIT 10;

8. 현재 실행중인 쿼리 확인

-- 현재 활성 쿼리 확인
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    NOW() - query_start AS duration,
    query
FROM
    pg_stat_activity
WHERE
    state != 'idle'
    AND query NOT LIKE '%pg_stat_activity%'
ORDER BY
    duration DESC;

9. 느린 쿼리 로깅 설정

-- 현재 세션에만 적용
SET log_min_duration_statement = 1000;  -- 1초 이상 쿼리 로깅

-- 전역 설정 (postgresql.conf)

postgresql.conf:

# 1초 이상 실행되는 쿼리 로깅
log_min_duration_statement = 1000

# 로그에 추가 정보 포함
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'all'  # 또는 'ddl', 'mod', 'none'
log_duration = on
log_lock_waits = on

10. pg_stat_statements 통계 초기화

-- 모든 통계 초기화
SELECT pg_stat_statements_reset();

-- 특정 쿼리 통계만 초기화 (PostgreSQL 12+)
SELECT pg_stat_statements_reset(userid, dbid, queryid);

11. 유용한 관리 쿼리

-- 데이터베이스별 통계
SELECT
    d.datname,
    COUNT(*) AS query_count,
    SUM(calls) AS total_calls,
    ROUND(SUM(total_exec_time)::numeric, 2) AS total_time_ms
FROM
    pg_stat_statements s
    JOIN pg_database d ON s.dbid = d.oid
GROUP BY
    d.datname
ORDER BY
    total_time_ms DESC;

-- 캐시 히트율 확인
SELECT
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    CASE
        WHEN (shared_blks_hit + shared_blks_read) > 0
        THEN ROUND((shared_blks_hit::numeric / (shared_blks_hit + shared_blks_read)) * 100, 2)
        ELSE 0
    END AS cache_hit_ratio
FROM
    pg_stat_statements
WHERE
    (shared_blks_hit + shared_blks_read) > 0
ORDER BY
    cache_hit_ratio ASC
LIMIT 10;

주요 컬럼 설명

  • calls: 쿼리 실행 횟수
  • total_exec_time: 총 실행 시간 (밀리초)
  • mean_exec_time: 평균 실행 시간 (밀리초)
  • rows: 반환되거나 영향받은 총 행 수
  • shared_blks_hit: 공유 버퍼 캐시에서 읽은 블록 수
  • shared_blks_read: 디스크에서 읽은 블록 수
  • temp_blks_read/written: 임시 파일 I/O (정렬, 해시 등)

pg_stat_statements vs pg_stat_activity

  • pg_stat_statements: 누적 통계 (과거부터 현재까지의 모든 쿼리)
  • pg_stat_activity: 현재 실행 중인 쿼리만 표시

참고사항

  • pg_stat_statements는 PostgreSQL 9.2 이상에서 사용 가능합니다
  • shared_preload_libraries 설정 변경 시 PostgreSQL 재시작이 필요합니다
  • 쿼리는 정규화되어 저장됩니다 (파라미터 값은 ? 또는 $1 등으로 대체)
  • 프로덕션 환경에서 사용 시 pg_stat_statements.max 값을 적절히 조정하세요
Share