본문 바로가기
Q&A해결2024년 7월 25일2분 읽기

PostgreSQL 인덱스를 어떤 컬럼에 걸어야 할까요

개
개발자준호
조회 764댓글 4

PostgreSQL에서 쿼리가 느려지기 시작해서 인덱스를 걸려고 합니다. 테이블 구조는 이렇습니다:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  status VARCHAR(20),   -- pending, paid, shipped, delivered
  amount DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT NOW()
);
-- 약 50만 건의 데이터

주로 사용하는 쿼리:

SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC;
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

어떤 컬럼에 인덱스를 걸어야 하고, 복합 인덱스는 언제 쓰는 건가요?

이전 글
REST API 설계 시 URL 네이밍 컨벤션 질문
다음 글
Docker Compose 볼륨 마운트 질문

댓글 4

김김영삼2년 전

쿼리 패턴을 보고 인덱스를 설계해볼게요.


-- 쿼리 1: user_id로 조회 + created_at 정렬
-- → 복합 인덱스 (user_id, created_at DESC)
CREATE INDEX idx_orders_user_created 
ON orders (user_id, created_at DESC);

-- 쿼리 2: status + created_at 범위 조회
-- → 복합 인덱스 (status, created_at)
CREATE INDEX idx_orders_status_created 
ON orders (status, created_at);

-- 쿼리 3: user_id로 GROUP BY
-- → 쿼리 1의 인덱스가 커버

인덱스 설계 원칙:



  • WHERE 절에 자주 쓰이는 컬럼에 인덱스

  • 카디널리티(고유값 수)가 높은 컬럼이 효과적 (user_id > status)

  • 복합 인덱스는 왼쪽부터 매칭되므로 순서가 중요

  • ORDER BY 컬럼도 인덱스에 포함하면 정렬 비용 절약


적용 후 EXPLAIN ANALYZE로 실제 효과를 확인해보세요!

개개발자준호김영삼2년 전

감사합니다! EXPLAIN ANALYZE 해봤는데 쿼리 1이 Seq Scan에서 Index Scan으로 바뀌면서 200ms → 3ms로 줄었습니다. 그런데 인덱스를 너무 많이 걸면 INSERT/UPDATE가 느려진다고 하던데 몇 개까지 괜찮은 건가요?

김김영삼개발자준호2년 전

200ms → 3ms면 극적인 개선이네요! 인덱스 수에 정해진 제한은 없지만 경험적으로:



  • 읽기 위주 테이블: 5-10개도 괜찮음

  • 쓰기 많은 테이블: 3-5개 이내 권장

  • 사용하지 않는 인덱스는 오히려 성능 저하


-- 사용하지 않는 인덱스 확인
SELECT indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE schemaname = 'public' 
ORDER BY idx_scan ASC;

idx_scan이 0인 인덱스는 제거를 고려해보세요.

개개발자준호김영삼2년 전

미사용 인덱스 확인 쿼리까지! 정말 실용적인 답변 감사합니다. 덕분에 DB 최적화 방향이 잡혔습니다 👍

Ctrl+Enter로 등록