Explain 이란?

Explain은 PostgreSQL의 쿼리 최적화 도구로, 지정한 SQL 쿼리에 대해 PostgreSQL이 생성하는 실행 계획(Execution Plan)을 확인할 수 있게 해줍니다.

실행 계획은 원하는 자료를 출력하기 위해서, 어떤 테이블이 테이블 전체 순차 검색을 하는지, 인덱스 검색을 하는지를 보여줍니다.

또한 여러 테이블이 조인이 될 경우 그 각 테이블들의 조인 알고리즘은 어떤 것을 사용할 것인지를 보여줍니다.

Explain 시놉시스

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

옵션으로는 ANALYZE, VERBOSE, COSTS, SETTINGS, BUFFERS, WAL, TIMING, SUMMARY, FORMAT 등이 있으며, 각 옵션은 더 구체적인 정보를 제공합니다.

테스트 테이블 사이즈 및 인덱스:

# 10,000,000 테이블 (1천만 테이블)

pgbench -h localhost -p 5432 -U postgres -d test -i -s 100
dropping old tables...
creating tables...
generating data (client-side)...
10000000 of 10000000 tuples (100%) done (elapsed 85.17 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 133.24 s (drop tables 0.06 s, create tables 0.04 s, client-side generate 87.92 s, vacuum 9.52 s, primary keys 35.72 s).

[opensql@postgresql:~]$ psql
psql (16.4)
Type "help" for help.

postgres=# l+
                                                                                         List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |                       | 7556 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +| 7345 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |            |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +| 7572 kB | pg_default | default template for new databases
           |          |          |                 |             |             |            |           | postgres=CTc/postgres |         |            |
 test      | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |                       | 1503 MB | pg_default |
(4 rows)


test=# dt+
                                         List of relations
 Schema |       Name       | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+------------------+-------+----------+-------------+---------------+---------+-------------
 public | pgbench_accounts | table | postgres | permanent   | heap          | 1281 MB |
 public | pgbench_branches | table | postgres | permanent   | heap          | 40 kB   |
 public | pgbench_history  | table | postgres | permanent   | heap          | 0 bytes |
 public | pgbench_tellers  | table | postgres | permanent   | heap          | 80 kB   |
(4 rows)


test=# d+ pgbench_accounts
                                         Table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
 aid      | integer       |           | not null |         | plain    |             |              |
 bid      | integer       |           |          |         | plain    |             |              |
 abalance | integer       |           |          |         | plain    |             |              |
 filler   | character(84) |           |          |         | extended |             |              |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Access method: heap
Options: fillfactor=100




test db 사이즈 = 1.5GB
pgbench_accounts 테이블 사이즈 = 1.281GB
pgbench_accounts 테이블 인덱스 = pgbench_accounts_pkey

pgbench_accounts 테이블 구성:

테스트 Admin Tool : pgAdmin 4

개요 및 기본 기능 : https://tmaxtibero.blog/5355/

옵션

• ANALYZE

커멘드를 실행하고 실제 실행 시간과 기타 통계를 표시합니다. 디폴트 값은 FALSE 입니다.

• VERBOSE

계획에 관한 추가 정보를 표시합니다. 구체적으로, 계획 트리의 각 노드에 대한 출력 열 목록, 스키마 테이블, 함수명을 포함하며, 표현식의 변수에 항상 범위 테이블 별칭을 레이블하고, 통계가 표시되는 각 트리거의 이름을 항상 출력합니다. 쿼리 식별자가 계산된 경우에도 표시됩니다. 디폴트 값은 FALSE 입니다.

• COSTS

각 계획 노드의 예상 시작 및 총 비용, 예상 행 수 및 각 행의 예상 너비에 대한 정보를 포함합니다. 디폴트 값은 TRUE 입니다.

• SETTINGS

구성 매개변수에 대한 정보를 포함합니다. 구체적으로, 내장된 기본값과 다른 값으로 쿼리 계획에 영향을 미치는 옵션을 포함합니다. 디폴트 값은 FALSE 입니다.

• BUFFERS

버퍼 사용에 대한 정보를 포함합니다. 구체적으로, 공유 블록, 로컬 블록, 임시 블록의 HIT, 읽기, 쓰기, 변경 내용을 포함합니다. 결과에 나오는 항목 중 dirted 블럭은 해당쿼리로 변경된 블록의 수를, written 블럭은 해당 쿼리가 실행되며 세션이 공유버퍼에서 변경된 블록을 디스크에 기록한 수를 출력합니다. 텍스트 형식에서는 0이 아닌 값만 출력됩니다. 디폴트 값은 FALSE 입니다.

• WAL

WAL 레코드 생성에 대한 정보를 포함합니다. 구체적으로, 레코드 수, 전체 페이지 이미지 수(fpi) 및 생성된 WAL 양(바이트)을 포함합니다. 텍스트 형식에서는 0이 아닌 값만 출력됩니다. 이 매개변수는  ANALYZE 가 활성화된 경우에만 사용할 수 있습니다. 디폴트 값은 FALSE 입니다.

• TIMING

실제 각 노드별 소모 시간을 보여줍니다. 반복적으로 시스템 시간을 확인하는데서 오는 오버헤드는 쿼리의 성능을 굉장히 저하시킬 수 있기 때문에 정확한 시간이 필요한 것이 아니라면 FALSE로 두는 것이 좋습니다. 다만 FALSE 상태로 두더라도 해당 쿼리의 총 실행시간은 항상 계산됩니다. 이 매개변수는  ANALYZE 가 활성화된 경우에만 사용할 수 있습니다. 디폴트 값은 TRUE 입니다.

• SUMMARY

쿼리 계획 뒤에 요약 정보(예: 총 TIMING 정보)를 포함합니다. 이 매개변수는 기본적으로 ANALYZE사용될 때 포함되지만, 그렇지 않으면 이 옵션을 사용하여 활성화할 수 있습니다. 계획 시간에는 EXPLAIN EXECUTE캐시에서 계획을 가져오는 데 필요한 시간이 포함되며, 재계획에 필요한 시간이 포함되는 경우도 있습니다.

• FORMAT

실행계획 값을 TEXT, XML, JSON, YAML의 타입으로 출력합니다. 내용은 모두 동일하며, 기본 출력값은 TEXT 형식입니다. 다만 pgAdmin에서는 FORMAT GUI 가 없기에, 아래와 같이 명령어를 입력해야 합니다.

**EXPLAIN (FORMAT 타입) 쿼리문**

e.g.
EXPLAIN (FORMAT JSON) SELECT * FROM PGBENCH_ACCOUNTS;

JSON 타입 출력예시:

XML 타입 출력예시:

YAML 타입 출력예시:

EXPLAIN 항목별 의미

1. EXPLAIN 출력시 항목

실행계획을 위해 가장 기본적으로 사용하는 구문은 EXPLAIN 입니다. Admin 툴 사용시 GUI 에 Explain 항목이 따로 있습니다.

(pgAdmin4 기준)

pgAdmin 출력예시:

리눅스 출력예시:

test=# EXPLAIN SELECT * FROM PGBENCH_ACCOUNTS;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..263935.00 rows=10000000 width=97)
(1 row)

테스트 테이블의 1천만개의 행을 조건 없이 조회했을 때의 플랜 조회입니다. 기본적으로 cost, rows, width 가 출력됩니다.

• COST

cost는 예상비용입니다. cost의 계산식은 아래와 같습니다.

(읽은 디스크 페이지 * seq_page_cost ) + (스캔된 행 * cpu_tuple_cost )

# seq_page_cost 와 cpu_tuple_cost 의 기본값은 각각 1.0과 0.01 입니다. 

--읽은 디스크 페이지:

test=# SELECT relpages FROM pg_class WHERE relname = 'pgbench_accounts';
 relpages
----------
   163935
(1 row)

-- 스캔된 행 (전체 행)

test=# SELECT COUNT(*) FROM pgbench_accounts;
  count
----------
 10000000
(1 row)

(163935 * 1) + (10000000 * 0.01) = 263935
cost = 263935

• ROWS

rows는 스캔된 행의 갯수입니다. 특별한 경우가 없는 한, 전체 행의 갯수가 출력됩니다.

test=# SELECT COUNT(*) FROM pgbench_accounts;
  count
----------
 10000000
(1 row)

row = 10000000

• WIDTH

width는 컬럼별 평균 byte 수입니다. 아래의 명령어로 확인할 수 있습니다.

SELECT * FROM pg_stats WHERE tablename = 'pgbench_accounts';

# 너무 가독성이 떨어지기에 아래와 같이 조건을 걸어 출력합니다.

SELECT
    attname AS "Column Name",
    n_distinct AS "Distinct Values",
    avg_width AS "Average Width",
    substring(most_common_vals::text, 1, 50) || '...' AS "Most Common Values",
    substring(most_common_freqs::text, 1, 50) || '...' AS "Most Common Frequencies",
    correlation AS "Correlation"
FROM
    pg_stats
WHERE
    tablename = 'pgbench_accounts';
    
 Column Name | Distinct Values | Average Width |                  Most Common Values                   |                Most Common Frequencies                | Correlation
-------------+-----------------+---------------+-------------------------------------------------------+-------------------------------------------------------+-------------
 aid         |              -1 |             4 |                                                       |                                                       |           1
 bid         |             100 |             4 | {21,7,76,29,79,93,70,69,16,97,10,94,49,28,62,15,41... | {0.011433333,0.011266666,0.011266666,0.0112333335,... |           1
 abalance    |               1 |             4 | {0}...                                                | {1}...                                                |           1
 filler      |               1 |            85 | {"                                                ... | {1}...                                                |           1
(4 rows)

width = sum(avg_width)
4 + 4 + 4 + 85 = 97

width = 97

2. EXPLAIN ANALYZE 출력시 항목

ANALYZE 옵션을 넣고 조회했을 때의 출력값입니다. pgAdmin4 기준 GUI 에 Explain Analyze 항목이 따로 있습니다. pgAdmin4 에서는 visualize 기능을 지원하고 있습니다. pgAdmin4에서 실행계획은 아래와 같이 출력됩니다.

pgAdmin 출력예시:

리눅스 EXPLAIN ANALYZE 출력예시:

test=# explain analyze select count(*) from pgbench_accounts;
                                                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=133248.18..133248.19 rows=1 width=8) (actual time=1590.177..1594.604 rows=1 loops=1)
   ->  Gather  (cost=133247.97..133248.18 rows=2 width=8) (actual time=1589.966..1594.595 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=132247.97..132247.98 rows=1 width=8) (actual time=1585.179..1585.180 rows=1 loops=3)
               ->  Parallel Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..121831.30 rows=4166667 width=0) (actual time=0.055..1084.067 rows=3333333 loops=3)
                     Heap Fetches: 0
 Planning Time: 0.163 ms
 Execution Time: 1594.655 ms
(9 rows)

ANALYZE 옵션을 넣고 조회했을 때의 출력값입니다. 기본적으로 COST, ROWS, WIDTH 에 더해 ACTUAL TIME, LOOPS, PLANNING TIME, EXECUTION TIME 이 출력됩니다.

• ACTUAL TIME

실제 쿼리를 수행하는데 소모된 시간을 보여주며 [값 1.. 값 2] 형태로 표현됩니다. 앞부분(값 1)은 작업 결과로 첫 번째 row를 리턴하기 전까지 비용이고, 뒷부분(값 2)은 마지막 row를 리턴할 때까지의 비용입니다.

• LOOPS

row가 조회되는 횟수를 나타냅니다.

• PLANNING TIME

쿼리 플랜을 생성하고 최적화하는 데 걸린 시간을 나타냅니다. 여기에는 파싱이나 재작성한 시간은 포함되지 않습니다.

• EXECUTION TIME

실제 쿼리를 실행하는 데 걸린 시간입니다. 시작 및 종료 시간과 실행된 모든 트리거를 실행하는 시간이 포함되지만 구문 분석, 재작성 또는 계획 시간은 포함되지 않습니다.

실행계획 분석

리눅스 EXPLAIN ANALYZE 출력예시:

test=# explain analyze select count(*) from pgbench_accounts;

                                                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=133248.18..133248.19 rows=1 width=8) (actual time=1590.177..1594.604 rows=1 loops=1)
   ->  Gather  (cost=133247.97..133248.18 rows=2 width=8) (actual time=1589.966..1594.595 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=132247.97..132247.98 rows=1 width=8) (actual time=1585.179..1585.180 rows=1 loops=3)
               ->  Parallel Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..121831.30 rows=4166667 width=0) (actual time=0.055..1084.067 rows=3333333 loops=3)
                     Heap Fetches: 0
 Planning Time: 0.163 ms
 Execution Time: 1594.655 ms
