[PostgreSQL] 컬럼 간 상관관계가 파티션별 실행 계획에 미치는 영향

주문일(order_date)을 기준으로 나눈 테이블을 출고일(ship_date)로 조회했는데, 파티셔닝하지 않은 테이블보다 읽은 데이터가 크게 줄었다.

파티션 키를 조건에 넣지 않았는데 왜 이런 차이가 생겼을까? 이 글은 실제 업무에서 겪은 성능 문제와 실행 계획을 분석한 경험을 바탕으로 작성했다.

1. 문제 상황

주문 테이블을 주문 연도별로 나누었다고 가정해 보자.

orders
├── orders_2020: 2020년에 주문한 데이터
├── orders_2021: 2021년에 주문한 데이터
├── orders_2022: 2022년에 주문한 데이터
├── orders_2023: 2023년에 주문한 데이터
└── orders_2024: 2024년에 주문한 데이터

파티션을 나누는 기준은 order_date지만, 조회 조건에는 ship_date만 사용했다.

SELECT count(*)
FROM orders
WHERE status = 'CANCELED'
  AND amount >= 100000
  AND ship_date >= DATE '2024-01-01'
  AND ship_date <  DATE '2025-01-01';

같은 데이터를 단일 테이블과 파티션 테이블에 구성하고 비교한 결과는 다음과 같았다.

구성	shared read	읽기량 환산	실행 시간
단일 테이블 + 인덱스	2,367,444 blocks	약 18 GiB	5분 초과 또는 타임아웃
연도별 파티션 + 파티션별 인덱스	440,442 blocks	약 3.4 GiB	약 57초

파티션 구성에서 읽은 블록이 약 5.4배 적었다.

두 구성은 캐시 영향을 줄인 조건에서 비교했고 shared hit은 거의 없었다. 단일 테이블의 전체 BUFFERS 값은 타임아웃을 해제한 뒤 별도로 끝까지 실행해 확인했다.

이 결과만 보고 PostgreSQL이 출고일을 보고 주문 연도 파티션을 자동으로 골랐다고 생각하기 쉽다. 하지만 실제 동작은 조금 다르다.

2. 먼저 알아둘 점: 파티션 프루닝은 아니다

파티션 프루닝(partition pruning)은 조건에 맞지 않는 파티션을 실행 대상에서 아예 제외하는 기능이다.

예를 들어 다음 조건은 파티션 키인 order_date를 직접 사용한다.

WHERE order_date >= DATE '2024-01-01'
  AND order_date <  DATE '2025-01-01'

PostgreSQL은 파티션의 범위 정보를 보고 orders_2020부터 orders_2023까지는 조건을 만족할 수 없다고 판단할 수 있다. 따라서 이 파티션들을 조회하지 않아도 된다.

하지만 이 글의 쿼리에는 ship_date 조건만 있다.

WHERE ship_date >= DATE '2024-01-01'
  AND ship_date <  DATE '2025-01-01'

PostgreSQL은 파티션 정의만 보고 orders_2020에 2024년 출고 데이터가 절대로 없다고 증명할 수 없다. 2020년에 주문한 상품이 아주 늦게 출고되었을 수도 있기 때문이다.

따라서 이 경우에는 다음처럼 구분해야 한다.

• 파티션 프루닝: 필요 없는 파티션을 실행 대상에서 제외한다.
• 이 글의 사례: 모든 파티션을 확인하지만, 관련 없는 파티션에서는 인덱스를 짧게 확인하고 0건을 반환한다.

즉, 조회가 빨라진 이유를 파티션 프루닝으로 설명하면 안 된다.

3. 주문일과 출고일은 보통 함께 움직인다

일반적인 주문 시스템에서는 주문일과 출고일이 완전히 무관하지 않다.

주문일	출고일	저장되는 파티션
2023-12-30	2024-01-02	orders_2023
2024-01-03	2024-01-04	orders_2024
2024-06-10	2024-06-12	orders_2024

대부분의 상품은 주문 후 며칠 안에 출고되므로, 2024년에 출고된 주문 대부분은 orders_2024에 들어 있을 가능성이 높다. 연말 주문처럼 일부 데이터만 이전 연도 파티션에 있을 수 있다.

이것은 PostgreSQL이 두 날짜의 관계를 특별히 이해해서 생기는 일이 아니다. 실제 업무 데이터가 그런 모양으로 저장되어 있기 때문에 생긴다.

4. 파티션마다 출고일 인덱스가 있다

파티션 부모 테이블에 다음 인덱스를 만들었다고 가정하자.

CREATE INDEX idx_orders_ship_date ON orders (ship_date);

