EXPLAIN
EXPLAIN
명령문의 실행 계획(execution plan)을 보여 주는 명령이에요. 쿼리를 실제로 실행하지 않고도 PostgreSQL 플래너가 세운 계획이 어떤지 확인해 볼 수 있죠. ANALYZE 옵션을 주면 실제로 실행해서 실행 시간 통계까지 확인할 수 있어요. 쿼리 성능을 진단하고 튜닝할 때 가장 먼저 쓰는 명령이에요.
출처: PostgreSQL 문서
본문
개요 (Synopsis)
EXPLAIN [ ( option [, ...] ) ] statement
where option can be one of:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
SETTINGS [ boolean ]
GENERIC_PLAN [ boolean ]
BUFFERS [ boolean ]
SERIALIZE [ { NONE | TEXT | BINARY } ]
WAL [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
MEMORY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
설명 (Description)
이 명령은 PostgreSQL 플래너가 주어진 명령문에 대해 생성한 실행 계획을 보여 줘요. 실행 계획은 명령문이 참조하는 테이블을 어떻게 스캔할지(단순 순차 스캔, 인덱스 스캔 등), 그리고 여러 테이블을 참조한다면 각 입력 테이블에서 필요한 행을 모으기 위해 어떤 조인 알고리즘을 사용할지 보여 줘요.
표시 내용 중 가장 중요한 부분은 예상 명령문 실행 비용(estimated execution cost)이에요. 이는 플래너가 명령문을 실행하는 데 걸릴 시간을 추정한 값이죠(임의의 비용 단위로 측정되지만, 관례상 디스크 페이지 읽기 횟수를 의미해요). 실제로는 두 개의 숫자가 표시되는데, 첫 번째 행을 반환하기 전까지의 시작 비용(start-up cost)과 모든 행을 반환하는 데 드는 총 비용(total cost)이에요. 대부분의 쿼리에서는 총 비용이 중요하지만, EXISTS 안의 서브쿼리 같은 상황에서는 플래너가 총 비용 대신 가장 작은 시작 비용을 선택해요(실행기는 어차피 행을 하나 얻으면 멈추기 때문이에요). 또한 LIMIT 절로 반환할 행 수를 제한하면, 플래너는 끝점 비용 사이를 적절히 보간해 어떤 계획이 정말 가장 저렴한지 추정해요.
ANALYZE 옵션을 주면 명령문이 계획만 세워지는 게 아니라 실제로 실행돼요. 그러면 실제 실행 시간 통계가 표시에 추가되는데, 각 계획 노드에 소요된 총 경과 시간(밀리초)과 실제로 반환한 행 수가 포함돼요. 플래너의 추정치가 실제와 얼마나 가까운지 확인하는 데 유용해요.
IMPORTANT:
ANALYZE옵션을 사용하면 명령문이 실제로 실행된다는 점을 명심하세요.EXPLAIN은SELECT가 반환할 출력은 버리지만, 명령문의 다른 부수 효과는 평소처럼 발생해요.INSERT,UPDATE,DELETE,MERGE,CREATE TABLE AS,EXECUTE명령문에EXPLAIN ANALYZE를 쓰면서 명령이 실제 데이터에 영향을 주지 않게 하려면, 이런 방법을 사용하세요.BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
파라미터 (Parameters)
ANALYZE — 명령을 실제 실행하고 실제 실행 시간과 기타 통계를 보여 줘요. 기본값은 FALSE예요.
VERBOSE — 계획에 관한 추가 정보를 표시해요. 구체적으로는 계획 트리의 각 노드에 대한 출력 열 목록을 포함하고, 테이블·함수 이름을 스키마로 한정하며, 표현식의 변수에 항상 범위 테이블 별칭을 붙이고, 통계가 표시되는 각 트리거의 이름을 항상 출력해요. 쿼리 식별자(compute_query_id)가 계산되어 있다면 그것도 표시돼요. 기본값은 FALSE예요.
COSTS — 각 계획 노드의 예상 시작·총 비용과 예상 행 수, 각 행의 예상 너비에 대한 정보를 포함해요. 기본값은 TRUE예요.
SETTINGS — 구성 파라미터에 대한 정보를 포함해요. 구체적으로는 쿼리 계획에 영향을 주면서 기본 제공 기본값과 값이 다른 옵션을 포함해요. 기본값은 FALSE예요.
GENERIC_PLAN — 명령문에 $1 같은 파라미터 자리표시자가 들어 있는 것을 허용하고, 그 파라미터 값에 의존하지 않는 일반적인 계획(generic plan)을 생성해요. 일반 계획과 파라미터를 지원하는 명령문 유형에 대한 자세한 내용은 PREPARE를 참고하세요. 이 파라미터는 ANALYZE와 함께 사용할 수 없어요. 기본값은 FALSE예요.
BUFFERS — 버퍼 사용에 대한 정보를 포함해요. 구체적으로는 공유 블록이 hit·read·dirtied·written된 횟수, 로컬 블록이 hit·read·dirtied·written된 횟수, temp 블록이 read·written된 횟수, 그리고 track_io_timing이 활성화된 경우 데이터 파일 블록·로컬 블록·임시 파일 블록을 읽고 쓰는 데 걸린 시간(밀리초)을 포함해요. hit은 필요할 때 블록이 이미 캐시에 있어서 읽기를 피했다는 뜻이에요. 공유 블록은 일반 테이블·인덱스의 데이터를 담고, 로컬 블록은 임시 테이블·인덱스의 데이터를 담으며, temp 블록은 정렬, 해시, Materialize 계획 노드 등에서 사용하는 단기 작업 데이터를 담아요. dirtied된 블록 수는 이 쿼리가 변경한 이전에 수정되지 않은 블록의 수를, written된 블록 수는 쿼리 처리 중 이 백엔드가 캐시에서 내보낸 이전에 더럽혀진 블록의 수를 나타내요. 상위 노드에 표시된 블록 수에는 모든 하위 노드가 사용한 블록이 포함돼요. 텍스트 형식에서는 0이 아닌 값만 출력돼요. 버퍼 정보는 ANALYZE를 사용하면 자동으로 포함돼요.
SERIALIZE — 쿼리 출력 데이터를 *직렬화(serializing)*하는 비용, 즉 클라이언트로 보내기 위해 텍스트나 바이너리 형식으로 변환하는 비용에 대한 정보를 포함해요. 데이터 타입 출력 함수가 비싸거나 TOAST된 값을 외부 저장소에서 가져와야 한다면, 이는 쿼리를 일반 실행할 때 걸리는 시간의 상당 부분을 차지할 수 있어요. EXPLAIN의 기본 동작인 SERIALIZE NONE은 이런 변환을 수행하지 않아요. SERIALIZE TEXT나 SERIALIZE BINARY를 지정하면 적절한 변환이 수행되고, 그 변환에 걸린 시간이 측정돼요(TIMING OFF를 지정하지 않았다면). BUFFERS 옵션도 함께 지정하면 변환에 관련된 버퍼 접근도 집계돼요. 다만 어떤 경우에도 EXPLAIN이 결과 데이터를 실제로 클라이언트에 보내지는 않으므로, 네트워크 전송 비용은 이 방법으로 조사할 수 없어요. 직렬화는 ANALYZE가 활성 상태일 때만 켤 수 있어요. SERIALIZE를 인자 없이 쓰면 TEXT로 간주돼요.
WAL — WAL 레코드 생성에 대한 정보를 포함해요. 구체적으로는 레코드 수, 전체 페이지 이미지(full page image, fpi) 수, 생성된 WAL 크기(바이트), WAL 버퍼가 가득 찬 횟수를 포함해요. 텍스트 형식에서는 0이 아닌 값만 출력돼요. 이 파라미터는 ANALYZE가 활성 상태일 때만 사용할 수 있어요. 기본값은 FALSE예요.
TIMING — 출력에 각 노드의 실제 시작 시간과 소요 시간을 포함해요. 시스템 시계를 반복적으로 읽는 오버헤드는 일부 시스템에서 쿼리를 크게 느리게 만들 수 있으므로, 정확한 시간이 아니라 실제 행 수만 필요할 때는 이 파라미터를 FALSE로 설정하는 게 유용할 수 있어요. 이 옵션으로 노드 수준 타이밍을 꺼도 명령문 전체의 실행 시간은 항상 측정돼요. 이 파라미터는 ANALYZE가 활성 상태일 때만 사용할 수 있어요. 기본값은 TRUE예요.
SUMMARY — 쿼리 계획 뒤에 요약 정보(예: 합산된 타이밍 정보)를 포함해요. 요약 정보는 ANALYZE를 사용하면 기본적으로 포함되지만, 그 외에는 기본적으로 포함되지 않으며 이 옵션으로 켤 수 있어요. EXPLAIN EXECUTE의 계획 시간에는 캐시에서 계획을 가져오는 데 걸리는 시간과 필요한 경우 재계획(re-planning)에 걸리는 시간이 포함돼요.
MEMORY — 쿼리 계획 단계의 메모리 소비에 대한 정보를 포함해요. 구체적으로는 플래너가 메모리 내 구조에 사용한 정확한 저장 용량과, 할당 오버헤드를 고려한 총 메모리를 포함해요. 기본값은 FALSE예요.
FORMAT — 출력 형식을 지정해요. TEXT, XML, JSON, YAML 중 하나예요. 비텍스트 출력은 텍스트 출력 형식과 같은 정보를 담지만, 프로그램이 파싱하기 더 쉬워요. 기본값은 TEXT예요.
*boolean* — 선택한 옵션을 켤지 끌지 지정해요. 옵션을 켜려면 TRUE, ON, 1을, 끄려면 FALSE, OFF, 0을 쓸 수 있어요. boolean 값은 생략할 수도 있으며, 그 경우 TRUE로 간주돼요.
*statement* — 실행 계획을 보고 싶은 SELECT, INSERT, UPDATE, DELETE, MERGE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, CREATE MATERIALIZED VIEW AS 명령문 중 하나예요.
출력 (Outputs)
이 명령의 결과는 statement에 대해 선택된 계획의 텍스트 설명으로, 선택적으로 실행 통계가 주석으로 달려요. 제공되는 정보에 대한 설명은 14.1절을 참고하세요.
참고 (Notes)
PostgreSQL 쿼리 플래너가 쿼리를 최적화할 때 합리적으로 정보에 입각한 결정을 내리도록, 쿼리에 사용되는 모든 테이블에 대해 pg_statistic 데이터가 최신 상태여야 해요. 보통은 autovacuum 데몬이 이를 자동으로 처리해요. 하지만 테이블의 내용이 최근 상당히 바뀌었다면, autovacuum이 변경을 따라잡을 때까지 기다리는 대신 수동으로 ANALYZE를 실행해야 할 수도 있어요.
실행 계획의 각 노드 실행 시간 비용을 측정하기 위해, 현재 EXPLAIN ANALYZE 구현은 쿼리 실행에 프로파일링 오버헤드를 추가해요. 결과적으로 어떤 쿼리에 EXPLAIN ANALYZE를 실행하면 쿼리를 일반 실행할 때보다 훨씬 오래 걸릴 수 있어요. 오버헤드의 크기는 쿼리의 성격과 사용 중인 플랫폼에 따라 달라져요. 최악의 경우는 실행당 시간이 매우 적게 필요한 계획 노드가 있고, 시간을 얻기 위한 운영 체제 호출이 상대적으로 느린 머신에서 발생해요.
예제 (Examples)
단일 integer 열과 10000개 행을 가진 테이블에 대한 간단한 쿼리의 계획을 보여 주려면:
EXPLAIN SELECT * FROM foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
(1 row)
같은 쿼리를 JSON 출력 형식으로 보여 주면:
EXPLAIN (FORMAT JSON) SELECT * FROM foo;
QUERY PLAN
--------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan",+
"Relation Name": "foo", +
"Alias": "foo", +
"Startup Cost": 0.00, +
"Total Cost": 155.00, +
"Plan Rows": 10000, +
"Plan Width": 4 +
} +
} +
]
(1 row)
인덱스가 있고 인덱스를 사용할 수 있는 WHERE 조건을 쓰는 쿼리라면, EXPLAIN은 다른 계획을 보여 줄 수 있어요:
EXPLAIN SELECT * FROM foo WHERE i = 4;
QUERY PLAN
--------------------------------------------------------------
Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4)
Index Cond: (i = 4)
(2 rows)
같은 쿼리를 YAML 형식으로 보여 주면:
EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
QUERY PLAN
-------------------------------
- Plan: +
Node Type: "Index Scan" +
Scan Direction: "Forward"+
Index Name: "fi" +
Relation Name: "foo" +
Alias: "foo" +
Startup Cost: 0.00 +
Total Cost: 5.98 +
Plan Rows: 1 +
Plan Width: 4 +
Index Cond: "(i = 4)"
(1 row)
XML 형식은 독자의 연습 문제로 남겨 둘게요.
비용 추정을 생략한 같은 계획을 보여 주면:
EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;
QUERY PLAN
----------------------------
Index Scan using fi on foo
Index Cond: (i = 4)
(2 rows)
집계 함수를 사용하는 쿼리의 계획 예시를 보여 주면:
EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=23.93..23.93 rows=1 width=4)
-> Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)
Index Cond: (i < 10)
(3 rows)
준비된 쿼리의 실행 계획을 표시하는 EXPLAIN EXECUTE 사용 예시를 보여 주면:
PREPARE query(int, int) AS SELECT sum(bar) FROM test
WHERE id > $1 AND id < $2
GROUP BY foo;
EXPLAIN ANALYZE EXECUTE query(100, 200);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10.00 loops=1)
Group Key: foo
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=4
-> Index Scan using test_pkey on test (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99.00 loops=1)
Index Cond: ((id > 100) AND (id < 200))
Index Searches: 1
Buffers: shared hit=4
Planning Time: 0.244 ms
Execution Time: 0.073 ms
(10 rows)
당연히 여기 나온 특정 숫자는 관련 테이블의 실제 내용에 따라 달라져요. 또한 숫자나 선택된 쿼리 전략은 플래너 개선으로 인해 PostgreSQL 릴리스마다 달라질 수 있다는 점도 참고하세요. 게다가 ANALYZE 명령은 데이터 통계를 추정할 때 무작위 샘플링을 사용하므로, 테이블의 실제 데이터 분포가 변하지 않았더라도 ANALYZE를 새로 실행하면 비용 추정치가 바뀔 수 있어요.
앞선 예시는 EXECUTE에 주어진 특정 파라미터 값에 대한 “커스텀” 계획을 보여 줬다는 점을 주목하세요. 파라미터화된 쿼리의 일반 계획(generic plan)을 보고 싶을 수도 있는데, GENERIC_PLAN으로 할 수 있어요:
EXPLAIN (GENERIC_PLAN)
SELECT sum(bar) FROM test
WHERE id > $1 AND id < $2
GROUP BY foo;
QUERY PLAN
-------------------------------------------------------------------------------
HashAggregate (cost=26.79..26.89 rows=10 width=12)
Group Key: foo
-> Index Scan using test_pkey on test (cost=0.29..24.29 rows=500 width=8)
Index Cond: ((id > $1) AND (id < $2))
(4 rows)
이 경우 파서가 $1과 $2가 id와 같은 데이터 타입이어야 한다고 올바르게 추론했으므로, PREPARE에서 파라미터 타입 정보가 없어도 문제가 되지 않았어요. 다른 경우에는 파라미터 기호에 타입을 명시적으로 지정해야 할 수도 있는데, 다음과 같이 캐스팅해서 지정할 수 있어요:
EXPLAIN (GENERIC_PLAN)
SELECT sum(bar) FROM test
WHERE id > $1::integer AND id < $2::integer
GROUP BY foo;
호환성 (Compatibility)
SQL 표준에는 EXPLAIN 명령문이 정의되어 있지 않아요.
다음 문법은 PostgreSQL 9.0 이전에 사용되던 것으로, 여전히 지원돼요:
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
이 문법에서는 옵션을 정확히 표시된 순서로 지정해야 한다는 점에 유의하세요.
더 알아보기 (Learn more)
EXPLAIN의 출력을 해석하는 방법은 “Using EXPLAIN” 절에서 더 자세히 볼 수 있어요. 준비된 명령문의 일반 계획과 관련해서는 PREPARE 문서를, 통계를 갱신하는 방법은 ANALYZE 문서를 함께 보면 좋아요.