Source: Dev.to

PostgreSQL에서 누락된 인덱스를 찾고 고치는 방법

PostgreSQL은 강력한 자동화된 통계 수집 기능을 제공하지만, 인덱스가 없거나 잘못 설계된 경우는 여전히 성능 병목을 일으킬 수 있습니다. 이 글에서는 실제 운영 환경에서 누락된 인덱스를 식별하고, 적절히 추가하거나 조정하는 과정을 단계별로 살펴보겠습니다.

---

1️⃣ 인덱스 사용 현황 확인

a. pg_stat_user_indexes와 pg_stat_user_tables 활용

다음 쿼리는 스캔되지 않은 인덱스와 해당 테이블의 전체 스캔 비율을 보여줍니다.

SELECT
    s.schemaname,
    s.relname,
    i.indexrelname,
    i.idx_scan,
    i.idx_tup_read,
    i.idx_tup_fetch,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(s.relid))      AS table_size,
    round(100.0 * i.idx_scan / NULLIF(s.seq_scan + i.idx_scan, 0), 2) AS idx_scan_pct
FROM pg_stat_user_indexes i
JOIN pg_stat_user_tables s USING (relid)
WHERE i.idx_scan = 0               -- 한 번도 사용되지 않은 인덱스
ORDER BY pg_relation_size(i.indexrelid) DESC;

• idx_scan = 0 → 전혀 사용되지 않은 인덱스 (삭제 고려)
• idx_scan_pct가 낮은 경우 → 인덱스가 존재하지만 충분히 활용되지 않음

b. 인덱스가 전혀 없는 경우 찾기

SELECT
    n.nspname AS schema,
    c.relname AS table,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'                     -- 일반 테이블
  AND NOT EXISTS (
        SELECT 1
        FROM pg_index i
        WHERE i.indrelid = c.oid
      )
ORDER BY pg_relation_size(c.oid) DESC;

이 쿼리는 인덱스가 전혀 없는 테이블을 리스트업합니다. 큰 테이블일수록 우선 순위가 높습니다.

---

2️⃣ 실제 쿼리에서 인덱스가 필요한지 확인

a. EXPLAIN (ANALYZE, BUFFERS) 로 실행 계획 분석

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders o
WHERE o.customer_id = 12345
  AND o.created_at >= '2023-01-01';

• Seq Scan이 나타나면 인덱스가 없거나 사용되지 않은 것입니다.
• Buffers: shared hit=... read=... 를 통해 디스크 I/O 비용을 파악할 수 있습니다.

b. pg_stat_statements 로 가장 비용이 큰 쿼리 찾기

SELECT
    query,
    calls,
    total_time,
    rows,
    (total_time / calls) AS avg_time,
    (rows / calls)        AS avg_rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

가장 오래 걸리는 쿼리를 대상으로 인덱스 필요성을 검토합니다.

---

3️⃣ 인덱스 설계 팁

주의: 인덱스가 많을수록 쓰기 비용(INSERT/UPDATE/DELETE)이 증가합니다. 반드시 읽기/쓰기 비율을 고려하세요.

---

4️⃣ 인덱스 추가 후 검증

1. 통계 업데이트

   ANALYZE VERBOSE table_name;

2. 쿼리 재실행 (EXPLAIN + 실제 실행)
   인덱스가 사용되는지 확인하고, total_time이 감소했는지 비교합니다.

3. 모니터링
   몇 시간~하루 정도 pg_stat_user_indexes 를 재조회해 idx_scan이 증가했는지 확인합니다.

---

5️⃣ 자동화된 도구 활용

예시 – HypoPG 로 가상 인덱스 테스트:

-- 확장 설치 (한 번만)
CREATE EXTENSION IF NOT EXISTS hypopg;

-- 가상 인덱스 생성
SELECT hypopg_create_index('CREATE INDEX ON orders(customer_id, created_at)');

-- 가상 인덱스가 사용되는지 확인
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345 AND created_at >= '2023-01-01';

가상 인덱스가 실제 실행 계획에 포함되면, 실제 인덱스를 만들기에 충분히 가치가 있다는 신호입니다.

---

6️⃣ 인덱스 정리 (폐기) 전략

• idx_scan = 0 그리고 pg_relation_size(index) > 10 MB → 즉시 삭제 고려
• pg_stat_user_indexes 에서 idx_tup_fetch 가 매우 낮은 경우 → 사용 빈도 낮음
• 삭제 전: DROP INDEX CONCURRENTLY idx_name; 로 롤링 업데이트 수행 (읽기 서비스 중단 방지)

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_id_created_at;

---

📌 요약

1. 통계 조회 (pg_stat_user_indexes, pg_stat_user_tables) 로 현재 인덱스 활용도를 파악한다.
2. 실제 쿼리 (EXPLAIN ANALYZE, pg_stat_statements) 를 분석해 인덱스가 필요한 부분을 찾는다.
3. 인덱스 설계 원칙에 따라 적절한 컬럼 순서와 옵션을 선택한다.
4. 추가 후 검증 → ANALYZE, 재실행, 모니터링을 통해 효과를 확인한다.
5. 자동화 도구(HypoPG, pgBadger 등) 로 사전 검증 및 지속적인 감시를 수행한다.
6. 불필요한 인덱스는 DROP INDEX CONCURRENTLY 로 안전하게 제거한다.

