[Postgresql] Scan과 Join

 

/* PostgreSQL 실행 계획 속 스캔과 조인*/

PostgreSQL에서 SQL이 실행될 때, 단순히 "조건에 맞는 데이터 가져오기"로 끝나는 게 아니다.
실제로는 어떤 방식으로 테이블을 읽을지(스캔), 그리고 여러 테이블을 어떻게 결합할지(조인)를 내부적으로 전략 세워서 실행한다.
이 전략을 이해하면, 쿼리 튜닝이 한결 쉬워진다.

 

/* 스캔 방식: 데이터를 어떻게 읽을까 */

 

• Sequential Scan (Seq Scan)
  • 테이블의 모든 블록을 처음부터 끝까지 훑는 방식.
  • 조건이 있든 없든 전부 읽고, 거기서 필터링한다.
  • 인덱스가 없어도 되고, 작은 테이블이거나 조건으로 인덱스를 타도 효율이 없을 때 선택됨.
  • 예: SELECT * FROM orders WHERE amount > 1000; (amount에 인덱스 없을 때)
• Index Scan
  • 인덱스를 이용해 조건에 맞는 튜플 위치를 찾고, 해당 위치의 데이터를 테이블에서 가져온다.
  • 랜덤 I/O가 많을 수 있지만, 조건에 맞는 데이터가 적을 때 빠르다.
  • 예: SELECT * FROM orders WHERE id = 10; (id에 PK 인덱스 존재)

[동작흐름]
인덱스 → TID 목록 → Heap(테이블)에서 행 읽기

• Bitmap Index Scan
  • 인덱스를 이용하되, 튜플 위치를 한 번에 모아 비트맵으로 관리한 뒤, 해당 페이지를 순차적으로 읽는다.
  • 랜덤 I/O를 줄이기 위해 여러 인덱스 결과를 합칠 때도 쓰인다. (BitmapAnd, BitmapOr)
  • 예: WHERE city = 'Seoul' AND age > 30 (city, age 각각 인덱스 있음)
• Index Only Scan
  • 인덱스에서 필요한 모든 데이터를 얻을 수 있을 때, 테이블 접근을 생략하는 방식.
  • 테이블에 접근하지 않으니 훨씬 빠르다. 단, Visibility Map 덕분에 "이 튜플이 최신 버전"임을 알 수 있어야 가능하다.
  • 예: SELECT id FROM orders WHERE id = 10; (id가 PK라 인덱스에 id 전부 있음)
  • VACUUM이 제대로 안 돌면 Index Only Scan 못 쓸 수 있음

[동작흐름]
인덱스 → 바로 결과 반환 (Heap 접근 없음)

 

 

/* 조인 방식: 테이블을 어떻게 합칠까 */

1. Nested Loop Join
   • 한쪽 테이블의 한 행마다 다른 쪽 테이블을 검색한다.
   • 소규모 테이블 조인 시 효율적이며, 인덱스와 찰떡궁합.
   • 예: 고객 한 명씩 주문 목록을 조회하는 경우.
2. Hash Join
   • 한쪽 테이블을 해시 테이블로 만들고, 다른 테이블을 순회하면서 매칭되는 해시 값을 찾는다.
   • 대규모 테이블, 인덱스 없을 때 강력.
   • 예: 두 대형 로그 테이블을 특정 키로 조인.
3. Merge Join (Sort-Merge Join)
   • 양쪽 테이블을 조인 키로 정렬한 뒤, 병합하듯 결합.
   • 이미 정렬된 인덱스를 활용하면 효율적.
   • 예: 날짜순으로 정렬된 주문과 배송 데이터를 합칠 때.

/* 튜닝 */

# 실행 계획 확인

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

실행 계획을 보면 PostgreSQL이 어떤 스캔, 어떤 조인 방식을 썼는지 확인이 가능하다.

 

index-scan 유도

CREATE TABLE orders(
  id bigserial PRIMARY KEY,
  customer_id int,
  city text,
  amount numeric,
  created_at timestamp
);
CREATE INDEX idx_orders_city_amount ON orders(city, amount);
ANALYZE orders;

-- 인덱스 스캔 유도
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM orders
WHERE city = 'Seoul' AND amount > 1000
LIMIT 50;

 

index only scan 유도

-- 필요한 컬럼이 전부 인덱스에 있어야 함
CREATE INDEX idx_orders_city_amount_id ON orders(city, amount, id);

VACUUM (ANALYZE) orders; -- VM 업데이트
EXPLAIN (ANALYZE, BUFFERS)

SELECT id FROM orders
WHERE city='Seoul' AND amount > 1000;