PostgreSQL 쿼리 튜닝

PostgreSQL 쿼리 튜닝은(는) 무엇을 하나요?

명제 검증은 CHECKS.md를 참고하세요. 아래 모든 내용은 일반 PostgreSQL에 적용됩니다. 먼저 버전을 확인하세요(SHOW server_version): 기본 BUFFERS(PG18), skip scan(PG18), 기본 hash_mem_multiplier(PG15부터 2.0)가 버전에 따라 다릅니다.

핵심 규칙

무작정 고치지 마세요. 순서는 다음과 같습니다:

1. 실제로 시간이 소요되는 위치
2. 어떤 쿼리가 원인이며 호출 횟수는 얼마인지
3. 계획 노드 중 행 추정이 처음으로 틀린 지점
4. 수정
5. 프로덕션 규모 데이터에서 페이지 수 기준 검증

인덱스, work_mem, random_page_cost는 진단이 지목할 때만 수정하세요. 이유: 프로파일 없이 '무서운' 쿼리를 수정해도 1/60밖에 기여하지 못하며, 무작정 추가한 인덱스는 오히려 긴 쿼리에 해를 끼칩니다.

시간이 소요되는 위치 찾기

• '느리다'는 불만: 먼저 DB 시간과 사용자가 체감하는 시간을 비교하세요. 쿼리 합계가 약 100ms인데 1분을 호소한다면 DB 문제가 아닙니다. 쿼리를 건드리지 말고 애플리케이션, 네트워크, 프론트엔드를 확인하세요.
• 다중 쿼리 작업: pg_stat_statements로 프로파일을 수집하세요.
  1. pg_stat_statements.track = all로 설정하세요. 기본값은 top이므로 함수 내부 쿼리는 보이지 않습니다.
  2. pg_stat_statements_reset()을 호출하세요. 슈퍼유저 또는 함수에 부여된 권한이 필요합니다. 재설정은 모든 통계를 삭제하므로 스테이징 환경이나 조용한 시간에만 수행하세요.
  3. 작업을 한 번 실행하고 total_exec_time 기준으로 정렬하세요.
  4. calls, shared_blks_hit + shared_blks_read, toplevel을 확인하세요. toplevel = true인 행의 시간에는 이미 중첩 쿼리가 포함됩니다. 무작정 모두 합치면 시간이 두 번 계산됩니다.
• 주요 비율을 차지하는 부분만 수정하세요. 개선 효과는 그 비율을 넘을 수 없습니다: 60초 중 1초짜리 하위 작업은 최대 1/60만 개선됩니다.
• pg_stat_statements의 쿼리는 정규화됩니다: 상수는 $1로 대체되며, 세션별로 분리되지 않고, 집계(평균, min/max, stddev)만 저장됩니다. 값에 따라 계획이 달라질 것 같으면(큰 고객 vs 작은 고객) 구체적인 값을 가져와서 따로 EXPLAIN을 분석하세요. 쿼리 ID로 pg_stat_activity 및 EXPLAIN VERBOSE와 연결하려면 compute_query_id가 필요합니다(PG14부터; auto는 pg_stat_statements가 로드되면 활성화됨).
• 로그를 통한 방법: log_line_prefix에 %p 또는 %c와 시간 %m이 필요합니다. 중첩 쿼리는 auto_explain을 통해서만 볼 수 있습니다. log_min_duration을 설정하고(기본값 -1, 즉 비활성화), log_analyze와 log_nested_statements를 활성화하세요(PG15부터 슈퍼유저 또는 GRANT SET 필요). 로그는 pgBadger로 분석하세요. 단일 세션의 경우 SET log_min_duration_statement(슈퍼유저 또는 GRANT SET ON PARAMETER 필요)와 LOAD 'auto_explain'(슈퍼유저만 가능)으로 충분합니다.

측정 기준 데이터

• 개발 DB의 장난감 데이터로 결론을 내리지 마세요. 계획은 테이블 크기와 통계에 따라 달라지며, 소규모 DB는 전체가 캐시에 있습니다. 소규모 DB에서 쿼리가 빠르면 아무것도 증명하지 못합니다.
• 볼륨과 분포는 프로덕션과 동일하게. 프로덕션에 개인 데이터가 있으면 스테이징에 복사본을 가져오지 마세요. 동일한 형태의 합성 데이터를 생성하세요: 주요 테이블의 행 수와 클라이언트별 분포. 프로덕션에서는 통계(pg_stat_statements, pg_class)만 수집하세요.
• 프로덕션 상수가 포함된 계획을 외부 시각화 도구에 붙여넣지 마세요: 상수에 개인 데이터가 있을 수 있습니다. 로컬 도구를 사용하세요.