PostgreSQL의 선언적 파티셔닝에서는 부모에 만든 파티션 인덱스를 통해 각 파티션에 대응하는 인덱스를 둘 수 있다. 그러면 ship_date로 조회할 때 각 파티션은 자신의 인덱스를 사용할 수 있다.

실행 흐름을 단순하게 표현하면 다음과 같다.

orders_2020의 ship_date 인덱스 확인 → 2024년 데이터 0건
orders_2021의 ship_date 인덱스 확인 → 2024년 데이터 0건
orders_2022의 ship_date 인덱스 확인 → 2024년 데이터 소수
orders_2023의 ship_date 인덱스 확인 → 2024년 데이터 일부
orders_2024의 ship_date 인덱스 확인 → 2024년 데이터 대부분

모든 파티션을 방문하기는 하지만, 관련 없는 파티션에서는 인덱스의 일부만 확인한 뒤 빠르게 끝날 수 있다.

5. 실제 비용은 데이터 페이지를 많이 읽을 때 커진다

인덱스는 책의 목차와 비슷하다. 조건에 맞는 행의 위치를 알려 주지만, status와 amount 같은 다른 조건까지 확인하려면 실제 테이블 데이터 페이지를 읽어야 할 수 있다.

WHERE status = 'CANCELED'
  AND amount >= 100000

PostgreSQL은 테이블과 인덱스를 페이지 또는 블록 단위로 읽는다. 조건에 맞는 행이 많고 넓게 퍼져 있다면 읽어야 할 데이터 페이지도 늘어난다.

이 사례의 실행 계획에서는 2024년 출고 데이터가 거의 없는 파티션이 적은 수의 인덱스 블록만 확인하고 끝났다. 반대로 실제 행이 많은 파티션에 데이터 페이지 읽기가 집중되었다.

공식 문서에서도 자주 조회하는 행이 하나 또는 소수의 파티션에 모여 있으면 파티셔닝이 성능에 도움이 될 수 있다고 설명한다. 또한 한 파티션에서 많은 행을 읽어야 한다면 여러 번 흩어진 인덱스 접근을 하는 대신 순차 스캔 같은 다른 방법을 선택할 수도 있다.

다만 파티셔닝 자체가 파티션 내부의 행을 날짜순으로 정렬해 주는 것은 아니다. 또한 위의 5.4배 차이가 오직 파티셔닝 하나 때문에 발생했다고 단정할 수는 없다. 테이블과 인덱스 크기, 데이터 분포, 테이블 팽창, 캐시 상태, 선택된 실행 계획도 결과에 영향을 준다.

이 글에서 설명하는 것은 실제 EXPLAIN (ANALYZE, BUFFERS) 결과에서 확인한 동작이다. 다른 시스템에서도 같은 효과가 나는지는 반드시 직접 측정해야 한다.

6. 파티션마다 별도의 통계와 읽기 방법이 있다

각 파티션은 실제 데이터를 저장하는 별도 테이블이다. 따라서 각 파티션은 자신의 데이터와 인덱스, 통계 정보를 가진다.

ANALYZE orders;

ONLY를 사용하지 않고 파티션 부모 테이블을 ANALYZE하면 하위 파티션도 함께 처리된다. 수집한 통계는 PostgreSQL이 각 읽기 방법의 비용과 예상 행 수를 계산할 때 사용한다.

예를 들어 데이터가 다음처럼 분포할 수 있다.

• orders_2020: 출고일도 대부분 2020년이다.
• orders_2023: 일부 연말 주문이 2024년에 출고되었다.
• orders_2024: 출고일이 대부분 2024년이다.

그러면 PostgreSQL은 파티션마다 서로 다른 읽기 방법을 선택할 수 있다.

• 조건에 맞는 행이 거의 없는 파티션: 짧은 Index Scan
• 조건에 맞는 행이 많은 파티션: 상황에 따라 Bitmap Heap Scan 또는 Seq Scan

여러 파티션의 결과는 보통 Append 노드에서 합쳐진다. Append 아래에 서로 다른 스캔 방식이 함께 나타날 수 있다.

7. 데이터가 실제로 몰려 있는지 확인하기

먼저 2024년 출고 데이터가 어느 주문 연도에 들어 있는지 확인해 보자.

SELECT date_part('year', order_date) AS order_year,
       count(*) AS row_count
FROM orders
WHERE ship_date >= DATE '2024-01-01'
  AND ship_date <  DATE '2025-01-01'
GROUP BY 1
ORDER BY 1;

결과가 다음과 비슷하다면 2024년 출고 데이터가 특정 파티션에 집중되어 있는 것이다.

order_year | row_count
-----------+----------
2020       |         0
2021       |         0
2022       |        15
2023       |     3,200
2024       | 8,500,000

반대로 모든 주문 연도에 2024년 출고 데이터가 고르게 퍼져 있다면, 관련 없는 파티션에서 빠르게 0건을 찾는 효과는 기대하기 어렵다.