(9 rows)

쿼리 플랜에 순서는 하단에서 상단으로 읽습니다. 위 예시의 경우, Parallel Index Only Scan → Partial Aggregate → Gather → Finalize Aggregate 순으로 읽습니다. pgAdmin4의 실행계획을 참조한다면 쿼리플랜을 한눈에 볼 수 있습니다.

1. Parallel Index Only Scan

Parallel Index Only Scan using pgbench_accounts_pkey on pgbench_accounts
  (cost=0.43..121831.30 rows=4166667 width=0) (actual time=0.055..1084.067 rows=3333333 loops=3)
  Heap Fetches: 0

• 기능:
  • pgbench_accounts 테이블의 기본 키(primary key) 인덱스를 사용해 데이터를 스캔(읽기)합니다.
  • 데이터를 읽을 때 테이블(Heap)을 직접 접근하지 않고 인덱스만 사용하므로 더 빠릅니다.
• 정보:
  • Heap Fetches: 0. 테이블 데이터(Heap)에 접근하지 않았음을 의미합니다.
  • 병렬 작업자당 처리된 행 수(rows): 한 작업자가 약 3333333개의 행을 처리.전체 행 수는 약 3333333 × 3 = 1000만 개입니다.
  • 실제 실행 시간(actual time): 각 작업자가 0.055ms에 시작해 약 1084ms 동안 작업했습니다.