다수의 빠른 호출(N+1)

• 쿼리가 이미 빠르지만 단일 작업에서 수십, 수백 번 호출된다면 속도를 높이지 마세요. 전체 배치를 하나의 쿼리로 재작성하세요:

  • 코드에서 ID 목록을 루프하는 대신 WHERE id = ANY($1) 또는 배치 사용
  • 카테고리별 집계는 count(*) FILTER (WHERE …) 사용
  • 데이터가 없는 행을 잃지 않도록 LEFT JOIN 사용

  이유: 1ms 쿼리는 더 이상 빠르게 만들 수 없지만, 천 번 호출되면 속도가 느려집니다. 예를 들어, 하나의 쿼리로 통합된 27번의 호출은 약 60,000페이지를 읽는 반면, 개별 호출은 약 900,000페이지를 읽습니다.

• 단일 쿼리로 해결되지 않으면 DB 설정이 아닌 서비스의 데이터 액세스 방식을 재검토하세요.

변형 비교 방법

• 주요 지표는 페이지 수입니다. EXPLAIN (ANALYZE, BUFFERS)를 실행하고 최상위 노드의 shared hit + read를 확인하세요: 이미 하위 노드의 합계입니다. hit와 read는 캐시 의존성이므로 합산하세요. PG18에서는 ANALYZE와 함께 BUFFERS가 자동으로 포함됩니다.
• 시간은 참고용일 뿐입니다. 캐시, 부하, 하드웨어, ANALYZE 측정 자체의 영향을 받습니다. 두 번째 실행은 항상 더 빠르며 아무것도 증명하지 않습니다. '더 빨라졌다'는 여러 번 실행 후에만 말하세요.
• 페이지 수는 CPU 부하를 반영하지 않습니다: 메모리 내 정렬, 함수, JIT, 병렬 계획. 쿼리가 읽기보다 계산을 더 많이 수행한다면 시간을 별도로 확인하세요.
• 행과 버퍼만 필요한 경우 TIMING FALSE를 추가하세요: 측정 오버헤드가 없습니다.

계획 분석

1. 쿼리 클래스

   • 짧은 쿼리: 적은 행을 반환하며, 사용자가 응답을 기다립니다. 예: 객체 카드, 목록 페이지, 키 검색. 인덱스 액세스와 중첩 루프를 예상하세요. 분석 중에는 SET max_parallel_workers_per_gather = 0으로 설정하세요.
   • 긴 쿼리: 테이블을 거의 전체 스캔하며, 상단에 집계가 있습니다. 예: 보고서, 분석, 내보내기. 순차 스캔과 해시 조인을 예상하며, 병렬 처리는 정상입니다.

   이는 기대일 뿐 규칙이 아닙니다. 계획이 기대와 다르면 인덱스를 추가하지 말고 추정치에서 원인을 찾으세요.

2. 추정치 vs 실제값. 노드별로 rows와 actual rows를 비교하세요. loops > 1인 경우 actual rows 값은 한 번 실행 기준이므로 loops를 곱하세요. 불일치가 처음 나타난 가장 안쪽 노드를 수정하세요. 오류는 계획 위로 전파되어 잘못된 액세스 방식과 잘못된 조인으로 이어집니다.

3. 페이지 수 vs 테이블 크기(긴 쿼리의 경우). ANALYZE를 실행하세요. 그런 다음 최상위 노드의 hit + read를 쿼리 테이블의 relpages 합계(pg_class에서)와 비교하세요. 인덱스와 TOAST는 쿼리에서 읽는 경우 별도로 더하세요.

   • 비슷하거나 더 작음: 데이터를 한 번 읽으며 계획이 합리적입니다.
   • 몇 배 더 큼: 데이터를 반복해서 읽습니다(예: 중첩 루프 내부의 Index Scan).

   짧은 인덱스 쿼리에는 이 기준이 적합하지 않습니다.

4. 메모리. BUFFERS에 temp read/written이 있거나 Sort가 디스크를 사용하거나 Hash의 Batches > 1이면 메모리가 부족합니다. 해결 방법은 '무엇을 수정할 것인가' 섹션을 참조하세요.

무엇을 수정할 것인가

먼저 추정치와 쿼리 자체를 수정하고, 그다음 메모리를 수정하세요. 비용은 가장 마지막에 변경하세요.