이 과정을 정기적으로(예: 주간/월간) 수행하면, 읽기 성능을 최적화하면서 쓰기 부하를 최소화할 수 있습니다. Happy indexing!

문제: 숨겨진 순차 스캔

기능을 배포하고 테스트는 통과했으며 스테이징도 정상인데, 프로덕션에서는 이틀 전까지 완벽히 동작하던 쿼리가 타임아웃을 발생시키기 시작합니다:

SELECT ... WHERE customer_email = ?

테이블에 이제 1,000만 행이 들어있고, PostgreSQL이 그 모든 행을 읽고 있습니다.
인덱스가 없으므로 → 전체 순차 스캔 → 시간당 수천 번 조회되는 테이블에서 쿼리당 약 200 ms.

개발 환경에서는 테이블에 몇 백 행만 있기 때문에 순차 스캔이 마이크로초 수준으로 끝나고, 실제 데이터 양이 늘어나기 전까지는 문제를 눈치채지 못합니다.

Source: …

순차적으로 스캔되는 테이블 찾기

PostgreSQL은 pg_stat_user_tables에 스캔 통계를 기록합니다. 뷰를 조회하여 문제 테이블을 찾아보세요:

SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    CASE WHEN (seq_scan + idx_scan) > 0
        THEN round(100.0 * seq_scan / (seq_scan + idx_scan), 1)
        ELSE 0
    END AS sequential_scan_percentage,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

확인할 내용

• seq_tup_read가 높음 (순차 스캔으로 가져온 행이 많음).
• sequential_scan_percentage > 90 % (거의 모든 접근이 순차 스캔).
• idx_scan = 0 (그 테이블에 대해 인덱스를 사용하는 쿼리가 없음).

seq_tup_read가 크게 나타나면 인덱스가 없거나 사용되지 않고 있다는 신호입니다.

특정 쿼리에서 스캔 확인

Run EXPLAIN (ANALYZE, BUFFERS) for the query that is slow:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM sim_customers
WHERE customer_email = 'test@example.com';

출력에 실제 시간이 크게 표시되고 많은 버퍼가 읽힌 Seq Scan이 포함되어 있다면, 플래너가 사용할 수 있는 인덱스가 없는 것입니다.

인덱스를 만든 후(다음 섹션 참고) 동일한 명령을 다시 실행합니다. 이제 Index Scan 또는 Index Only Scan이 표시되고 버퍼 사용량이 크게 감소했을 것입니다.

누락된 인덱스 추가

읽기와 쓰기를 차단하지 않도록 동시적으로 인덱스를 생성합니다:

CREATE INDEX CONCURRENTLY idx_sim_customers_email
    ON sim_customers (customer_email);

*CONCURRENTLY*는 짧은 기간의 잠금만 획득하고, 백그라운드에서 인덱스를 구축하며, 다운타임을 방지합니다. 시간이 더 오래 걸리고 테이블을 두 번 스캔하지만, 프로덕션 테이블에 안전합니다.

앞과 동일한 EXPLAIN으로 인덱스가 사용되는지 확인하십시오; 6 400개의 버퍼(≈ 50 MB)를 읽은 순차 스캔이 이제는 몇 개만 읽을 수 있습니다.

다중 컬럼 인덱스

쿼리가 여러 컬럼을 필터링할 때, 복합 인덱스가 일반적으로 별도의 단일 컬럼 인덱스보다 효율적입니다:

CREATE INDEX CONCURRENTLY idx_orders_customer_date
    ON orders (customer_id, order_date);

컬럼 순서가 중요합니다

1. 가장 선택도가 높은 컬럼을 먼저 – 행 집합을 가장 많이 줄이는 컬럼이 위치 1이어야 합니다.
2. 범위 컬럼은 마지막에 – <, >, BETWEEN과 함께 사용되는 컬럼은 마지막에 위치해야 합니다.
3. 선행 접두사 규칙 – 플래너는 선행 컬럼(들)만을 필터링하는 쿼리에 대해 인덱스를 사용할 수 있습니다. 쿼리가 뒤쪽 컬럼만을 필터링하는 경우(예: order_date만) 인덱스를 사용할 수 없습니다.

순서가 잘못되면 플래너는 인덱스를 조용히 무시하고 순차 스캔으로 전환하며, 이는 사용되지 않는 인덱스를 유지하는 쓰기 오버헤드도 발생시킵니다.

Why a missing index hurts more than one query

• 순차 스캔은 테이블의 큰 부분을 읽어 버퍼 캐시에서 유용한 페이지를 내쫓습니다.
• 이러한 캐시 압박은 이전에 캐시된 데이터의 혜택을 받았던 관련 없는 쿼리들의 성능을 저하시킵니다.
• 테이블이 커짐에 따라 영향도 비선형적으로 증가하여 “몇 달 동안은 잘 작동하던” 쿼리를 병목으로 만들게 됩니다.

베스트 프랙티스 체크리스트