PostgreSQL 실행 계획(Execution Plan) 이해하기

9. Rows

rows=5000

EXPLAIN에서 Rows는 Optimizer가 예상한 해당 Node가 반환할 Row 수다.
여기서 중요한 부분은

읽은 Row 수가 아니라 Node가 위쪽 Node로 전달할 것으로 예상하는 Row 수다.

예를 들어 테이블에 100만 건이 있지만

WHERE status = 'ERROR'

조건을 통과하는 데이터가 5,000건이라고 예상한다면

rows=5000

이 나올 수 있다.
실행계획 분석에서 Rows는 매우 중요하다.
왜냐하면 Optimizer는

얼마나 많은 Row가 나올 것인가?

를 기준으로

Index Scan vs Seq Scan

Nested Loop vs Hash Join

Join 순서

Sort 비용

등을 결정하기 때문이다.
PostgreSQL 공식 문서에서도 rows를 Plan Node가 처리한 전체 Row 수가 아니라 Node가 출력할 것으로 예상되는 Row 수라고 설명한다.

10. Estimated Rows와 Actual Rows

EXPLAIN ANALYZE를 사용하면 가장 중요한 비교가 가능해진다.
예를 들어

rows=5000

이라고 예상했지만

actual rows=24870

이 나왔다고 하자.
Optimizer는

약 5,000건

이라고 생각했지만 실제로는

약 25,000건

이었다.
약 5배의 오차가 발생한 것이다.
이 차이가 커지면 Optimizer가 잘못된 판단을 내릴 가능성이 높아진다.
예를 들어 Optimizer가

결과가 10건밖에 안 나오겠네.

Nested Loop가 좋겠다.

라고 판단했는데 실제 결과가

100,000건

이라면 Nested Loop가 엄청난 반복 작업을 발생시킬 수 있다.
따라서 실행계획을 볼 때 가장 먼저 확인할 가치가 높은 것 중 하나가

Estimated Rows
vs
Actual Rows

이다.
PostgreSQL 공식 문서 역시 실제 실행 시간 그 자체뿐 아니라 Planner의 Row 추정치가 실제 Row 수와 얼마나 가까운지 확인하는 것이 중요하다고 설명한다.

11. Width

width=64

Width는 해당 Node가 반환하는 한 Row의 평균 크기를 Byte 단위로 추정한 값이다.
예를 들어

rows=10000
width=64

라면 대략적으로

10,000 Rows × 64 Bytes

정도의 데이터가 다음 Node로 전달될 것으로 예상하고 있다는 의미다.
Width 자체만 보고 성능 문제라고 판단하는 경우는 많지 않다.
하지만 Row가 많고 Width까지 크다면

Sort
Hash
Join
Memory
Temporary File

등의 비용이 커질 수 있다.
그래서 불필요한

SELECT *

가 대량 조회에서는 문제가 될 수 있는 이유 중 하나이기도 하다.

12. Actual Time

EXPLAIN ANALYZE를 실행하면 다음 값이 추가된다.

actual time=0.021..5.321

구조는 Cost와 비슷하다.

actual time =
첫 Row를 반환하기까지 걸린 시간
..
모든 Row를 반환하기까지 걸린 시간

단위는 ms다.
따라서

actual time=0.021..5.321

이라면 대략

첫 Row 반환

0.021ms


해당 Node 처리 완료

5.321ms

라고 이해하면 된다.
여기서 Cost와 Actual Time을 직접 비교하면 안 된다.

cost = arbitrary cost unit

actual time = milliseconds

이기 때문이다.

13. Loops

다음 실행계획을 보자.

Index Scan using orders_user_id_idx on orders
(actual time=0.010..0.015 rows=3 loops=1000)

loops=1000은 해당 Node가 1,000번 실행되었다는 의미다.
이 값은 특히 Nested Loop에서 중요하다.
예를 들어 다음 구조가 있다고 하자.

Nested Loop

    Seq Scan users
        actual rows=1000

    Index Scan orders
        loops=1000

동작을 단순화하면

users 첫 번째 Row
    ↓
orders Index Scan

users 두 번째 Row
    ↓
orders Index Scan

users 세 번째 Row
    ↓
orders Index Scan

...

1000번

과 같은 구조다.
따라서 Index Scan 한 번이 아무리 빨라도

0.1ms × 100,000 loops

처럼 실행된다면 전체 성능에는 큰 영향을 줄 수 있다.
PostgreSQL에서 loops > 1인 경우 표시되는 actual time과 rows는 한 번 실행당 평균값이다. 전체 실행량을 이해하려면 loops를 함께 봐야 한다.
단, 각 Node의 시간을 단순히 전부 더하면 안 된다. 부모 Node의 시간에는 자식 Node에서 소비된 시간이 포함될 수 있기 때문이다.

14. 결국 실행계획에서 무엇을 봐야 할까?

예를 들어 다음과 같은 실행계획이 있다고 하자.

Nested Loop
(cost=10..500 rows=100)
(actual time=1..3000 rows=100000 loops=1)

    Seq Scan on users
    (cost=0..100 rows=100)
    (actual time=0.01..100 rows=100000 loops=1)

    Index Scan on orders
    (cost=0.1..2 rows=1)
    (actual time=0.01..0.02 rows=1 loops=100000)

