PostgreSQL: SELECT 효율 분석

※ PostgreSQL: Analysis of SELECT Efficiency.

 

안녕하세요. 듀스트림입니다.

 

대부분의 서비스에서는 SELECT(Read) 비율이 압도적으로 많습니다.

 

그렇다면 여기서 의문이 생기겠죠.

• 인덱스를 타긴 타는데, 정말 효율적으로 동작하고 있을까?
• 어떤 테이블과 어떤 쿼리가 Heap 접근을 많이 유발 중일까?
• Visibility Map, Index-only Scan, 통계 뷰 지표들은 서로 어떻게 연결되는 거지?

 

오늘 포스팅에서는 로그와 모니터링에 의존하지 않고 시스템 뷰와 통계 지표만으로 SELECT 효율을 해석하는 방법을 알려드리겠습니다.

---

1. SELECT 효율?

서비스 쿼리는 기본적으로 응답 시간이 빨라야합니다.

 

SELECT 효율 분석은 여기서 한 뎁스 더 들어가서 다음 내용을 정량적 지표로 보는 것입니다.

1. 얼마나 많은 튜플을 읽었는지 (tup_returned / tup_fetched / seq_tup_read / idx_tup_fetch)
2. 그 과정에서 Heap(실제 테이블 페이지)에 얼마나 자주 접근했는지
3. 인덱스만으로 해결(Index-only scan)하고 있는 부분은 얼마나 되는지
4. 이 부하가 특정 테이블·쿼리에 집중되어 있는지

 

분석을 위해 우리는 PostgreSQL에서 제공하는 아래 누적 통계 시스템 뷰들을 활용해야합니다.

• pg_stat_database
• pg_stat_all_tables / pg_stat_user_tables
• pg_stat_all_indexes
• pg_statio_user_tables
• pg_stat_statements (Extension)

---

2. DB 전체 관점: tup_returned vs tup_fetched

2.1 정의

pg_stat_database에는 SELECT 관련 핵심 지표 두 개가 있습니다.

• tup_returned: 시퀀스 스캔 + 인덱스 스캔을 통해 반환된 전체 라이브 튜플 수
• tup_fetched: 인덱스 스캔을 통해 Heap에서 Fetch된 라이브 튜플 수

직관적으로 보면:

• tup_returned = SELECT가 클라이언트에게 얼마나 많은 로우를 내보냈는가
• tup_fetched = 그중 인덱스를 타고 실제 Heap까지 내려가 확인한 로우 수

 

공식 문서 및 관련 논의에서는 대략 아래 관계가 소개됩니다.

• tup_returned ≈ seq_tup_read(모든 테이블의 합) + idx_tup_read(모든 인덱스의 합)
• tup_fetched ≈ idx_tup_fetch(모든 테이블의 합)

27.2. The Cumulative Statistics System

---

2.2 활용 예시

SELECT datname,
       tup_returned,
       tup_fetched,
       round( case when tup_returned = 0
                   then 0
                   else 100.0 * tup_fetched / tup_returned end, 2) AS fetched_ratio_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY tup_returned DESC;

이 비율을 확인했을 때,

• 너무 낮다 → 대부분이 시퀀스 스캔(또는 결과가 적은 쿼리)
• 너무 높다 → 인덱스 기반 Heap 접근이 매우 많은 워크로드

여기서는 "좋다/나쁘다"가 아니라 패턴을 보는 용도입니다. (실제 평가는 테이블·쿼리 단위에서 이어집니다.)

---

3. 테이블 관점: seq_tup_read와 idx_tup_fetch

개별 테이블의 SELECT 효율은 pg_stat_all_tables로 보는 것이 기본입니다.

SELECT schemaname,
       relname,
       seq_scan,
       seq_tup_read,
       idx_scan,
       idx_tup_fetch
FROM pg_stat_all_tables
ORDER BY (seq_tup_read + idx_tup_fetch) DESC;

여기서 중요한 컬럼은 다음과 같습니다:

• seq_scan / seq_tup_read: 전체 테이블 스캔 횟수 및 그때 읽힌 라이브 튜플 수
• idx_scan: 인덱스 스캔 시작 횟수
• idx_tup_fetch: 인덱스 스캔을 통해 Heap에서 실제 Fetch된 라이브 튜플 수

 

해석 포인트

1. seq_tup_read가 비정상적으로 크다:
   • WHERE 절 조건 부적절
   • 인덱스 누락
   • 통계 부정확으로 인한 잘못된 플랜 선택
2. idx_scan은 많은데 idx_tup_fetch가 너무 크다:
   • 인덱스를 잘 타지만 결과 집합이 커서 Heap I/O가 많이 발생
   • range scan, 넓은 조건, 설계 이슈 등을 의심
3. idx_tup_fetch << idx_tup_read (인덱스 레벨, 아래 4 참조)
   • index-only scan, dead tuple, VM 상태 등의 영향

❗이 단계에서 어느 테이블이 부하의 중심인지를 빠르게 찾을 수 있습니다.

---

4. 인덱스 관점: idx_tup_read vs idx_tup_fetch

pg_stat_all_indexes를 보면 인덱스 사용 효율을 더 명확히 볼 수 있습니다.

 

핵심 컬럼은 다음과 같습니다:

• idx_scan: 인덱스 스캔 횟수
• idx_tup_read: 인덱스에서 읽은 인덱스 엔트리 수
• idx_tup_fetch: simple index scan으로 인해 실제 Heap에서 Fetch된 라이브 튜플 수

 