8. 실행 계획에서 확인하기

추측만 하지 말고 실제 실행 계획과 버퍼 사용량을 확인해야 한다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM orders
WHERE status = 'CANCELED'
  AND amount >= 100000
  AND ship_date >= DATE '2024-01-01'
  AND ship_date <  DATE '2025-01-01';

다음 항목을 순서대로 살펴본다.

1. 실행 계획에 모든 파티션이 나타나는가?
2. 관련 없는 파티션의 actual rows가 0 또는 아주 작은가?
3. 그 파티션들의 Buffers 값도 작은가?
4. 실제 읽기가 특정 파티션에 집중되는가?

단순화한 실행 계획은 다음과 같은 모양이 될 수 있다.

Append
├── Index Scan on orders_2020 (actual rows=0)
├── Index Scan on orders_2021 (actual rows=0)
├── Index Scan on orders_2022 (actual rows=소수)
├── Index Scan on orders_2023 (actual rows=일부)
└── Bitmap Heap Scan on orders_2024 (actual rows=대부분)

자주 보는 항목의 뜻은 다음과 같다.

• rows: 실행 전에 예상한 행 수
• actual rows: 실제로 처리한 행 수
• shared hit: 필요한 블록이 PostgreSQL의 공유 버퍼에 이미 있었던 횟수
• shared read: 공유 버퍼에 없어서 읽기를 요청한 블록 수

shared read가 곧 물리 디스크에서 직접 읽은 횟수라는 뜻은 아니다. 운영체제 파일 캐시가 데이터를 제공했을 수도 있다. 따라서 두 결과를 비교할 때는 캐시 상태와 실행 조건을 가능한 한 동일하게 맞춰야 한다.

또한 EXPLAIN ANALYZE는 쿼리를 실제로 실행한다. UPDATE나 DELETE처럼 데이터를 바꾸는 쿼리를 확인할 때는 트랜잭션에서 실행한 뒤 롤백하는 등 주의가 필요하다.

9. 언제 효과가 있고, 언제 없을까?

다음 조건에서는 이 사례와 비슷한 결과가 나올 가능성이 있다.

• 주문일과 출고일처럼 두 값이 업무적으로 함께 움직인다.
• 조회할 컬럼의 인덱스가 각 파티션에 있다.
• 조건에 맞는 실제 행이 하나 또는 소수의 파티션에 몰려 있다.
• ANALYZE가 실행되어 통계가 최신 상태다.

반대로 다음 경우에는 효과가 작거나 없을 수 있다.

• 출고일 데이터가 모든 주문 연도 파티션에 고르게 흩어져 있다.
• 조회할 컬럼의 인덱스가 없다.
• 모든 파티션에서 조건에 맞는 행을 많이 읽어야 한다.
• 통계가 오래되어 예상 행 수가 실제와 크게 다르다.

파티셔닝은 만능 최적화가 아니다. 데이터 분포와 자주 사용하는 조회 조건에 맞는지 확인한 뒤 선택해야 한다.

10. 정리

이번 사례를 간단히 정리하면 다음과 같다.

1. 조회 조건에 파티션 키가 없으므로 파티션 프루닝은 일어나지 않는다.
2. 주문일과 출고일이 업무적으로 함께 움직여 2024년 출고 데이터가 일부 파티션에 몰려 있었다.
3. 관련 없는 파티션은 출고일 인덱스를 짧게 확인하고 0건을 반환했다.
4. 실제 데이터 페이지 읽기는 조건에 맞는 행이 많은 파티션에 집중되었다.
5. 이 효과는 보장되지 않으므로 데이터 분포와 EXPLAIN (ANALYZE, BUFFERS)를 함께 확인해야 한다.

핵심은 PostgreSQL이 두 날짜의 관계를 자동으로 알아냈다는 것이 아니다. 실제 데이터가 파티션별로 어떻게 나뉘어 있는지, 각 파티션에서 어떤 인덱스와 실행 계획을 사용하는지가 읽기량의 차이를 만들었다.

11. 참고 자료

• PostgreSQL 공식 문서: Table Partitioning — 파티션의 저장 구조, 파티션별 인덱스, 파티션 프루닝의 동작 기준
• PostgreSQL 공식 문서: ANALYZE — 플래너 통계의 용도와 파티션별 통계 수집 방식
• PostgreSQL 공식 문서: Using EXPLAIN — 예상 행 수와 실제 행 수를 포함한 실행 계획 해석 방법
• PostgreSQL 공식 문서: EXPLAIN — ANALYZE, BUFFERS, shared hit, shared read의 의미
• PostgreSQL 공식 문서: Parallel Plans — 여러 하위 스캔의 결과를 합치는 Append 계열 노드