What It Means to Read an Execution Plan

Summary

Reading an execution plan is not skimming node names but finding the gap between the optimizer's prediction and the actual result.

A report comes in that it is slow

The report you get most during the stabilization period is "the screen is slow." There is an order to follow.

1. 어느 화면/기능이 느린가 (액세스 로그의 응답시간 상위 URL)
2. 그 기능이 실행하는 SQL 은 무엇인가
3. 그 SQL 의 실행계획은 어떻게 생겼나
4. 옵티마이저의 예측과 실제 결과가 얼마나 다른가
5. 데이터가 문제인가, 인덱스가 문제인가, 쿼리가 문제인가

If you skip step 1 and start with "the DB seems to be slow," your direction wavers. Pinpointing the measurement point first is half of tuning.

Reading an execution plan is not reading node names

Reading an execution plan is not skimming node names. It is finding the gap between the optimizer's prediction and the actual result.

The optimizer looks at the statistics and predicts "with this condition, roughly this many rows will come out." If the prediction is right, it generally builds a good plan. If it is wrong, it builds a strange plan. So what to look at is estimated rows vs actual rows.

예측 92,300  실제 91,188   → 오차 1.2%. 통계가 건강하다. 계획을 믿어도 된다
예측      1  실제 482,913   → 옵티마이저가 잘못된 전제 위에서 정확히 계산했다

In the second case, what needs fixing is not the query but the statistics. If you run ANALYZE and refresh the statistics, the plan changes entirely. Before you cling to the query and add hints, suspect the statistics first.

Cost is not time

Another common misunderstanding. The cost that appears in an execution plan is not a unit of time. It is a score assigned relatively, with "the cost of reading one disk page sequentially" set to 1.0. So advice like this has no basis.

"If the cost exceeds 10000, it is dangerous"

10000 may be 0.3 seconds or 30 seconds. It depends on hardware and cache state. Cost is a value to use when comparing two plans of the same query, not an absolute criterion.

A parent node's time is a cumulative value

What you must know when looking at each node's actual time in an execution plan.

A parent node's time is a cumulative value that includes its children's time.

Hash Join  (실제 131ms)
  ├─ Seq Scan on orders   (실제 92ms)   ← 여기가 진짜 범인
  └─ Hash on customers    (실제 12ms)

The Hash Join being 131ms does not mean the join is slow. 92ms of that is the orders scan. The time the join itself used is about 27ms. If you do not know this, you tune the wrong place.

Typical reasons an index is not used

1. A function was applied to the column

-- 인덱스를 못 탄다
WHERE substr(ORD_DT, 1, 6) = '202608'

-- 범위 조건으로 바꾸면 탄다
WHERE ORD_DT >= '20260801' AND ORD_DT < '20260901'

An index is sorted by the column's original value. When you wrap it in a function, it is no longer that value, so the index order cannot be used. This happens often with date strings, case conversion, and type casting.

This is expressed as being SARGable. It means to write the condition in a searchable form.

2. The leading column of a composite index is not in the condition

With a (CUST_ID, ORD_DT) index, if the condition has only WHERE ORD_DT = ?, the index cannot be used. It is like having a phone book sorted by surname-then-given-name when you only know the given name.

3. Poor selectivity

With WHERE USE_YN = 'Y' where 99% are 'Y', simply scanning everything is faster than finding rows through the index and reading the table again. The optimizer not using the index can be the right decision.

An important attitude here: a Seq Scan (full scan) is not always bad. For a small table, or a query that must read most of the rows, a full scan is the best. The reflexive reaction "I see a scan, so let's make an index" only adds indexes and slows writes.

Covering indexes

If all the columns a query needs are in the index, it does not need to read the table.

-- 인덱스: (CUST_ID, ORD_DT, ORD_AMT)
SELECT ORD_DT, ORD_AMT FROM ORDERS WHERE CUST_ID = ?;
-- → 인덱스만 읽고 끝. 테이블 접근이 없다

If you see COVERING INDEX or Index Only Scan in the execution plan, this is the state. Making a frequently used lookup covering by adding a column or two to the index is quite effective. In exchange, the index gets bigger and writes get slower. It is a trade-off.

The N+1 problem

This is a typical performance problem created in the application.

주문 100건 조회       → 쿼리 1회
각 주문의 고객명 조회 → 쿼리 100회
                       총 101회

Each query is 1ms, so everything looks fast if you only look at the log. But with 101 round trips, network latency alone comes to hundreds of ms. And it gets worse linearly as data grows.

The solution is one join or one IN clause. If you use an ORM, this problem arises silently, so during development you need the habit of keeping the SQL log on and counting how many queries go out to open one screen.

Statistics and ANALYZE

The optimizer's basis for decisions is statistics. So when statistics are stale, plans get worse.

Run ANALYZE after bulk loads/deletes
Always run it right after a migration. Otherwise the optimizer believes "this table is empty"
Check that it runs periodically. There are production DBs where automatic statistics collection is actually turned off

A large share of reports that performance suddenly got worse after a migration are stale statistics. The queries and indexes are the same; only the plan changed. If you do not know the cause, you wander for a long time.

Finally — leave tuning results in a document

You must leave a record of what you changed and why. Six months later, when someone tries to delete "what is this odd index?", you will need it.

대상: SCR-021 주문조회
증상: p95 4.2초
원인: (ORD_DT, CUST_ID) 인덱스가 조회 패턴과 순서가 반대
조치: IX_ORDERS_01 (CUST_ID, ORD_DT) 추가, 기존 인덱스 유지(배치가 사용)
결과: p95 0.3초. 실행계획이 스캔에서 인덱스 탐색으로 전환

What it looks like in the field

The place that leaks most often from a "the screen is slow" report is step 1. If you do not pinpoint the measurement point and start with "the DB seems to be slow," everything you do from then on is built on guesswork. Extracting the top URLs by response time from the access log takes 1 minute, and that 1 minute sets the direction.

The next most common thing is the situation where you created an index and do not know why it is not used. The cause is usually one of three — a function was applied to the column (substr(dt,1,6)='202603'), the leading column of the composite index is not in the condition, or selectivity is poor and the optimizer deliberately chose not to use it. In the last case, forcing the index actually makes it slower.

And you must leave tuning results in a document. Adding an index is not free but a trade of reads against writes, so without a record of why this index was created, the next person cannot judge whether to delete it or keep it.