2. Partial Aggregate

Partial Aggregate  (cost=132247.97..132247.98 rows=1 width=8) (actual time=1585.179..1585.180 rows=1 loops=3)

• 기능:
  • 각 작업자(worker)가 담당하는 데이터의 부분 합계를 계산합니다.
• 정보:
  • 실제 실행 시간(actual time): 각 작업자가 약 1585ms 동안 부분 합계를 계산을 했습니다.
  • 루프 수(loops): 3번 실행된 것은 3개의 병렬 작업자가 데이터를 처리했기 때문입니다.

3. Gather

Gather  (cost=133247.97..133248.18 rows=2 width=8) (actual time=1589.966..1594.595 rows=3 loops=1)
	 Workers Planned: 2
	 Workers Launched: 2

• 기능:
  • 여러 병렬 작업자(worker)가 각각 계산한 부분 결과를 모아서 전달합니다.
• 정보:
  • Workers Planned: 2개의 작업자(worker)를 계획.
  • Workers Launched: 실제로 2개의 작업자가 시작되었습니다.
  • 결과 행 수(rows): 각 작업자가 부분 합계를 1개씩 계산해서 총 3개의 행을 반환했습니다.
  • 루프 수(loops): 이 작업도 1번 실행되었습니다.

4. Finalize Aggregate