중요 포인트는

• bitmap scan의 Heap fetch는 테이블 쪽 idx_tup_fetch(pg_stat_all_tables)에만 반영되고, 인덱스 쪽 idx_tup_fetch에는 반영되지 않습니다.
• idx_tup_read ≥ idx_tup_fetch 가 일반적이며,
  • dead/not-visible 튜플 때문에 Heap fetch는 줄어들 수 있습니다.
  • index-only scan으로 Heap을 안 타는 경우도 있기 때문입니다.

 

즉:

• idx_tup_read → 인덱스 레벨의 후보 로우
• idx_tup_fetch → 그 중 실제 테이블까지 내려간 라이브 로우

❗이 둘의 차이를 보면 인덱스 품질, dead tuple, index-only scan 효과를 유추할 수 있습니다.

---

5. 쿼리 관점: pg_stat_statements

어느 쿼리가 부하를 일으키는지 보려면 pg_stat_statements 확장을 사용하는 것이 가장 효과적입니다.

-- 설정: postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- compute_query_id = auto

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query,
       calls,
       rows,               -- 반환/영향 받은 전체 로우 수
       mean_exec_time,
       shared_blks_hit,
       shared_blks_read
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 20;

 

여기서:

• rows는 SELECT의 결과 로우(및 DML 영향 로우)를 포함
• shared_blks_read / hit와 함께 보면
  • 많은 로우를 반환하면서 디스크 I/O도 큰 쿼리,
  • 적은 로우 반환인데 이상하게 I/O가 큰 쿼리를 빠르게 식별할 수 있습니다.

 

실제 튜닝 순서는:

1. pg_stat_all_tables로 문제 테이블 후보 찾고
2. pg_stat_statements에서 해당 테이블을 많이 읽는 쿼리 찾기

---

6. I/O 관점: pg_statio_user_table - 캐시 효율 확인

Heap/Index 블록 단위 I/O는 pg_statio_user_tables로 확인합니다.

SELECT relname,
       heap_blks_read,
       heap_blks_hit,
       idx_blks_read,
       idx_blks_hit,
       round(
         100.0 * heap_blks_hit
         / NULLIF(heap_blks_hit + heap_blks_read, 0),
         2
       ) AS heap_hit_ratio_pct
FROM pg_statio_user_tables
ORDER BY (heap_blks_read + heap_blks_hit) DESC;

 

 

• Heap hit 비율이 낮고 idx_tup_fetch가 큰 테이블 → 인덱스는 타지만 Heap I/O를 매번 디스크에서 읽는 구조일 수 있음
• 이 경우:
  • 인덱스 조건 개선
  • 자주 쓰는 데이터의 working set 크기 확인
  • autovacuum / 통계 갱신 / 클러스터링 고려

---

7. Visibility Map과 idx_tup_fetch: 흔한 오해 정리

인덱스 스캔을 통해 heap에서 fetch된 튜플 수(idx_tup_fetch)가 Visibility Map 갱신이 안 돼서 발생한 튜플 수와는 다릅니다.

 

idx_tup_fetch의 의미

• idx_tup_fetch는 인덱스 스캔을 수행하면서 실제 Heap 페이지에 접근해 읽어온 "라이브 튜플"의 수입니다.
• 이는 정상적인 MVCC 가시성 검증의 결과이며, VM 오류나 지연을 뜻하지 않습니다.

 

즉,

• idx_tup_fetch 자체는 VM이 안 돼서 생긴 비용이 아니라, 그냥 인덱스 기반 SELECT가 Heap까지 내려간 정상적인 카운트입니다.
• idx_tup_fetch가 높으면 인덱스는 많이 쓰이는데 Heap 접근도 많이 발생하는 상태라 range 쿼리, 낮은 selectivity, VM/IO/캐시 상태를 함께 보며 튜닝 계획을 잡으시면 됩니다.

 

❗이건 Index Only Scan에서 발생하는 Heap Fetches와는 다른 개념입니다.

Index Only Scan은:
  1. 인덱스에서 후보 TID를 찾고,
  2. 해당 Heap 페이지가 VM에서 all-visible이면 → Heap을 보지 않고 인덱스 값만으로 튜플 반환
  3. all-visible이 아니면 → Heap 튜플을 실제로 읽어 가시성 확인

즉, VM이 잘 관리될수록 Index Only Scan에서 발생하는 Heap Fetches와 idx_tup_fetch 수치가 줄어듭니다.

---

8. SELECT 효율 분석 워크플로우

1. 통계 리셋

SELECT pg_stat_reset();

 

2. 일정 시간 단위로 체크

-- DB 전체 조회 패턴 확인
SELECT datname, tup_returned, tup_fetched
FROM pg_stat_database;


-- 테이블별 조회 부하 상위 N 추출
SELECT schemaname, relname,
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_all_tables
ORDER BY (seq_tup_read + idx_tup_fetch) DESC
LIMIT 20;

 

3. 문제 테이블에 대한 인덱스/쿼리 분석

• pg_stat_all_indexes로 불필요한 인덱스/비효율 인덱스 확인
• pg_stat_statements에서 해당 테이블을 많이 읽는 쿼리 파악
• pg_statio_user_tables로 Heap/Index hit ratio 확인
• 필요시 VACUUM, ANALYZE, 커버링 인덱스, 쿼리 리라이트 적용
• Fetch 관련 지표 변화를 다시 비교

---

오늘은 여기까지~