쿼리 플래너와 EXPLAIN (Query Planner/EXPLAIN)
쿼리 플래너와 EXPLAIN (Query Planner/EXPLAIN)
PostgreSQL은 받은 쿼리마다 *쿼리 플랜(query plan)*을 설계합니다. 쿼리 구조와 데이터의 성질에 맞는 플랜을 고르는 것은 성능에 절대적으로 중요하기 때문에, 시스템은 좋은 플랜을 고르려고 노력하는 복잡한 *플래너(planner)*를 포함하고 있습니다. 플래너가 어떤 쿼리에 대해 어떤 쿼리 플랜을 만들었는지 확인하려면 EXPLAIN 명령을 쓰면 됩니다. 플랜을 읽는 것은 어느 정도 경험이 필요한 예술에 가깝지만, 이 절에서는 그 기초를 다루려고 합니다.
이 절의 예제는 v18 개발 소스에서 VACUUM ANALYZE를 실행한 뒤의 회귀 테스트(regression test) 데이터베이스에서 가져온 것입니다. 직접 예제를 실행해 보면 비슷한 결과를 얻을 수 있겠지만, 추정 비용과 행 수는 조금씩 달라질 수 있습니다. ANALYZE의 통계가 정확한 값이 아니라 무작위 표본(random sample)이고, 비용이 본질적으로 플랫폼에 다소 의존적이기 때문입니다.
예제들은 EXPLAIN의 기본 "text" 출력 형식을 사용합니다. 이 형식은 사람이 읽기에 간결하고 편리합니다. EXPLAIN의 출력을 프로그램에 넣어 추가 분석하려면, 대신 기계가 읽을 수 있는 출력 형식(XML, JSON, YAML) 중 하나를 사용해야 합니다.
14.1.1. EXPLAIN 기초 (EXPLAIN Basics)
쿼리 플랜의 구조는 *플랜 노드(plan node)*들의 트리입니다. 트리의 가장 아래 레벨에 있는 노드는 스캔 노드(scan node)로, 테이블에서 원시 행(raw row)을 반환합니다. 테이블 접근 방법에 따라 서로 다른 유형의 스캔 노드가 있습니다. 순차 스캔(sequential scan), 인덱스 스캔(index scan), 비트맵 인덱스 스캔(bitmap index scan)이 그것입니다. 또한 테이블이 아닌 행 소스도 있는데, FROM 절의 VALUES 절이나 집합 반환 함수(set-returning function) 같은 것이며, 이것들도 고유한 스캔 노드 유형을 갖습니다. 쿼리가 조인, 집계, 정렬, 또는 원시 행에 대한 다른 연산을 요구한다면, 스캔 노드 위에는 그 연산을 수행하는 추가 노드들이 놓입니다. 이러한 연산들도 보통 여러 가지 방법이 가능하므로, 여기에도 다양한 노드 유형이 나타날 수 있습니다.
EXPLAIN의 출력은 플랜 트리의 각 노드마다 한 줄씩이며, 기본 노드 유형과 플래너가 그 플랜 노드의 실행을 위해 만든 비용 추정치를 보여줍니다. 노드의 요약 줄에서 들여쓰기되어 추가 줄이 나올 수도 있는데, 노드의 추가 속성을 보여줍니다. 맨 첫 줄(가장 위쪽 노드의 요약 줄)은 플랜의 추정 총 실행 비용을 갖고 있습니다. 바로 이 숫자가 플래너가 최소화하려고 하는 값입니다.
출력이 어떤 모양인지 보여 주는 아주 간단한 예부터 보겠습니다.
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
이 쿼리에는 WHERE 절이 없으므로 테이블의 모든 행을 스캔해야 하고, 그래서 플래너는 단순한 순차 스캔 플랜을 선택했습니다. 괄호 안에 적힌 숫자는 (왼쪽부터) 다음과 같은 의미입니다.
- 추정 시작 비용(estimated start-up cost): 출력 단계를 시작하기 전에 소비되는 시간. 예를 들어 정렬 노드에서 정렬을 수행하는 데 걸리는 시간입니다.
- 추정 총 비용(estimated total cost): 플랜 노드를 끝까지 실행한다고, 즉 가능한 모든 행을 가져온다고 가정할 때의 비용. 실제로는 노드의 부모 노드가 가능한 모든 행을 다 읽지 않고 중간에 멈출 수도 있습니다. (아래
LIMIT예제를 참고하세요.) - 이 플랜 노드가 출력하는 추정 행 수(estimated number of rows): 역시 노드를 끝까지 실행한다고 가정한 값입니다.
- 이 플랜 노드가 출력하는 행의 추정 평균 폭(estimated average width, 바이트 단위).
비용은 플래너의 비용 매개변수에 의해 결정되는 임의의 단위로 측정됩니다(19.7.2절 참고). 전통적으로 비용은 디스크 페이지 인출(fetch) 단위로 측정하는데, 즉 seq_page_cost를 관례적으로 1.0으로 두고 다른 비용 매개변수는 그에 상대적으로 설정합니다. 이 절의 예제는 기본 비용 매개변수로 실행했습니다.
중요한 점은, 상위 레벨 노드의 비용에는 그 모든 자식 노드의 비용이 포함된다는 것입니다. 그리고 비용은 플래너가 신경 쓰는 것만 반영한다는 것도 알아야 합니다. 특히 비용은 출력 값을 텍스트 형태로 변환하거나 클라이언트로 전송하는 데 걸리는 시간을 고려하지 않습니다. 이는 실제 경과 시간에서 중요한 요소가 될 수 있지만, 플래너는 플랜을 바꿔도 이 비용을 바꿀 수 없으므로 무시합니다. (올바른 플랜이라면 모두 같은 행 집합을 출력할 것이라고 믿습니다.)
rows 값은 좀 까다롭습니다. 플랜 노드가 처리하거나 스캔한 행의 수가 아니라, 노드가 *출력(emitted)*하는 행의 수이기 때문입니다. 노드에 적용되는 WHERE 절 조건에 의한 필터링 때문에, 이 값은 스캔한 행 수보다 적은 경우가 많습니다. 이상적으로는 최상위 레벨의 rows 추정치가 쿼리가 실제로 반환, 갱신, 삭제하는 행 수에 근사해야 합니다.
다시 예제로 돌아가겠습니다.
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
이 숫자들은 아주 직관적으로 도출됩니다. 다음을 실행하면
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
tenk1이 디스크 페이지 345개와 행 10000개를 갖고 있음을 알 수 있습니다. 추정 비용은 (읽은 디스크 페이지 수 * seq_page_cost) + (스캔한 행 수 * cpu_tuple_cost)로 계산됩니다. 기본적으로 seq_page_cost는 1.0, cpu_tuple_cost는 0.01이므로, 추정 비용은 (345 * 1.0) + (10000 * 0.01) = 445입니다.
이제 쿼리를 바꿔서 WHERE 조건을 추가해 보겠습니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244)
Filter: (unique1 < 7000)
EXPLAIN 출력이 Seq Scan 플랜 노드에 붙은 "filter"(필터) 조건으로 WHERE 절이 적용되는 것을 보여 줍니다. 즉, 이 플랜 노드는 스캔하는 각 행마다 그 조건을 검사하고, 조건을 통과하는 행만 출력합니다. WHERE 절 때문에 출력 행 수 추정치가 줄어들었습니다. 하지만 스캔은 여전히 10000행을 모두 방문해야 하므로 비용은 줄지 않았습니다. 사실 정확히 말하면 10000 * cpu_operator_cost만큼 비용이 조금 올라갔는데, WHERE 조건을 검사하는 데 드는 추가 CPU 시간을 반영한 것입니다.
이 쿼리가 실제로 선택하는 행 수는 7000이지만, rows 추정치는 근사값일 뿐입니다. 이 실험을 직접 재현해 보면 다소 다른 추정치를 얻게 될 수 있으며, 게다가 ANALYZE 명령을 실행할 때마다 바뀔 수 있습니다. ANALYZE가 만드는 통계가 테이블의 무작위 표본에서 얻어지기 때문입니다.
이제 조건을 더 제한적으로 만들어 보겠습니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=5.06..224.98 rows=100 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
Index Cond: (unique1 < 100)
여기서 플래너는 두 단계 플랜을 사용하기로 결정했습니다. 자식 플랜 노드는 인덱스 조건과 일치하는 행들의 위치를 찾기 위해 인덱스를 방문하고, 상위 플랜 노드는 그 행들을 실제로 테이블에서 가져옵니다. 행을 하나씩 따로 가져오는 것은 순차적으로 읽는 것보다 훨씬 비싸지만, 테이블의 모든 페이지를 방문할 필요가 없으므로 순차 스캔보다는 여전히 쌉니다. (두 플랜 레벨을 쓰는 이유는, 상위 플랜 노드가 별도 인출의 비용을 최소화하기 위해 인덱스가 식별한 행 위치들을 읽기 전에 물리적 순서로 정렬하기 때문입니다. 노드 이름에 언급된 "bitmap(비트맵)"이 바로 이 정렬을 수행하는 메커니즘입니다.)
이제 WHERE 절에 조건을 하나 더 추가해 보겠습니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=5.04..225.20 rows=1 width=244)
Recheck Cond: (unique1 < 100)
Filter: (stringu1 = 'xxx'::name)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
Index Cond: (unique1 < 100)
추가된 조건 stringu1 = 'xxx'는 출력 행 수 추정치를 줄이지만, 비용은 줄이지 않습니다. 여전히 같은 행 집합을 방문해야 하기 때문입니다. stringu1 절은 인덱스 조건으로 적용될 수 없는데, 이 인덱스는 unique1 열에만 있기 때문입니다. 대신 인덱스를 이용해 가져온 행들에 대한 필터로 적용됩니다. 그래서 비용은 이 추가 검사를 반영해 실제로 약간 올라갔습니다.
어떤 경우에는 플래너가 "단순한" 인덱스 스캔 플랜을 선호합니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
Index Cond: (unique1 = 42)
이 유형의 플랜에서는 테이블 행을 인덱스 순서대로 가져오므로 읽기가 훨씬 더 비싸지만, 행 수가 너무 적어서 행 위치를 정렬하는 추가 비용이 아깝지 않습니다. 이런 플랜 유형은 단일 행만 가져오는 쿼리에서 가장 자주 볼 수 있습니다. 또한 인덱스 순서와 일치하는 ORDER BY 조건이 있는 쿼리에서도 자주 쓰이는데, 그러면 ORDER BY를 만족시키는 데 추가 정렬 단계가 필요 없기 때문입니다. 이 예제에서 ORDER BY unique1을 추가해도 인덱스가 이미 요청된 순서를 암묵적으로 제공하므로 같은 플랜을 사용합니다.
플래너는 ORDER BY 절을 여러 가지 방식으로 구현할 수 있습니다. 위 예제는 그런 정렬 절이 암묵적으로 구현될 수 있음을 보여 줍니다. 플래너는 명시적인 Sort 단계를 추가할 수도 있습니다.
EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=1109.39..1134.39 rows=10000 width=244)
Sort Key: unique1
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
플랜의 일부가 필요한 정렬 키의 앞부분(prefix)에 대한 정렬을 보장한다면, 플래너는 대신 Incremental Sort(증분 정렬) 단계를 사용하기로 할 수도 있습니다.
EXPLAIN SELECT * FROM tenk1 ORDER BY hundred, ten LIMIT 100;
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=19.35..39.49 rows=100 width=244)
-> Incremental Sort (cost=19.35..2033.39 rows=10000 width=244)
Sort Key: hundred, ten
Presorted Key: hundred
-> Index Scan using tenk1_hundred on tenk1 (cost=0.29..1574.20 rows=10000 width=244)
일반적인 정렬과 비교하면, 증분 정렬은 전체 결과 집합이 정렬되기 전에 튜플을 반환할 수 있어서 특히 LIMIT 쿼리에서 최적화를 가능하게 합니다. 또한 메모리 사용량과 정렬을 디스크로 넘길(spill) 가능성을 줄여 줄 수도 있지만, 결과 집합을 여러 정렬 배치(batch)로 나누는 데 따르는 오버헤드 증가라는 비용이 따릅니다.
WHERE에서 참조하는 여러 열 각각에 별도 인덱스가 있다면, 플래너는 그 인덱스들의 AND 또는 OR 조합을 사용하기로 선택할 수 있습니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=25.07..25.07 rows=10 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0)
Index Cond: (unique2 > 9000)
하지만 이는 두 인덱스를 모두 방문해야 하므로, 인덱스 하나만 쓰고 다른 조건은 필터로 처리하는 것보다 반드시 이득은 아닙니다. 관련된 범위를 바꿔 보면 플랜이 그에 따라 달라지는 것을 알 수 있습니다.
다음은 LIMIT의 효과를 보여 주는 예제입니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=0.29..14.28 rows=2 width=244)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.27 rows=10 width=244)
Index Cond: (unique2 > 9000)
Filter: (unique1 < 100)
위와 같은 쿼리지만 LIMIT를 추가해 모든 행을 가져올 필요가 없어졌고, 플래너는 무엇을 할지 마음을 바꿨습니다. Index Scan 노드의 총 비용과 행 수가 끝까지 실행되는 것처럼 표시되는 것에 주목하세요. 하지만 Limit 노드는 그 행들 중 5분의 1만 가져온 뒤 멈출 것으로 예상되므로, 그 총 비용도 5분의 1에 불과하며, 그것이 이 쿼리의 실제 추정 비용입니다. 이 플랜이 이전 플랜에 Limit 노드를 추가하는 것보다 선호되는 이유는, Limit이 비트맵 스캔의 시작 비용을 피할 수 없으므로 그 접근 방식의 총 비용이 25 단위를 넘게 되기 때문입니다.
이제 지금까지 다뤄온 열들을 사용해서 두 테이블을 조인해 보겠습니다.
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=4.65..118.50 rows=10 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
Index Cond: (unique1 < 10)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244)
Index Cond: (unique2 = t1.unique2)
이 플랜에는 두 개의 테이블 스캔을 입력(자식)으로 갖는 중첩 루프 조인(nested-loop join) 노드가 있습니다. 노드 요약 줄의 들여쓰기가 플랜 트리 구조를 반영합니다. 조인의 첫 번째, 즉 "outer(외부)" 자식은 앞서 본 것과 비슷한 비트맵 스캔입니다. 그 노드에서 WHERE 절 unique1 < 10을 적용하므로 그 비용과 행 수는 SELECT ... WHERE unique1 < 10에서 얻는 것과 같습니다. t1.unique2 = t2.unique2 절은 아직 관련이 없으므로 외부 스캔의 행 수에 영향을 주지 않습니다. 중첩 루프 조인 노드는 외부 자식에서 얻은 각 행마다 두 번째, 즉 "inner(내부)" 자식을 한 번씩 실행합니다. 현재 외부 행의 열 값은 내부 스캔에 끼워 넣을 수 있습니다. 여기서는 외부 행의 t1.unique2 값을 사용할 수 있으므로, 앞서 본 단순한 SELECT ... WHERE t2.unique2 = 상수 경우와 비슷한 플랜과 비용을 얻습니다. (추정 비용은 위에서 본 것보다 실제로 조금 낮은데, t2에 대한 반복 인덱스 스캔에서 발생할 것으로 예상되는 캐싱 때문입니다.) 루프 노드의 비용은 외부 스캔의 비용에, 외부 행마다의 내부 스캔 반복 한 번(여기서는 10 * 7.90)을 더하고, 조인 처리용 CPU 시간을 조금 더한 것에 기반해 설정됩니다.
이 예제에서 조인의 출력 행 수는 두 스캔의 행 수의 곱과 같지만, 항상 그런 것은 아닙니다. 두 테이블을 모두 언급하는 추가 WHERE 절이 있어서 어느 입력 스캔에도 적용할 수 없고 조인 지점에서만 적용할 수 있는 경우가 있기 때문입니다. 예를 들어 보겠습니다.
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;
QUERY PLAN
---------------------------------------------------------------------------------------------
Nested Loop (cost=4.65..49.36 rows=33 width=488)
Join Filter: (t1.hundred < t2.hundred)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
Index Cond: (unique1 < 10)
-> Materialize (cost=0.29..8.51 rows=10 width=244)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..8.46 rows=10 width=244)
Index Cond: (unique2 < 10)
조건 t1.hundred < t2.hundred는 tenk2_unique2 인덱스에서 검사할 수 없으므로 조인 노드에서 적용됩니다. 이는 조인 노드의 추정 출력 행 수를 줄이지만, 두 입력 스캔은 바꾸지 않습니다.
여기서 주목할 점은 플래너가 조인의 내부 관계 위에 Materialize(구체화) 플랜 노드를 두어 "materialize"하기로 선택했다는 것입니다. 즉 중첩 루프 조인 노드가 그 데이터를 외부 관계의 각 행마다 한 번씩, 총 열 번 읽어야 하더라도, t2 인덱스 스캔은 한 번만 수행됩니다. Materialize 노드는 데이터를 읽으면서 메모리에 저장하고, 이후의 각 패스에서는 메모리에서 데이터를 반환합니다.
외부 조인(outer join)을 다룰 때는 "Join Filter"와 일반 "Filter" 조건이 모두 붙은 조인 플랜 노드를 볼 수 있습니다. Join Filter 조건은 외부 조인의 ON 절에서 오므로, Join Filter 조건을 통과하지 못한 행도 null 확장 행으로 출력될 수 있습니다. 반면 일반 Filter 조건은 외부 조인 규칙이 적용된 뒤에 적용되므로 행을 무조건 제거하는 역할을 합니다. 내부 조인에서는 이 두 유형의 필터 사이에 의미상 차이가 없습니다.
쿼리의 선택성(selectivity)을 조금 바꾸면 아주 다른 조인 플랜을 얻을 수 있습니다.
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=226.23..709.73 rows=100 width=488)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
-> Hash (cost=224.98..224.98 rows=100 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
Index Cond: (unique1 < 100)
여기서 플래너는 해시 조인(hash join)을 선택했습니다. 한 테이블의 행을 인메모리 해시 테이블에 넣은 뒤, 다른 테이블을 스캔하면서 각 행에 대해 해시 테이블을 조사(probe)해 일치 여부를 찾는 방식입니다. 들여쓰기가 플랜 구조를 반영한다는 점을 다시 주목하세요. tenk1에 대한 비트맵 스캔은 해시 테이블을 구성하는 Hash 노드의 입력입니다. 그것이 다시 Hash Join 노드로 반환되고, Hash Join 노드는 외부 자식 플랜에서 행을 읽어 각각에 대해 해시 테이블을 검색합니다.
또 다른 가능한 조인 유형은 병합 조인(merge join)으로, 아래에 예시되어 있습니다.
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Merge Join (cost=0.56..233.49 rows=10 width=488)
Merge Cond: (t1.unique2 = t2.unique2)
-> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..643.28 rows=100 width=244)
Filter: (unique1 < 100)
-> Index Scan using onek_unique2 on onek t2 (cost=0.28..166.28 rows=1000 width=244)
병합 조인은 입력 데이터가 조인 키에 대해 정렬되어 있어야 합니다. 이 예제에서는 각 입력이 인덱스 스캔으로 올바른 순서의 행을 방문하도록 정렬되어 있습니다. 하지만 순차 스캔과 정렬로도 가능합니다. (많은 행을 정렬할 때는 순차 스캔 후 정렬이 인덱스 스캔을 자주 이기는데, 인덱스 스캔이 요구하는 비순차적 디스크 접근 때문입니다.)
변형된 플랜을 보는 한 가지 방법은 19.7.1절에 설명된 활성화/비활성화 플래그를 사용해 플래너가 가장 싸다고 생각한 전략을 무시하도록 강제하는 것입니다. (거친 도구지만 유용합니다. 14.3절도 참고하세요.) 예를 들어, 이전 예제에서 병합 조인이 최고의 조인 유형이라고 확신하지 못한다면, 이렇게 시도해 볼 수 있습니다.
SET enable_mergejoin = off;
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=226.23..344.08 rows=10 width=488)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on onek t2 (cost=0.00..114.00 rows=1000 width=244)
-> Hash (cost=224.98..224.98 rows=100 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0)
Index Cond: (unique1 < 100)
이 출력은 플래너가 이 경우 해시 조인이 병합 조인보다 거의 50% 더 비싸다고 생각한다는 것을 보여 줍니다. 물론 다음 질문은 그 생각이 맞는지입니다. 이는 아래에서 설명할 EXPLAIN ANALYZE를 사용해 조사할 수 있습니다.
활성화/비활성화 플래그로 플랜 노드 유형을 비활성화할 때, 많은 플래그는 해당 플랜 노드의 사용을 억제하기만 할 뿐 플래너의 사용 능력을 완전히 막지는 않습니다. 이는 플래너가 주어진 쿼리에 대해 플랜을 구성할 수 있는 능력을 유지하도록 설계된 것입니다. 결과 플랜에 비활성화된 노드가 포함되면 EXPLAIN 출력이 그 사실을 표시합니다.
SET enable_seqscan = off;
EXPLAIN SELECT * FROM unit;
QUERY PLAN
---------------------------------------------------------
Seq Scan on unit (cost=0.00..21.30 rows=1130 width=44)
Disabled: true
unit 테이블에는 인덱스가 없어서 테이블 데이터를 읽을 다른 방법이 없으므로, 순차 스캔이 쿼리 플래너에게 유일한 선택지입니다.
일부 쿼리 플랜에는 원래 쿼리의 서브SELECT에서 생기는 *서브플랜(subplan)*이 관련됩니다. 그런 쿼리는 때때로 일반 조인 플랜으로 변환될 수 있지만, 변환할 수 없을 때는 다음과 같은 플랜을 얻게 됩니다.
EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t
WHERE t.ten < ALL (SELECT o.ten FROM onek o WHERE o.four = t.four);
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on public.tenk1 t (cost=0.00..586095.00 rows=5000 width=4)
Output: t.unique1
Filter: (ALL (t.ten < (SubPlan 1).col1))
SubPlan 1
-> Seq Scan on public.onek o (cost=0.00..116.50 rows=250 width=4)
Output: o.ten
Filter: (o.four = t.four)
이 다소 인위적인 예제는 두 가지 점을 설명하는 데 도움이 됩니다. 외부 플랜 레벨의 값이 서브플랜 안으로 전달될 수 있고(여기서는 t.four가 전달됨), 서브셀렉트의 결과를 외부 플랜에서 사용할 수 있다는 것입니다. 그 결과 값들은 EXPLAIN에 (서브플랜이름).colN 같은 표기로 표시되는데, 서브SELECT의 N번째 출력 열을 가리킵니다.
위 예제에서 ALL 연산자는 외부 쿼리의 각 행마다 서브플랜을 다시 실행합니다. (그래서 추정 비용이 높습니다.) 일부 쿼리는 *해시드 서브플랜(hashed subplan)*을 사용해 이를 피할 수 있습니다.
EXPLAIN SELECT *
FROM tenk1 t
WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);
QUERY PLAN
--------------------------------------------------------------------------------------------
Seq Scan on tenk1 t (cost=61.77..531.77 rows=5000 width=244)
Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
SubPlan 1
-> Index Only Scan using onek_unique1 on onek o (cost=0.28..59.27 rows=1000 width=4)
(4 rows)
여기서 서브플랜은 한 번만 실행되고 그 출력은 인메모리 해시 테이블에 로드되며, 외부 ANY 연산자가 그 테이블을 조사합니다. 이는 서브SELECT가 외부 쿼리의 어떤 변수도 참조하지 않고, ANY의 비교 연산자가 해싱에 적합해야 한다는 것을 요구합니다.
외부 쿼리의 어떤 변수도 참조하지 않는 데 더해 서브SELECT가 행을 하나 이상 반환할 수 없다면, 대신 initplan으로 구현될 수 있습니다.
EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on public.tenk1 t1 (cost=0.02..470.02 rows=1000 width=4)
Output: t1.unique1
Filter: (t1.ten = (InitPlan 1).col1)
InitPlan 1
-> Result (cost=0.00..0.02 rows=1 width=4)
Output: ((random() * '10'::double precision))::integer
initplan은 외부 플랜을 실행할 때마다 딱 한 번만 실행되고, 그 결과는 외부 플랜의 이후 행들에서 재사용하기 위해 저장됩니다. 그래서 이 예제에서 random()은 한 번만 평가되고, t1.ten의 모든 값은 같은 무작위로 선택된 정수와 비교됩니다. 이는 서브SELECT 구성이 없을 때 일어나는 일과는 꽤 다릅니다.
14.1.2. EXPLAIN ANALYZE
EXPLAIN의 ANALYZE 옵션을 사용하면 플래너 추정치의 정확성을 확인할 수 있습니다. 이 옵션을 쓰면 EXPLAIN이 실제로 쿼리를 실행하고, 일반 EXPLAIN이 보여 주는 것과 같은 추정치와 함께 각 플랜 노드에서 축적된 실제 행 수와 실제 실행 시간을 표시합니다. 예를 들어 다음과 같은 결과를 얻을 수 있습니다.
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10.00 loops=1)
Buffers: shared hit=36 read=6
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10.00 loops=1)
Recheck Cond: (unique1 < 10)
Heap Blocks: exact=10
Buffers: shared hit=3 read=5 written=4
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10.00 loops=1)
Index Cond: (unique1 < 10)
Index Searches: 1
Buffers: shared hit=2
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1.00 loops=10)
Index Cond: (unique2 = t1.unique2)
Index Searches: 10
Buffers: shared hit=24 read=6
Planning:
Buffers: shared hit=15 dirtied=9
Planning Time: 0.485 ms
Execution Time: 0.073 ms
"actual time"(실제 시간) 값은 실시간의 밀리초 단위인 반면, cost 추정치는 임의의 단위로 표현되므로 서로 일치할 가능성이 낮다는 점에 주의하세요. 보통 가장 중요하게 봐야 할 것은 추정 행 수가 실제와 합리적으로 가깝은지입니다. 이 예제에서는 추정치가 모두 정확히 맞았지만, 실제로는 꽤 드문 경우입니다.
일부 쿼리 플랜에서는 서브플랜 노드가 두 번 이상 실행될 수 있습니다. 예를 들어 위 중첩 루프 플랜에서 내부 인덱스 스캔은 외부 행마다 한 번씩 실행됩니다. 그런 경우 loops 값은 노드의 총 실행 횟수를 보고하고, 실제 시간과 rows 값은 실행당 평균을 보여 줍니다. 이는 숫자를 비용 추정치가 표시되는 방식과 비교 가능하게 만들기 위한 것입니다. loops 값을 곱하면 노드에서 실제로 소비된 총 시간을 얻습니다. 위 예제에서 우리는 tenk2의 인덱스 스캔을 실행하는 데 총 0.030밀리초를 소비했습니다.
어떤 경우에는 EXPLAIN ANALYZE가 플랜 노드 실행 시간과 행 수 외에 추가 실행 통계를 보여 줍니다. 예를 들어 Sort와 Hash 노드는 추가 정보를 제공합니다.
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100.00 loops=1)
Sort Key: t1.fivethous
Sort Method: quicksort Memory: 74kB
Buffers: shared hit=440
-> Hash Join (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100.00 loops=1)
Hash Cond: (t2.unique2 = t1.unique2)
Buffers: shared hit=437
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000.00 loops=1)
Buffers: shared hit=345
-> Hash (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100.00 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 35kB
Buffers: shared hit=92
-> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100.00 loops=1)
Recheck Cond: (unique1 < 100)
Heap Blocks: exact=90
Buffers: shared hit=92
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100.00 loops=1)
Index Cond: (unique1 < 100)
Index Searches: 1
Buffers: shared hit=2
Planning:
Buffers: shared hit=12
Planning Time: 0.187 ms
Execution Time: 3.036 ms
Sort 노드는 사용된 정렬 방법(특히 정렬이 인메모리였는지 온디스크였는지)과 필요한 메모리 또는 디스크 공간 양을 보여 줍니다. Hash 노드는 해시 버킷과 배치 수, 그리고 해시 테이블에 사용된 메모리의 최대치를 보여 줍니다. (배치 수가 1을 초과하면 디스크 공간 사용도 포함되지만, 표시되지는 않습니다.)
Index Scan 노드(그리고 Bitmap Index Scan, Index-Only Scan 노드)는 모든 노드 실행/loops 전반에 걸친 총 검색 횟수를 보고하는 "Index Searches"(인덱스 검색) 줄을 보여 줍니다.
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 500, 700, 999);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=9.45..73.44 rows=40 width=244) (actual time=0.012..0.028 rows=40.00 loops=1)
Recheck Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
Heap Blocks: exact=39
Buffers: shared hit=47
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..9.44 rows=40 width=0) (actual time=0.009..0.009 rows=40.00 loops=1)
Index Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
Index Searches: 4
Buffers: shared hit=8
Planning Time: 0.029 ms
Execution Time: 0.034 ms
여기서 4번의 별도 인덱스 검색이 필요한 Bitmap Index Scan 노드를 볼 수 있습니다. 스캔은 술어의 IN 구성에 있는 각 integer 값마다 tenk1_thous_tenthous 인덱스 루트 페이지에서 한 번씩 인덱스를 검색해야 했습니다. 하지만 인덱스 검색 횟수가 쿼리 술어와 그렇게 단순하게 대응하지 않는 경우가 많습니다.
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 2, 3, 4);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=9.45..73.44 rows=40 width=244) (actual time=0.009..0.019 rows=40.00 loops=1)
Recheck Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
Heap Blocks: exact=38
Buffers: shared hit=40
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..9.44 rows=40 width=0) (actual time=0.005..0.005 rows=40.00 loops=1)
Index Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
Index Searches: 1
Buffers: shared hit=2
Planning Time: 0.029 ms
Execution Time: 0.026 ms
이 IN 쿼리의 변형은 인덱스 검색을 단 한 번만 수행했습니다. 인덱스 탐색에 더 적은 시간을 소비했는데(원래 쿼리와 비교해), 그 이유는 이 IN 구성이 같은 tenk1_thous_tenthous 인덱스 리프 페이지에서 서로 옆에 저장된 인덱스 튜플과 일치하는 값들을 사용하기 때문입니다.
"Index Searches" 줄은 스킵 스캔(skip scan) 최적화를 적용해 인덱스를 더 효율적으로 탐색하는 B-트리 인덱스 스캔에서도 유용합니다.
EXPLAIN ANALYZE SELECT four, unique1 FROM tenk1 WHERE four BETWEEN 1 AND 3 AND unique1 = 42;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using tenk1_four_unique1_idx on tenk1 (cost=0.29..6.90 rows=1 width=8) (actual time=0.006..0.007 rows=1.00 loops=1)
Index Cond: ((four >= 1) AND (four <= 3) AND (unique1 = 42))
Heap Fetches: 0
Index Searches: 3
Buffers: shared hit=7
Planning Time: 0.029 ms
Execution Time: 0.012 ms
여기서 tenk1 테이블의 four와 unique1 열에 대한 다중 열 인덱스인 tenk1_four_unique1_idx를 사용하는 Index-Only Scan 노드를 볼 수 있습니다. 스캔은 각각 단일 인덱스 리프 페이지를 읽는 3번의 검색을 수행합니다. "four = 1 AND unique1 = 42", "four = 2 AND unique1 = 42", "four = 3 AND unique1 = 42"이 그것입니다. 11.3절에서 논의한 대로 이 인덱스의 선행 열(four 열)은 구별 값이 4개뿐인 반면 두 번째/마지막 열(unique1 열)은 구별 값이 많기 때문에, 이 인덱스는 일반적으로 스킵 스캔의 좋은 대상입니다.
또 다른 유형의 추가 정보는 필터 조건에 의해 제거된 행의 수입니다.
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000.00 loops=1)
Filter: (ten < 7)
Rows Removed by Filter: 3000
Buffers: shared hit=345
Planning Time: 0.102 ms
Execution Time: 2.145 ms
이 카운트는 조인 노드에 적용되는 필터 조건에 특히 유용할 수 있습니다. "Rows Removed" 줄은 스캔된 행(조인 노드의 경우 잠재적 조인 쌍)이 하나 이상 필터 조건에 의해 거부될 때만 나타납니다.
필터 조건과 유사한 사례가 "lossy"(손실) 인덱스 스캔에서 발생합니다. 예를 들어 특정 점을 포함하는 다각형을 검색하는 다음 검색을 생각해 봅시다.
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on polygon_tbl (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0.00 loops=1)
Filter: (f1 @> '((0.5,2))'::polygon)
Rows Removed by Filter: 7
Buffers: shared hit=1
Planning Time: 0.039 ms
Execution Time: 0.033 ms
플래너는 (아주 정확하게도) 이 샘플 테이블이 인덱스 스캔을 쓸 만큼 작지 않다고 생각하므로, 모든 행이 필터 조건에 의해 거부되는 일반적인 순차 스캔을 얻게 됩니다. 하지만 인덱스 스캔을 사용하도록 강제하면 다음과 같이 됩니다.
SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0.00 loops=1)
Index Cond: (f1 @> '((0.5,2))'::polygon)
Rows Removed by Index Recheck: 1
Index Searches: 1
Buffers: shared hit=1
Planning Time: 0.039 ms
Execution Time: 0.098 ms
여기서 인덱스가 후보 행 하나를 반환했고, 그 행이 인덱스 조건의 재검사(recheck)에 의해 거부되었음을 볼 수 있습니다. 이는 다각형 포함(polygon containment) 검사에서 GiST 인덱스가 "lossy"이기 때문입니다. 실제로는 대상과 겹치는 다각형을 가진 행들을 반환하고, 그 행들에 대해 정확한 포함 검사를 해야 합니다.
EXPLAIN에는 BUFFERS 옵션이 있는데, 주어진 쿼리의 계획 수립과 실행 중 수행된 I/O 연산에 대한 추가 세부 정보를 제공합니다. 표시되는 버퍼 숫자는 주어진 노드와 그 모든 자식 노드에 대해 적중(hit), 읽기(read), 오염(dirtied), 쓰기(written)된 비중복 버퍼의 개수를 보여 줍니다. ANALYZE 옵션은 BUFFERS 옵션을 암묵적으로 활성화합니다. 이것이 바람직하지 않다면 BUFFERS를 명시적으로 비활성화할 수 있습니다.
EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10.00 loops=1)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
Heap Blocks: exact=10
-> BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0.00 loops=1)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100.00 loops=1)
Index Cond: (unique1 < 100)
Index Searches: 1
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999.00 loops=1)
Index Cond: (unique2 > 9000)
Index Searches: 1
Planning Time: 0.162 ms
Execution Time: 0.143 ms
EXPLAIN ANALYZE는 실제로 쿼리를 실행하므로, 쿼리가 출력할 수 있는 어떤 결과는 EXPLAIN 데이터를 출력하기 위해 버려지더라도 부작용은 평소처럼 발생한다는 점을 명심하세요. 테이블을 바꾸지 않고 데이터 수정 쿼리를 분석하고 싶다면, 이후에 명령을 롤백하면 됩니다. 예를 들어 다음과 같습니다.
BEGIN;
EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Update on tenk1 (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1)
-> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100.00 loops=1)
Recheck Cond: (unique1 < 100)
Heap Blocks: exact=90
Buffers: shared hit=4 read=2
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1)
Index Cond: (unique1 < 100)
Index Searches: 1
Buffers: shared read=2
Planning Time: 0.151 ms
Execution Time: 1.856 ms
ROLLBACK;
이 예제에서 볼 수 있듯이 쿼리가 INSERT, UPDATE, DELETE, MERGE 명령일 때, 테이블 변경을 적용하는 실제 작업은 최상위 Insert, Update, Delete, Merge 플랜 노드가 수행합니다. 이 노드 아래의 플랜 노드들은 이전 행을 찾고/또는 새 데이터를 계산하는 작업을 수행합니다. 그래서 위에서 우리는 지금까지 봐온 것과 같은 종류의 비트맵 테이블 스캔이 있고, 그 출력이 갱신된 행을 저장하는 Update 노드로 공급되는 것을 볼 수 있습니다. 데이터 수정 노드가 상당한 실행 시간을 차지할 수 있음에도(여기서는 시간의 대부분을 차지함) 플래너는 현재 그 작업을 설명하는 비용 추정치에 아무것도 더하지 않는다는 점을 언급할 가치가 있습니다. 수행할 작업이 모든 올바른 쿼리 플랜에서 동일하므로, 플랜 결정에 영향을 주지 않기 때문입니다.
UPDATE, DELETE, MERGE 명령이 파티션 테이블이나 상속 계층(inheritance hierarchy)에 영향을 줄 때 출력이 다음과 같이 보일 수 있습니다.
EXPLAIN UPDATE gtest_parent SET f1 = CURRENT_DATE WHERE f2 = 101;
QUERY PLAN
----------------------------------------------------------------------------------------
Update on gtest_parent (cost=0.00..3.06 rows=0 width=0)
Update on gtest_child gtest_parent_1
Update on gtest_child2 gtest_parent_2
Update on gtest_child3 gtest_parent_3
-> Append (cost=0.00..3.06 rows=3 width=14)
-> Seq Scan on gtest_child gtest_parent_1 (cost=0.00..1.01 rows=1 width=14)
Filter: (f2 = 101)
-> Seq Scan on gtest_child2 gtest_parent_2 (cost=0.00..1.01 rows=1 width=14)
Filter: (f2 = 101)
-> Seq Scan on gtest_child3 gtest_parent_3 (cost=0.00..1.01 rows=1 width=14)
Filter: (f2 = 101)
이 예제에서 Update 노드는 세 개의 자식 테이블을 고려해야 하지만, 원래 언급된 파티션 테이블은 고려하지 않습니다. (그 테이블은 데이터를 저장하지 않기 때문입니다.) 그래서 테이블당 하나씩, 세 개의 입력 스캔 서브플랜이 있습니다. 명확성을 위해 Update 노드는 해당 서브플랜과 같은 순서로 갱신될 구체적인 대상 테이블을 보여 주도록 주석 처리됩니다.
EXPLAIN ANALYZE가 보여 주는 Planning time(계획 수립 시간)은 파싱된 쿼리에서 쿼리 플랜을 생성하고 최적화하는 데 걸린 시간입니다. 구문 분석이나 재작성을 포함하지 않습니다.
EXPLAIN ANALYZE가 보여 주는 Execution time(실행 시간)은 실행기 시작 및 종료 시간과 실행되는 트리거를 실행하는 시간을 포함하지만, 구문 분석, 재작성, 계획 수립 시간은 포함하지 않습니다. BEFORE 트리거 실행에 소비된 시간은 있다면 관련 Insert, Update, Delete 노드의 시간에 포함됩니다. 하지만 AFTER 트리거 실행에 소비된 시간은 전체 플랜 완료 후에 실행되므로 거기에 집계되지 않습니다. 각 트리거(BEFORE 또는 AFTER)에서 보낸 총 시간도 별도로 표시됩니다. 지연 제약 트리거(deferred constraint trigger)는 트랜잭션 끝까지 실행되지 않으므로 EXPLAIN ANALYZE가 전혀 고려하지 않는다는 점도 주의하세요.
최상위 노드에 대해 표시된 시간은 쿼리의 출력 데이터를 표시 가능한 형태로 변환하거나 클라이언트로 보내는 데 필요한 시간을 포함하지 않습니다. EXPLAIN ANALYZE는 데이터를 클라이언트로 보내지 않지만, SERIALIZE 옵션을 지정하면 쿼리의 출력 데이터를 표시 가능한 형태로 변환하고 그에 필요한 시간을 측정하라고 지시할 수 있습니다. 그 시간은 별도로 표시되며, 총 Execution time에도 포함됩니다.
14.1.3. 주의사항 (Caveats)
EXPLAIN ANALYZE로 측정한 실행 시간이 같은 쿼리의 일반 실행과 달라질 수 있는 두 가지 중요한 방식이 있습니다. 첫째, 출력 행이 클라이언트로 전달되지 않으므로 네트워크 전송 비용이 포함되지 않습니다. SERIALIZE가 지정되지 않으면 I/O 변환 비용도 포함되지 않습니다. 둘째, EXPLAIN ANALYZE가 추가하는 측정 오버헤드가 상당할 수 있는데, 특히 느린 gettimeofday() 운영체제 호출이 있는 시스템에서 그렇습니다. pg_test_timing 도구를 사용해 시스템에서 타이밍의 오버헤드를 측정할 수 있습니다.
EXPLAIN 결과를 실제로 테스트하는 상황과 많이 다른 상황으로 외삽해서는 안 됩니다. 예를 들어 장난감 크기 테이블에서의 결과가 큰 테이블에도 적용된다고 가정할 수 없습니다. 플래너의 비용 추정치는 선형적이지 않으므로, 더 크거나 작은 테이블에서 다른 플랜을 선택할 수 있습니다. 극단적인 예로, 디스크 페이지 하나만 차지하는 테이블에서는 인덱스가 있든 없든 거의 항상 순차 스캔 플랜을 얻게 됩니다. 플래너는 어떤 경우든 테이블을 처리하는 데 디스크 페이지 읽기 한 번이 걸린다는 것을 알므로, 인덱스를 보기 위해 추가 페이지 읽기를 소비할 가치가 없다고 판단합니다. (위의 polygon_tbl 예제에서 이런 일이 일어나는 것을 보았습니다.)
실제 값과 추정 값이 잘 맞지 않지만 실제로는 아무 문제가 없는 경우도 있습니다. 그런 경우 중 하나는 플랜 노드 실행이 LIMIT 또는 유사한 효과에 의해 중단될 때입니다. 예를 들어 앞서 사용한 LIMIT 쿼리에서,
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2.00 loops=1)
Buffers: shared hit=16
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2.00 loops=1)
Index Cond: (unique2 > 9000)
Filter: (unique1 < 100)
Rows Removed by Filter: 287
Index Searches: 1
Buffers: shared hit=16
Planning Time: 0.077 ms
Execution Time: 0.086 ms
Index Scan 노드의 추정 비용과 행 수는 끝까지 실행되는 것처럼 표시됩니다. 하지만 실제로는 Limit 노드가 두 개를 얻은 뒤 행 요청을 중단했으므로, 실제 행 수는 2개뿐이고 실행 시간도 비용 추정치가 암시하는 것보다 짧습니다. 이는 추정 오류가 아니라, 추정치와 실제 값이 표시되는 방식의 차이일 뿐입니다.
병합 조인에도 경험 없는 사람을 혼동시킬 수 있는 측정 인공물(measurement artifact)이 있습니다. 병합 조인은 한 입력이 소진되고 다른 입력의 다음 키 값이 그 입력의 마지막 키 값보다 크면 한 입력 읽기를 중단합니다. 그런 경우 더 이상 매치가 있을 수 없으므로 첫 번째 입력의 나머지를 스캔할 필요가 없습니다. 이로 인해 자식 중 하나를 전부 읽지 못하게 되는데, LIMIT에서 언급한 것과 같은 결과가 나타납니다. 또한 외부(첫 번째) 자식에 중복 키 값을 가진 행이 있으면, 내부(두 번째) 자식은 백업되어 그 키 값과 일치하는 행 부분에 대해 다시 스캔됩니다. EXPLAIN ANALYZE는 같은 내부 행의 이러한 반복 출력을 실제 추가 행인 것처럼 셉니다. 외부 중복이 많을 때, 내부 자식 플랜 노드의 보고된 실제 행 수는 내부 관계에 실제로 있는 행 수보다 상당히 커질 수 있습니다.
BitmapAnd와 BitmapOr 노드는 구현 제한 때문에 실제 행 수를 항상 0으로 보고합니다.
보통 EXPLAIN은 플래너가 만든 모든 플랜 노드를 표시합니다. 그러나 실행기가 계획 수립 시점에는 사용할 수 없었던 매개변수 값을 바탕으로, 특정 노드가 행을 만들 수 없으므로 실행할 필요가 없다고 판단할 수 있는 경우가 있습니다. (현재 이는 파티션 테이블을 스캔하는 Append 또는 MergeAppend 노드의 자식 노드에서만 일어날 수 있습니다.) 이 경우 그 플랜 노드들은 EXPLAIN 출력에서 생략되고 대신 Subplans Removed:N 주석이 나타납니다.