설명

이 명령은 지정한 쿼리문에 대해서 PostgreSQL 실행계획기가 만든 실행 계획을 보여준다. 그 실행 계획은 원하는 자료를 출력하기 위해서, 어떤 테이블이 테이블 전체 순차 검색을 하는지, 인덱스 검색을 하는지를 보여준다. 또한 여러 테이블이 조인이 될 경우 그 각 테이블들의 조인 알고리즘은 어떤 것을 사용할 것인지를 보여준다.

이 명령의 출력 결과에서 가장 중요한 부분은 각 단계별로 실행 될 작업의 비용이다. 이 비용은 실행계획기가 그 작업을 실행하는데 그 만큼의 비용이 들 것이다고 미리 짐작한 값이다. (이 비용은 관례적으로 디스크 페이지를 읽는데 드는 비용이 기준이 된다.) 비용은 두 부분의 숫자로 표시되는데, 앞부분은 그 작업의 결과로 첫번째 로우를 리턴하기 전까지의 비용이며, 뒷부분은 마지막 로우를 리턴할 때까지의 비용이다. 대부분의 쿼리들은 그 쿼리를 실행하는데 총 비용이 어느 정도인가를 파악하면 되지만, EXISTS 구문을 사용하는 서브쿼리와 같은 경우는 가장 적은 총 비용(뒷부분 값)보다는 가장 적은 시작 비용(앞부분 값)을 사용하도록 쿼리 계획을 짠다. (상식적으로 생각해도 EXISTS 구문은 그 서브 쿼리의 첫 로우가 리턴되면 해당 작업은 중지 해도 되기 때문이다.) 한편, LIMIT 구문의 경우는, 그 출력 범위가 전체 가운데 어느 부분인지를 파악해서, 총비용과 함께 고려해서 그 비용을 계산한다.

ANALYZE 옵션을 사용하면, 실제 해당 쿼리를 실행하고, 추청 비용과 함께 소요 비용, 소요 시간도 실제 처리된 각 계획 노드별 전체 로우 수도 보여준다. 이 옵션은 실행계획기가 추정하는 작업이 실 작업과 비교해서 얼마나 정확한지를 확인하는데 유용하게 사용된다.

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

ANALYZE 옵션과, VERBOSE 옵션만 괄호 없이 사용할 수 있다. 이는 PostgreSQL 9.0 이전 버전의 하위 호환성 때문에 계속 유지 되고 있다. 그 외 다른 옵션들은 새로운 구문에 따라 괄호 안에서 사용해야 한다.

매개 변수

출력

이 명령은 인자로 지정한 쿼리문에 대한 실행 계획을 사람이 읽을 수 있도록 글 형태로 출력하며, 추가로 실행 통계 정보도 포함하기도 한다. 14.1절에서는 이 명령을 사용하는 방법, 출력 결과를 읽고 쿼리를 튜닝하는 방법등에 대해서 소개하고 있다.

참고

PostgreSQL 쿼리 계획기가 가장 합리적인 실행 계획을 짤 수 있도록 하려면, 실행할 쿼리에서 사용되는 모든 테이블에 대한 최신 통계 정보가 pg_statistic 테이블의 자료로 수집되어야 한다. 이런 통계 수집 작업은 일반적으로 autovacuum daemon이 자동으로 처리한다. 하지만, 그 대상 테이블의 내용이 최근에 상당히 바뀌었고, autovacuum 데몬이 아직 그 변경 사항을 반영하지 않고 있다면, 사용자가 직접 ANALYZE 명령으로 통계 정보를 갱신할 필요가 있다.

실시간 비용을 확인하기 위해 EXPLAIN ANALYZE 명령을 사용한다면, 이 비용 계산 자체가 또 하나의 비용이 되어 해당 쿼리의 부가 비용이 발생하게 된다. 즉, 그냥 그 쿼리만 실행했을 때보다 EXPLAIN ANALYZE 명령으로 쿼리를 실행했을 때가 시간이 더 오래 걸 수가 있다. 이 부가 비용은 그 쿼리의 복잡도와 사용하는 플렛폼에 의존적이다. 최악의 경우는 아주 간단한 쿼리여서 실행하는데 얼마 걸리지도 않는데, 그 실행 시간을 계산하기 위해 시스템 시간을 구하는 것이 오히려 더 걸려 전체 실행 계획 결과 이상하게 나오는 경우다.

예제

다음은 한 테이블이 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)

미리 준비한 쿼리(prepared query)를 실행 할 때의 실행 계획은 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=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
   ->  Index Scan using test_pkey on test  (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)
         Index Cond: ((id > $1) AND (id < $2))
 Total runtime: 0.851 ms
(4 rows)

물론 여기서 각 수치들은 실제 테이블의 내용에 따라 다르게 보일 것이다. 또한 수치 뿐만 아니라, PostgreSQL 배포판에 따라 실행계획기의 성능이 개선되어 계획 자체가 변경되어 보일 수 있음을 감안해야 한다. 추가로 이 작업의 비용 계산에 있어 그 근거 자료가 되는 것은 ANALYZE 명령으로 수집된 샘플 자료라는 점이다. 그러므로, 실 자료는 달라진 것이 전혀 없어도, ANALYZE 명령이 실행되기 전 후로 실행계획이 달라질 수 있음을 알고 있어야한다.

호환성

EXPLAIN 구문은 표준 SQL에는 정의되어 있지않다.

관련 항목