• 조건의 함수 래핑(starts_at::date = …, date_trunc('day', starts_at) = …, round(col) …). 반개방 범위 starts_at >= $1 AND starts_at < $2로 재작성하세요. timestamptz의 경계는 서버 시간대가 아닌 사용자 시간대에서 계산하세요. 이유: 표현식에 대한 통계가 없으면 플래너는 고정 선택도(=의 경우 0.5% 행)를 사용하여 크게 오류를 범합니다. 열 자체의 범위는 통계를 기반으로 평가되며 인덱스를 사용합니다. 재작성이 불가능한 경우에만 표현식 인덱스 또는 통계를 만드세요.
• 선택된 인덱스를 확인하세요. 인덱스가 복합 인덱스이고 필터링되는 열이 첫 번째가 아니라면, PG18까지는 전체를 읽습니다. PG18에서는 skip scan(계획에 Index Searches: N으로 표시됨)이 도움이 되지만, 첫 번째 열에 고유 값이 적을 때만 유용합니다. 그렇지 않으면 해당 열이 첫 번째인 인덱스가 필요합니다.
• 복합 FK 선언(account_id, user_id). 애플리케이션에서 모든 것을 자체적으로 확인하더라도, FK의 모든 열을 단순 동등 조건으로 조인을 작성하세요. 플래너는 FK를 사용하여 여러 열에 걸친 조인을 평가합니다. FK가 없으면 열을 독립적으로 가정하여 크게 오류(100,000행 대신 1000행)를 범하고, 그 후 nested loop 대신 hash를 선택합니다. 조인이 FK를 완전히 포함하지 않는 경우(한 열이 표현식이거나 WHERE 절에 있음) FK 기반 평가가 적용되지 않습니다. 대상 테이블에는 동일한 열에 대한 고유 키가 필요합니다. 비용은 쓰기 오버헤드입니다.
• 긴 쿼리의 불필요한 조인. 테이블이 필터링에 기여하지 않고 FK와 NOT NULL로 관계가 보장된다면 제거하세요: 이것이 가장 큰 개선인 경우가 많습니다. 플래너가 다른 테이블에 대한 inner join을 자동으로 제거할 것이라고 기대하지 마세요(FK가 있어도). 플래너는 쿼리가 읽지 않는 열이 있는 고유 키에 대한 LEFT JOIN을 제거하며, PG18부터는 고유 키를 통한 자기 조인을 제거합니다. FK와 NOT NULL이 보장되지 않으면 이러한 조인을 건드리지 마세요: 결과가 변경됩니다.
• 추정치가 부정확하고 쿼리를 재작성할 수 없는 경우: 확장 통계를 만들거나 ALTER TABLE … ALTER COLUMN … SET STATISTICS n(기본값 100)을 설정하세요.
• 메모리 부족(temp 파일, Batches > 1). 세션 또는 역할에 대해 work_mem 또는 hash_mem_multiplier를 높이세요(SET, ALTER ROLE … SET). 전역적으로는 설정하지 마세요: work_mem은 각 세션의 각 노드에 할당됩니다. 해시 제한은 work_mem × hash_mem_multiplier입니다. 기본값은 4MB × 2.0 = 8MB입니다(PG15 이전에는 승수가 1.0이었음). SHOW로 확인하고 최종 제한을 계산하세요.

'플래너가 틀렸다'는 가설

• 트랜잭션 내에서 확인하세요: BEGIN; SET LOCAL enable_nestloop = off;(또는 enable_seqscan), 그다음 EXPLAIN (ANALYZE, BUFFERS), 그다음 ROLLBACK. 페이지 수로 비교하세요. 더 나은 계획이 있는지 알 수 있습니다.
• enable_* 설정을 구성 파일이나 서비스 코드에 남겨두지 마세요.** 이는 플래너가 특정 노드를 사용하지 못하도록 막으며 모든 쿼리에 영향을 줍니다. 문서에서는 이를 거칠고 임시적인 수단으로 설명합니다.
• 이제 두 가지 경로가 있습니다:
  • 행 추정치가 부정확함 – 추정치 수정('무엇을 수정할 것인가' 참조).
  • 추정치는 정확하지만 여러 쿼리에서 원하는 계획이 체계적으로 선택되지 않음 – 비용 변경. SSD를 사용하거나 데이터베이스가 거의 완전히 캐시에 있는 경우 random_page_cost를 4.0에서 1.1–2로 낮추되 seq_page_cost(1.0)보다 낮추지 마세요. effective_cache_size에는 실제 추정치(shared_buffers + OS 캐시, 기본값 4GB)를 설정하세요. 전체 데이터베이스에 대한 변경은 여러 쿼리에서 확인한 후에만 수행하세요.

스킬 외부

• pg_query_state – 패치된 커널 필요
• plprofiler 및 plpgsql_check – PL/pgSQL 로직용; 여기서 로직은 애플리케이션에 있음
• 상용 PostgreSQL 빌드가 필요한 기법