Finalize Aggregate  (cost=133248.18..133248.19 rows=1 width=8) (actual time=1590.177..1594.604 rows=1 loops=1)

• 기능:
  • 최종적으로 여러 작업자(worker)들이 계산한 부분 결과(partial result)를 모아서 전체 합계를 계산합니다.
• 정보:
  • 비용(cost): 133248.18에서 133248.19. 이 값은 PostgreSQL이 예상한 작업 비용입니다.
  • 실제 실행 시간(actual time): 작업이 시작된 시간(1590.177ms)과 끝난 시간(1594.604ms).
  • 결과 행 수(rows): 최종 결과로 1개의 행(카운트 값)이 생성되었습니다.
  • 루프 수(loops): 이 작업은 1번 실행되었습니다.

실행계획 정리

1. 병렬 처리:
   • 쿼리는 병렬 처리(Parallel Execution)를 사용해 데이터 스캔 속도를 높였습니다.
   • 2개의 작업자가 기본 키 인덱스를 사용해 데이터를 스캔했습니다.
2. 데이터 합산 과정:
   • 각 작업자가 자기 데이터를 스캔하고 부분 합계(Partial Aggregate)를 계산했습니다.
   • Gather 단계에서 작업자들이 계산한 부분 합계를 모았습니다.
   • Finalize Aggregate에서 최종 합계를 계산했습니다.
3. 실행 시간:
   • 데이터 스캔: 약 1084ms.
   • 부분 합산과 결과 처리: 약 1594ms.
   • 최종적으로 약 1594.655ms(1.59초)가 걸렸습니다.