PostgreSQL 인덱스 사용 여부 확인하기
PostgreSQL 인덱스 사용 여부 확인하기 (11.12절)
인덱스를 만들어 두기만 하고 실제로 잘 쓰이고 있는지 확인하지 않으면, 저장 공간만 차지하는 낭비가 될 수 있어요. 이 절에서는 실제 쿼리 작업에서 인덱스가 제대로 활용되고 있는지 점검하는 방법을 알려드릴게요. 개별 쿼리 기준으로는 EXPLAIN 명령으로, 서버 전체 기준으로는 통계 정보를 통해 확인할 수 있답니다.
출처: 공식문서
인덱스 사용 점검이 왜 필요할까요?
PostgreSQL의 인덱스는 별도의 유지보수나 튜닝이 필요 없지만, 실제 운영 환경의 쿼리 부하에서 어떤 인덱스가 정말 사용되고 있는지는 확인해볼 필요가 있어요. 아무리 좋은 인덱스라도 실제 쿼리가 그걸 안 쓴다면 의미가 없으니까요.
- 개별 쿼리에서 인덱스가 사용되는지 볼 때는
EXPLAIN명령을 사용해요. 자세한 활용법은 14.1절에 나와 있어요. - 실행 중인 서버 전체의 인덱스 사용 통계를 수집하려면 27.2절에서 설명하는 방법을 참고하면 돼요.
어떤 인덱스를 만들지 결정하는 팁
어떤 인덱스를 만들어야 할지에 대한 일반적인 절차를 정해진 공식으로 말하기는 어려워요. 앞선 절들에서 여러 전형적인 사례를 보여드렸지만, 실제로는 많은 실험이 필요할 때가 많답니다. 다음은 그 실험을 할 때 유용한 팁들이에요.
1. 먼저 ANALYZE를 꼭 실행하세요
ANALYZE 명령은 테이블에 있는 값들의 분포에 대한 통계 정보를 수집해요. 이 통계는 쿼리가 반환할 행 수를 추정하는 데 필요하고, 플래너가 각 쿼리 계획에 현실적인 비용을 매기려면 이 정보가 있어야 해요.
통계가 하나도 없는 상태에서는 기본값이 사용되는데, 이 기본값은 거의 항상 부정확하다고 보면 돼요. 따라서 ANALYZE를 실행하지 않고 인덱스 사용 여부를 분석하는 것은 처음부터 잘못된 출발점에서 시작하는 셈이에요. 자세한 내용은 24.1.3절과 24.1.6절을 참고하세요.
2. 실험에는 실제 데이터를 사용하세요
테스트 데이터로 인덱스를 구성하면 그 테스트 데이터에 필요한 인덱스가 무엇인지만 알 수 있어요. 그 이상의 의미는 없답니다. 특히 아주 작은 테스트 데이터셋을 사용하는 것은 치명적이에요.
예를 들어 100,000개 행 중에서 1,000개를 골라내는 쿼리는 인덱스 후보가 될 만하지만, 100개 행 중에서 1개를 고르는 쿼리는 인덱스가 필요 없을 가능성이 높아요. 100개 행은 보통 디스크 페이지 하나에 다 들어가니까, 페이지 하나를 순차적으로 읽는 것보다 빠른 계획이 존재할 수 없거든요.
또한 애플리케이션이 아직 프로덕션에 투입되지 않아 테스트 데이터를 직접 만들어야 할 때는 주의가 필요해요. 값들이 서로 너무 비슷하거나, 완전히 랜덤이거나, 정렬된 순서로 삽입되면 통계가 실제 데이터 분포와 많이 달라질 수 있어요.
3. 인덱스가 안 쓰일 때는 강제로 사용해보세요
인덱스가 사용되지 않는 상황에서 테스트 목적으로 인덱스를 강제로 사용하게 만들면 원인 분석에 도움이 돼요. 실행 계획 유형을 끌 수 있는 런타임 파라미터들이 있는데, 19.7.1절에서 확인할 수 있어요.
예를 들어 가장 기본적인 계획인 순차 스캔(enable_seqscan)과 중첩 루프 조인(enable_nestloop)을 끄면 시스템이 다른 계획을 선택하도록 강제할 수 있어요. 그런데도 시스템이 여전히 순차 스캔이나 중첩 루프 조인을 선택한다면, 인덱스가 안 쓰이는 데는 더 근본적인 이유가 있을 거예요. 예를 들어 쿼리 조건이 인덱스와 맞지 않는 경우가 대표적이죠. (어떤 쿼리가 어떤 인덱스를 사용할 수 있는지는 앞선 절들에서 설명했어요.)
4. 강제로 썼더니 인덱스가 사용된다면?
인덱스 사용을 강제했더니 실제로 인덱스를 탄다면 두 가지 가능성이 있어요.
- 시스템 판단이 맞는 경우: 인덱스를 쓰는 것이 실제로 적절하지 않은 상황
- 비용 추정이 현실을 반영하지 못하는 경우: 쿼리 계획의 비용 계산이 잘못된 상황
이때는 인덱스를 쓸 때와 안 쓸 때의 쿼리 시간을 직접 측정해보는 게 좋아요. EXPLAIN ANALYZE 명령이 여기서 유용하게 쓰인답니다.
5. 비용 추정이 틀렸다면?
비용 추정이 잘못된 것으로 판명되면, 역시 두 가지 가능성이 있어요.
- 전체 비용은 각 계획 노드의 행당 비용에 해당 노드의 선택도 추정치를 곱해서 계산돼요. 계획 노드의 비용은 런타임 파라미터로 조정할 수 있어요. (19.7.2절 참고)
- 선택도 추정이 부정확한 경우는 통계가 충분하지 않다는 뜻이에요. 통계 수집 파라미터를 튜닝하면 개선될 수 있는데,
ALTER TABLE명령으로 조정할 수 있어요.
비용을 적절하게 조정하는 데 계속 실패한다면, 인덱스 사용을 명시적으로 강제하는 방법을 써야 할 수도 있어요. 그 경우 PostgreSQL 개발자들에게 문의해서 문제를 검토받는 것도 한 가지 방법이랍니다.