beautifulsoups.dev
Published on

PostgreSQL 인덱스 미사용? 통계·파라미터 튜닝

Authors

운영에서 자주 보는 장면이 있습니다. 분명히 컬럼에 인덱스가 있고, 조건도 인덱스로 충분히 걸러질 것 같은데 EXPLAIN 을 보면 Seq Scan 이 나옵니다. 이때 무작정 인덱스를 더 만들거나 힌트를 찾기 전에, PostgreSQL이 왜 그 선택을 했는지부터 확인해야 합니다. PostgreSQL은 힌트 기반이 아니라 통계(Statistics)와 비용 모델(Cost model) 로 플랜을 고르기 때문에, 통계가 틀리거나 파라미터 바인딩 방식 때문에 선택도가 달라지면 “인덱스 미사용”처럼 보이는 결과가 쉽게 발생합니다.

이 글은 다음 순서로 문제를 재현하고 해결합니다.

  • EXPLAIN (ANALYZE, BUFFERS) 로 실제 실행과 추정의 차이를 확인
  • 통계가 왜 틀어지는지(autovacuum, stale stats, 상관관계, 다중 컬럼 조건)
  • 파라미터 바인딩과 플랜 캐시가 인덱스 사용을 막는 케이스
  • 비용 파라미터와 메모리 파라미터로 planner 판단을 현실화
  • 실전 체크리스트

참고로 테이블이 비정상적으로 커지거나 vacuum/analyze가 제때 돌지 않는 환경이라면 통계가 더 쉽게 망가집니다. autovacuum 이슈는 별도 글인 PostgreSQL autovacuum 멈춤으로 테이블 폭증 해결 도 함께 보시면 진단이 빨라집니다.

1) 먼저 확인: “인덱스 미사용”이 정말 느린가

인덱스를 안 타는 것이 항상 나쁜 것은 아닙니다. 조건이 넓어서 대부분의 행을 읽어야 한다면, 랜덤 I/O가 많은 인덱스 스캔보다 Seq Scan 이 더 빠를 수 있습니다. 따라서 첫 단계는 “플랜이 마음에 안 든다”가 아니라, 실제 병목인지 확인하는 것입니다.

필수 쿼리: EXPLAIN으로 추정치 vs 실제치 비교

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

여기서 봐야 할 핵심은 다음입니다.

  • rows=: planner의 추정 행 수
  • actual rows=: 실제 반환 행 수
  • Buffers:: shared hit/read, temp read/write
  • Sort Method:: 메모리 정렬인지 디스크 정렬인지

추정 행 수가 실제와 크게 다르면, planner는 잘못된 정보로 결정을 내린 것입니다. 이때부터 통계와 파라미터를 의심합니다.

2) 통계(Statistics)가 틀어지면 인덱스가 있어도 안 탄다

PostgreSQL은 테이블/컬럼 통계를 기반으로 선택도(selectivity)를 계산합니다. 대표적으로 다음 값들이 플랜에 영향을 줍니다.

  • n_distinct: 서로 다른 값의 개수 추정
  • most_common_vals, most_common_freqs: 자주 나오는 값과 빈도
  • histogram_bounds: 분포 히스토그램
  • correlation: 물리적 저장 순서와 값의 상관관계(특히 범위 조건)

통계가 부정확해지는 흔한 이유는 아래와 같습니다.

  • 대량 배치 업데이트/삭제/삽입 이후 ANALYZE 가 충분히 수행되지 않음
  • autovacuum/analyze 설정이 테이블 변화량을 따라가지 못함
  • 컬럼 값 분포가 치우쳐 있는데 통계 샘플이 부족함
  • 다중 컬럼 조건에서 컬럼 간 상관관계를 고려하지 못함

2-1) ANALYZE로 통계 갱신

가장 먼저 해볼 수 있는 처방은 통계 갱신입니다.

ANALYZE orders;
-- 또는 특정 컬럼만
ANALYZE orders (user_id, status, created_at);

갱신 후 같은 EXPLAIN (ANALYZE) 를 다시 찍어 추정치가 개선되는지 확인합니다.

2-2) 통계 대상/정밀도 올리기: statistics target

값 분포가 극단적으로 치우친 컬럼(예: status, type, country_code)은 기본 통계로는 부족할 수 있습니다. 이때 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 로 샘플링 정밀도를 올립니다.

ALTER TABLE orders
  ALTER COLUMN status SET STATISTICS 1000;

ANALYZE orders;

정밀도를 올리면 ANALYZE 시간이 늘 수 있으니, 정말로 선택도 추정이 중요한 컬럼에만 적용합니다.

2-3) 다중 컬럼 조건: extended statistics로 상관관계 반영