여기서 단순히

Nested Loop이 느리네.

라고 판단하면 문제의 원인을 놓칠 수 있다.
진짜 문제는

예상

users rows = 100


실제

users rows = 100000

일 수 있다.
Optimizer는 100건밖에 나오지 않을 것이라고 예상했기 때문에 Nested Loop를 선택했다.
하지만 실제로는 10만 건이 나왔고

orders Index Scan

loops=100000

이 되어버렸다.
즉 문제의 흐름은 다음과 같다.

Cardinality 추정 실패
        ↓
잘못된 Join 전략 선택
        ↓
과도한 Loop
        ↓
성능 저하

여기서 중요한 개념이 나온다.

실행계획 문제는 SQL 구조 때문에 발생할 수도 있지만, 데이터의 양과 데이터 분포 때문에 발생할 수도 있다.

같은 SQL이라도

데이터 1,000건

일 때와

데이터 1억 건

일 때 적절한 실행계획은 다를 수 있다.
심지어 데이터 건수가 같더라도

A = 50%
B = 50%

처럼 값이 고르게 분포한 경우와

A = 99.9%
B = 0.1%

처럼 데이터가 편향된 경우에도 적절한 실행계획이 달라질 수 있다.
PostgreSQL Planner가 합리적인 결정을 내리기 위해서는 pg_statistic 등의 통계 정보가 중요하며, 데이터가 크게 변경된 경우 통계가 실제 데이터 분포를 제대로 반영하지 못하면 잘못된 추정이 발생할 수 있다. PostgreSQL 역시 이러한 경우 필요하면 ANALYZE를 수행하여 통계를 갱신할 것을 설명한다.

15. Cost가 낮으면 무조건 좋은 실행계획인가?

그렇지는 않다.
Cost는 어디까지나 Optimizer의 예상값이다.
Optimizer가 알고 있는 정보가 잘못되어 있다면 Cost 역시 현실과 달라질 수 있다.
예를 들어 다음과 같은 상황이 발생할 수 있다.

Optimizer 예상

Nested Loop Cost = 100
Hash Join Cost   = 500

→ Nested Loop 선택

하지만 실제로 실행해보니

Nested Loop = 8초
Hash Join   = 500ms

일 수도 있다.
그렇다고 PostgreSQL의 Optimizer가 잘못되었다고 바로 판단하면 안 된다.
먼저 확인해야 할 것은

왜 Optimizer는 Nested Loop가 더 싸다고 판단했는가?

이다.
대부분의 실행계획 분석은 결국 이 질문을 추적하는 과정이다.

통계가 잘못되었는가?

Cardinality 예상이 틀렸는가?

데이터가 특정 값에 편향되어 있는가?

인덱스가 없는가?

조건절 때문에 인덱스를 사용할 수 없는가?

Join 순서가 비효율적인가?

메모리가 부족해 Disk Sort가 발생했는가?

실행계획을 읽는다는 것은 단순히

Seq Scan = 나쁨

Index Scan = 좋음

이라고 판단하는 것이 아니다.
Optimizer가 왜 이 Plan을 선택했는지를 역추적하는 과정에 가깝다.

17. 정리

PostgreSQL에서 SQL은 대략 다음 과정을 거친다.

SQL

↓

Parser
SQL 분석

↓

Rewrite
Query Tree 변환

↓

Planner / Optimizer
후보 실행 경로 생성
Cost 계산
실행계획 선택

↓

Executor
Plan Tree 실행

↓

Result

그리고 실행계획을 분석하기 위해

EXPLAIN

과

EXPLAIN ANALYZE

를 사용할 수 있다.

예상과 실제 비교
그리고 실행계획을 읽을 때 중요한 값은 다음과 연결된다.

cost
→ Optimizer가 예상한 비용

rows
→ Optimizer가 예상한 출력 Row 수

width
→ 예상 Row 크기

actual time
→ 실제 Node 실행 시간

actual rows
→ 실제 출력 Row 수

loops
→ Node 반복 실행 횟수

그중에서도 특히 중요하게 봐야 하는 것은

Estimated Rows
        vs
Actual Rows

이다.
이 차이가 크게 발생하면 잘못된 Join 방식이나 잘못된 Scan 방식이 선택되는 원인이 될 수 있기 때문이다.
결국 실행계획을 분석할 때 가장 중요한 질문은

“왜 PostgreSQL Optimizer는 이 실행계획이 가장 저렴하다고 판단했는가?”

이다.
이 질문을 따라가다 보면 자연스럽게

Seq Scan / Index Scan

Cardinality

통계 정보

Selectivity

Nested Loop

Hash Join

Merge Join

Sort

Buffer / Disk I/O

같은 개념으로 이어지게 된다.
실행계획은 단순히 느린 SQL을 찾아내는 출력물이 아니다.
SQL 구조와 데이터의 특성을 PostgreSQL이 어떻게 해석했는지 보여주는 결과물이라고 보는 것이 더 정확하다.