user_id 와 status 처럼 컬럼 간 상관관계가 강하면, planner는 두 조건을 독립으로 가정해 선택도를 곱해버릴 수 있습니다. 그 결과 “너무 많은 행이 나올 것”이라고 오판해 Seq Scan 을 선택할 수 있습니다.

PostgreSQL의 extended statistics로 이를 보완할 수 있습니다.

-- ndistinct 또는 dependencies를 활용
CREATE STATISTICS orders_user_status_dep (dependencies)
ON user_id, status
FROM orders;

ANALYZE orders;

적용 후 EXPLAIN 에서 추정 행 수가 실제에 가까워지는지 확인하세요.

3) 파라미터 바인딩과 플랜 캐시: “값에 따라 플랜이 달라져야” 하는데 고정되는 문제

애플리케이션에서 흔히 겪는 함정은 prepared statement 또는 ORM이 만든 바인딩 쿼리입니다.

  • 상수 값으로 실행하면 인덱스를 타는데
  • 바인딩 파라미터로 실행하면 Seq Scan 이 뜨는 현상

이는 PostgreSQL이 generic plan(파라미터 값에 독립적인 일반 플랜)을 택했기 때문일 수 있습니다. 특히 값 분포가 치우쳐서, 어떤 값은 결과가 10건인데 어떤 값은 1,000만 건인 경우에 문제가 커집니다.

3-1) 커스텀 플랜 vs 제네릭 플랜 확인

세션 단위로 planner의 선택을 강제해 원인을 좁힐 수 있습니다.

-- 세션에서만
SET plan_cache_mode = force_custom_plan;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = $1;

반대로 제네릭 플랜을 강제해 차이를 비교할 수도 있습니다.

SET plan_cache_mode = force_generic_plan;

두 모드에서 플랜이 크게 달라지고 성능 차이가 크다면, 애플리케이션의 prepared statement 전략(혹은 드라이버 설정)을 조정할 여지가 있습니다.

3-2) IN 리스트 vs ANY($1): 타입/추정 문제

배열 파라미터를 쓰는 패턴도 플랜을 흔듭니다.

-- 애플리케이션에서 자주 나오는 형태
SELECT * FROM orders
WHERE status = ANY($1);

이때 $1 의 타입이 애매하거나(unknown), 배열 길이가 큰데 통계가 이를 반영하지 못하면 planner가 보수적으로 잡아 Seq Scan 을 선택할 수 있습니다. 가능한 한 타입을 명확히 하거나, 배열 길이가 커질 때는 임시 테이블/조인으로 바꾸는 전략이 더 안정적입니다.

-- 타입 명시
SELECT * FROM orders
WHERE status = ANY($1::text[]);

4) 비용 모델 파라미터: SSD인데도 HDD처럼 계산하면 인덱스를 덜 탄다

PostgreSQL planner는 “얼마나 비싼가”를 파라미터로 계산합니다. 대표적으로 다음이 큽니다.

  • random_page_cost: 랜덤 페이지 접근 비용
  • seq_page_cost: 순차 접근 비용
  • effective_cache_size: OS 캐시 포함 가용 캐시 추정

SSD 환경인데 기본 random_page_cost 가 높게 설정되어 있으면, planner는 인덱스 스캔을 과하게 비싸게 보고 Seq Scan 을 더 선호할 수 있습니다.

4-1) 현재 값 확인

SHOW random_page_cost;
SHOW seq_page_cost;
SHOW effective_cache_size;

4-2) 현실적인 값으로 조정(예시)

아래는 “대개”의 예시입니다. 실제로는 스토리지/캐시/워크로드에 따라 벤치마크가 필요합니다.

-- postgresql.conf 또는 ALTER SYSTEM로 조정
ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET seq_page_cost = '1.0';
ALTER SYSTEM SET effective_cache_size = '8GB';

SELECT pg_reload_conf();
  • effective_cache_size 는 shared_buffers 만이 아니라 OS 페이지 캐시까지 포함한 “planner용 힌트”입니다.
  • 너무 공격적으로 낮추면 인덱스를 과신해서 오히려 느려질 수 있으니, 특정 쿼리만 보고 조정하지 말고 대표 워크로드를 함께 봐야 합니다.

5) 메모리 파라미터와 정렬/해시: 인덱스가 아니라 sort/aggregate가 병목일 수도

ORDER BY ... LIMIT 에서 인덱스가 있어도, planner가 다른 경로를 택하거나 정렬이 디스크로 떨어지면 느려질 수 있습니다.

  • work_mem 이 너무 작으면 정렬(Sort) 또는 해시 조인(Hash Join)이 temp 파일을 사용
  • 그 결과 “인덱스 안 타서 느린 것처럼” 보이지만 실제 병목은 temp I/O

5-1) 디스크 정렬 여부 확인

EXPLAIN (ANALYZE, BUFFERS) 결과에 아래가 보이면 위험 신호입니다.

  • Sort Method: external merge
  • temp read= 또는 temp written=

5-2) 세션 단위로 work_mem 올려 재현

SET work_mem = '128MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, count(*)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY user_id
ORDER BY count(*) DESC
LIMIT 100;

세션에서만 올려 성능이 개선되면, 전역 튜닝 또는 특정 작업 전용 커넥션/잡에서만 상향하는 방식이 안전합니다. 커넥션 풀이 큰 환경에서는 work_mem 을 전역으로 올리는 것이 메모리 폭탄이 될 수 있으니 주의해야 합니다. 커넥션 풀과 DB 메모리의 관계는 Spring Boot HikariCP 풀 고갈 원인·튜닝 실전 도 같이 보면 운영 관점에서 연결됩니다.

6) 인덱스 자체의 문제: “있다”와 “쓸 수 있다”는 다르다

통계/파라미터 이슈가 아닌데도 인덱스를 안 타면, 인덱스가 조건과 맞지 않는 경우가 많습니다.

6-1) 함수/캐스팅이 컬럼에 걸리면 인덱스가 무력화

-- 인덱스: idx_users_email (email)
SELECT * FROM users
WHERE lower(email) = 'a@b.com';

이 경우 일반 인덱스는 못 씁니다. 함수 기반 인덱스를 고려합니다.

CREATE INDEX idx_users_email_lower ON users (lower(email));

6-2) 복합 인덱스의 선두 컬럼 규칙

복합 인덱스 (user_id, created_at) 이 있는데 created_at 만 조건이면 기대만큼 못 탑니다.

-- 인덱스가 있어도 선두 컬럼이 빠지면 효율이 떨어짐
SELECT * FROM orders
WHERE created_at >= now() - interval '1 day';

이 경우에는 created_at 단일 인덱스나 파티셔닝, 또는 쿼리 패턴 자체 변경이 필요할 수 있습니다.

6-3) 부분 인덱스(partial index)로 선택도 극대화

상태값이 특정 값일 때만 자주 조회한다면 부분 인덱스가 강력합니다.

CREATE INDEX idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'PAID';

planner가 “이 인덱스는 훨씬 작고 선택도가 좋다”고 판단하면 인덱스를 탈 확률이 크게 올라갑니다.

7) 실전 진단 체크리스트

아래 순서로 보면 원인 규명이 빨라집니다.

  1. EXPLAIN (ANALYZE, BUFFERS) 로 추정 행 수와 실제 행 수의 괴리를 확인
  2. 통계 갱신: ANALYZE 후 변화 확인
  3. 분포가 치우친 컬럼이면 SET STATISTICS 로 정밀도 상향 후 재분석
  4. 다중 조건이면 extended statistics(CREATE STATISTICS ... (dependencies) 등) 적용 검토
  5. 바인딩 쿼리라면 plan_cache_mode 로 custom/generic 플랜 차이를 확인
  6. 비용 파라미터(random_page_cost, effective_cache_size)가 인프라 현실과 맞는지 점검
  7. work_mem 부족으로 temp I/O가 생기는지 확인
  8. 인덱스가 쿼리 형태(함수, 캐스팅, 선두 컬럼, 부분 조건)와 맞는지 재점검

8) 마무리: 인덱스는 “존재”가 아니라 “planner가 믿게” 만들어야 한다

PostgreSQL에서 인덱스 미사용 문제는 대개 인덱스가 없어서가 아니라, planner가 통계와 비용 모델을 근거로 “인덱스가 더 느릴 것”이라고 결론 내렸기 때문에 발생합니다. 따라서 해결도 인덱스 추가만으로 끝나지 않고, 다음 중 하나(혹은 조합)로 귀결됩니다.

  • 통계가 현실을 반영하도록 ANALYZE, 통계 타깃, extended statistics를 조정
  • 파라미터 바인딩/플랜 캐시로 인한 generic plan 문제를 확인하고 전략을 바꿈
  • 비용 파라미터를 스토리지/캐시 현실에 맞게 보정
  • 쿼리와 인덱스 정의를 “서로 맞물리게” 재설계(부분 인덱스, 함수 인덱스 등)

위 과정을 거치면 “왜 인덱스를 안 타는지”가 설명 가능해지고, 설명 가능한 문제는 재발 방지도 가능합니다.

Discuss on Twitter • View on GitHub