문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣOpenSQL 3
문서번호ㅣOTUTI001
본 문서는 postgresql을 기반으로한 opensql 기술 문서입니다.
개요
EXPLAIN 섹션은 EXPLAIN 기능에 대한 개요, 파라미터, 사용 방법에 대한 내용을 다루고 있습니다.
PREPARE 및 auto_explain 섹션에서는 각각 Prepare와 auto_explain 기능에 대한 요약, 파라미터, 사용 방법을 설명하고 있습니다.
본 문서에서는 이를 테스트하여 준비된 쿼리를 실행하고 로그를 통해 쿼리 실행 계획 변화 여부를 확인합니다.
방법
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 옵션을 사용할 때 명령문이 실제로 실행됩니다. EXPLAIN은 SELECT와 다른 출력을 반환하지 않지만, 명령문에는 데이터 변경과 같은 다른 부작용이 발생할 수 있습니다.
만약 데이터에 영향을 미치지 않으면서 INSERT, UPDATE, DELETE, CREATE TABLE AS, 또는 EXECUTE와 같은 명령문에 EXPLAIN ANALYZE를 사용한다면 다음과 같은 접근 방식을 사용할 수 있습니다:
트랜잭션 블록: 명령문을 BEGIN과 ROLLBACK으로 둘러싸서 트랜잭션 블록 내에서 실행합니다. 이렇게 하면 명령문을 실행하고 나중에 트랜잭션을 롤백하여 변경 사항을 취소할 수 있습니다.
예시
BEGIN; EXPLAIN ANALYZE INSERT INTO 테이블명 VALUES (...); ROLLBACK;
INSERT INTO 테이블명 VALUES (...); 부분을 실제 명령문으로 대체하면 됩니다.이러한 방법을 사용하면 명령문을 제어된 환경에서 실행하고 실제 데이터에 변경을 방지할 수 있습니다. 특히 실제 데이터베이스 내용을 변경하지 않고도 쓰기 작업의 성능을 분석하고자 할 때 유용합니다.
pgAdmin을 통한 Visualizing 방법
쿼리를 실행할 Database에서 Query Tool을 선택합니다.
버튼 E를 클릭하면 해당 쿼리에 대한 Explain이 실행됩니다. 또한 다양한 파라미터를 이용할 수 있습니다.
파라미터
ANALYZE 명령을 실행하고 실제 실행 시간 및 기타 통계를 표시합니다.
이 파라미터는 기본적으로 FALSE로 설정됩니다.
postgres=# explain analyze SELECT p.project_id, p.project_name, p.deptno AS project_deptno, d.dname AS dept_name,
e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
FROM project p
JOIN department d ON p.deptno = d.deptno
JOIN employee e ON p.deptno = e.deptno
ORDER BY p.project_id ASC;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Sort (cost=107.64..109.30 rows=667 width=226) (actual time=0.178..0.184
rows=33 loops=1)
-[ RECORD 2 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Sort Key: p.project_id
-[ RECORD 3 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Sort Method: quicksort Memory: 29kB
-[ RECORD 4 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Hash Join (cost=48.68..76.35 rows=667 width=226)
(actual time=0.139..0.156 rows=33 loops=1)
-[ RECORD 5 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Hash Cond: (p.deptno = d.deptno)
-[ RECORD 6 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Seq Scan on project p (cost=0.00..18.00 rows=800 width=66)
(actual time=0.080..0.082 rows=10 loops=1)
-[ RECORD 7 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Hash (cost=41.80..41.80 rows=550 width=168)
(actual time=0.048..0.049 rows=13 loops=1)
-[ RECORD 8 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Buckets: 1024 Batches: 1 Memory Usage: 10kB
-[ RECORD 9 ]--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Hash Join (cost=24.85..41.80 rows=550 width=168)
(actual time=0.030..0.040 rows=13 loops=1)
-[ RECORD 10 ]-------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Hash Cond: (e.deptno = d.deptno)
-[ RECORD 11 ]-------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Seq Scan on employee e (cost=0.00..15.50 rows=550
width=118)
(actual time=0.007..0.009 rows=13 loops=1)
-[ RECORD 12 ]-------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Hash (cost=16.60..16.60 rows=660 width=50)
(actual time=0.014..0.015 rows=4 loops=1)
-[ RECORD 13 ]-------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Buckets: 1024 Batches: 1 Memory Usage: 9kB
-[ RECORD 14 ]-------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Seq Scan on department d (cost=0.00..16.60
rows=660 width=50)
(actual time=0.007..0.009 rows=4 loops=1)
-[ RECORD 15 ]-------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Planning Time: 0.285 ms
-[ RECORD 16 ]-------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Execution Time: 0.236 ms쿼리 플랜을 읽는 방법은 아래와 같습니다.
[RECORD 14] → [RECORD 12,13] → [RECORD 11] → [RECORD 10, 09] → [RECORD 08, 07] → [RECORD 06] → [RECORD 05, 04] → [RECORD 03, 02, 01]
위는 pgAdmin을 이용해서 실행 계획을 Visualizing 한 것 입니다.
따라서 Visualizing을 통해서 쿼리 플랜을 한눈에 볼 수 있습니다.
[RECORD 14]
-> Seq Scan on department d (cost=0.00..16.60 rows=660 width=50) (actual time=0.007..0.009 rows=4 loops=1)
departmentcost의 첫번째 숫자 0.007는 첫번째 레코드를 검색하는cost이고 두번째 0.009는 처음부터 끝까지 처리하는데 드는 total cost입니다.rows는 660개의 행이 반환,width는 node에서 출력한rows의 예상 byte이다. 따라서 각rows는 50byte 입니다.actual time의 첫 번째 숫자 0.007은 시작 시간(첫 번째 레코드를 검색하는 시간)이고 두 번째 숫자 0.009 전체 node를 처리하는데 걸리는 시간으로 둘 다 밀리세컨드 단위입니다.rows는 4개의 행이 반환,loops는 실행 계획에서 반복된 횟수를 나타냅니다. 해당 단계가 한번만 실행된 것입니다.costvsactual timecost는 옵티마이저가 실행 계획을 사용되는 예상 비용을 나타내는 반면 actual time은 해당 실행 단계를 실제로 실행하여 얻은 시간을 보여줍니다.
[RECORD 12, 13]
-> Hash (cost=16.60..16.60 rows=660 width=50) (actual time=0.014..0.015 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB
- 스캔한 department 테이블에 대해서 상위 hash join에서 사용할 쿼리 행을 hash 합니다.
- Buckets: 해시 조인이나 그룹화를 위해 사용되는 버킷(bucket)의 수입니다. 이 값은 해시 테이블의 크기나 조인을 위한 내부 해시 매핑에 관련됩니다.해시 작업에서 1024개 버킷을 사용합니다. 따라서 데이터를 해시화 하여 1024의 버킷을 사용한다는 것을 나타냅니다.
- Batches: 작업이 분할된 배치(batch)의 수입니다. 대규모 작업을 처리할 때 데이터를 일부 그룹으로 분할하여 처리하는 것이 일반적입니다.작업을 작은 그룹으로 나누어 처리하는 걸 의미합니다. 이 플랜에서는 한번의 배치로 처리함을 알 수 있습니다.
- Memory Usage: 이 작업에서 사용된 메모리 양입니다. 여기서는 9KB의 메모리가 사용되었습니다.
[RECORD 11]
-> Seq Scan on employee e (cost=0.00..15.50 rows=550 width=118) (actual time=0.007..0.009 rows=13 loops=1)
- employee 테이블은 seq scan이 수행되었습니다.
[RECORD 9,10]
-> Hash Join (cost=24.85..41.80 rows=550 width=168) (actual time=0.030..0.040 rows=13 loops=1) Hash Cond: (e.deptno = d.deptno)
- employee 테이블과 department 테이블을 Hash Inner Join 합니다.
[RECORD 7,8]
-> Hash (cost=41.80..41.80 rows=550 width=168) (actual time=0.048..0.049 rows=13 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB
- [RECORD 10]에서 Hash Inner Join 한 테이블을 Hash합니다.
[RECORD 6]
-> Seq Scan on project p (cost=0.00..18.00 rows=800 width=66) (actual time=0.080..0.082 rows=10 loops=1)
- project 테이블은 seq scan이 수행되었습니다.
[RECORD 4,5]
-> Hash Join (cost=48.68..76.35 rows=667 width=226) (actual time=0.139..0.156 rows=33 loops=1) Hash Cond: (p.deptno = d.deptno)
- [RECORD 7,8] 에서 해시한 테이블과 project테이블을 Hash Inner Join 합니다.
[RECORD 1,2,3]
Sort (cost=107.64..109.30 rows=667 width=226) (actual time=0.178..0.184 rows=33 loops=1) Sort Key: p.project_id Sort Method: quicksort Memory: 29kB
- Sort Key: 정렬의 기준이 되는 열(column)입니다. 여기서는 "p.project_id" 열을 기준으로 정렬되었습니다.
- Sort Method: 사용된 정렬 방법입니다. 여기서는 "quicksort"가 사용되었으며, 이는 빠른 정렬 알고리즘 중 하나입니다. 또한 Memory: 29kB는 정렬 작업을 위해 사용된 메모리 양을 나타냅니다
명령 계획에 대한 추가 정보를 표시합니다. 출력 열 목록, 테이블 및 함수 이름에 대한 스키마 명시, 테이블 별칭, 통계가 표시되는 각 트리거의 이름을 항상 출력합니다. 이 파라미터는 기본적으로 FALSE로 설정됩니다.
postgres=# explain verbose SELECT p.project_id, p.project_name, p.deptno AS project_deptno, d.dname AS dept_name, e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm FROM project p JOIN department d ON p.deptno = d.deptno JOIN employee e ON p.deptno = e.deptno ORDER BY p.project_id ASC; -[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Sort (cost=177.72..181.10 rows=1352 width=147) -[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Output: p.project_id, p.project_name, p.deptno, d.dname, e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm -[ RECORD 3 ]-------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Sort Key: p.project_id -[ RECORD 4 ]-------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash Join (cost=71.89..107.41 rows=1352 width=147) -[ RECORD 5 ]-------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Output: p.project_id, p.project_name, p.deptno, d.dname, e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm -[ RECORD 6 ]-------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Hash Cond: (p.deptno = d.deptno) -[ RECORD 7 ]-------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Seq Scan on public.project p (cost=0.00..18.00 rows=800 width=66) -[ RECORD 8 ]-------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Output: p.project_id, p.project_name, p.deptno, p.start_date, p.end_date -[ RECORD 9 ]-------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash (cost=57.95..57.95 rows=1115 width=89) -[ RECORD 10 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Output: d.dname, d.deptno, e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno -[ RECORD 11 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash Join (cost=24.85..57.95 rows=1115 width=89) -[ RECORD 12 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Output: d.dname, d.deptno, e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno -[ RECORD 13 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Inner Unique: true -[ RECORD 14 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Hash Cond: (e.deptno = d.deptno) -[ RECORD 15 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Seq Scan on public.employee e (cost=0.00..30.15 rows=1115 width=39) -[ RECORD 16 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Output: e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno -[ RECORD 17 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash (cost=16.60..16.60 rows=660 width=50) -[ RECORD 18 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Output: d.dname, d.deptno -[ RECORD 19 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Seq Scan on public.department d (cost=0.00..16.60 rows=660 width=50) -[ RECORD 20 ]------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Output: d.dname, d.deptno
위 쿼리에 대한 설명은 Analyze을 참고 부탁 드립니다.
Analyze와 다른 점은 Output을 통해서 각 단계에서 반환하는 컬럼을 나타냅니다. 이 컬럼은 실행 결과에 대한 선택된 열을 의미합니다.
각 계획 노드의 예상 시작 및 총 비용 정보를 포함합니다. 또한 예상되는 결과 row(반환 되는 행), width(각 행의 byte)도 포함됩니다. 이 파라미터는 기본적으로 TRUE로 설정됩니다.
구성 매개변수에 대한 정보를 포함합니다. 구체적으로는 쿼리 계획에 영향을 주는 내장 기본 값과 다른 값을 가진 옵션을 포함합니다. 이 파라미터는 기본적으로 FALSE로 설정됩니다.
버퍼 사용에 대한 정보를 포함합니다. 구체적으로는 공유된 블록의 히트, 읽기, 변경, 쓰기 수, 로컬 블록의 히트, 읽기, 변경, 쓰기 수, temp 블록의 읽기 및 쓰기 수 등을 포함합니다.
temp 블록은 정렬, 해시, Materialize 계획 노드 및 유사한 경우에 사용되는 단기간의 작업 데이터를 포함합니다. 수정된 블록 수는 이 쿼리에 의해 변경된 이전에 수정되지 않은 블록 수를 나타냅니다.
상위 수준 노드에 표시된 블록 수에는 해당 노드의 모든 하위 노드에서 사용된 블록이 포함됩니다. 텍스트 형식에서는 0이 아닌 값만 인쇄됩니다. 기본 값은 FALSE입니다.
postgres=# explain (analyze, buffers) SELECT p.project_id, p.project_name, p.deptno AS project_deptno, d.dname AS dept_name, e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm FROM project p JOIN department d ON p.deptno = d.deptno JOIN employee e ON p.deptno = e.deptno ORDER BY p.project_id ASC; -[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Sort (cost=107.64..109.30 rows=667 width=226) (actual time=0.143..0.149 rows=33 loops=1) -[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Sort Key: p.project_id -[ RECORD 3 ]-------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Sort Method: quicksort Memory: 29kB -[ RECORD 4 ]-------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buffers: shared hit=3 read=3 -[ RECORD 5 ]-------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash Join (cost=48.68..76.35 rows=667 width=226) (actual time=0.084..0.096 rows=33 loops=1) -[ RECORD 6 ]-------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Hash Cond: (p.deptno = d.deptno) -[ RECORD 7 ]-------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buffers: shared read=3 -[ RECORD 8 ]-------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Seq Scan on project p (cost=0.00..18.00 rows=800 width=66) (actual time=0.014..0.015 rows=10 loops=1) -[ RECORD 9 ]-------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buffers: shared read=1 -[ RECORD 10 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash (cost=41.80..41.80 rows=550 width=168) (actual time=0.054..0.056 rows=13 loops=1) -[ RECORD 11 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buckets: 1024 Batches: 1 Memory Usage: 10kB -[ RECORD 12 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buffers: shared read=2 -[ RECORD 13 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash Join (cost=24.85..41.80 rows=550 width=168) (actual time=0.040..0.047 rows=13 loops=1) -[ RECORD 14 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Hash Cond: (e.deptno = d.deptno) -[ RECORD 15 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buffers: shared read=2 -[ RECORD 16 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Seq Scan on employee e (cost=0.00..15.50 rows=550 width=118) (actual time=0.008..0.009 rows=13 loops=1) -[ RECORD 17 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buffers: shared read=1 -[ RECORD 18 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash (cost=16.60..16.60 rows=660 width=50) (actual time=0.019..0.019 rows=4 loops=1) -[ RECORD 19 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buckets: 1024 Batches: 1 Memory Usage: 9kB -[ RECORD 20 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buffers: shared read=1 -[ RECORD 21 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Seq Scan on department d (cost=0.00..16.60 rows=660 width=50) (actual time=0.012..0.013 rows=4 loops=1) -[ RECORD 22 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buffers: shared read=1 -[ RECORD 23 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Planning: -[ RECORD 24 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Buffers: shared hit=155 read=8 -[ RECORD 25 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Planning Time: 0.827 ms -[ RECORD 26 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Execution Time: 0.247 ms
위 쿼리에 대한 설명은 Analyze를 참고 부탁 드립니다.
- Buffers: 각 단계에서의 버퍼 사용 정보를 나타냅니다.
- shared hit: 쿼리 실행 중 공유 버퍼 캐시에서 읽힌 횟수를 의미합니다. 쿼리 실행 중에 버퍼 캐시에서 직접 읽힌 횟수입니다.
- shared read: 디스크에서 데이터를 읽는 횟수를 나타냅니다. 이 값이 높을수록 디스크 I/O가 많이 발생한 것을 의미합니다.
WAL 레코드 생성에 대한 정보를 포함합니다. ANALYZE이 매개변수는 활성화된 경우에만 사용할 수 있습니다. 기본 값은 FALSE 입니다..
시작 시간과 각 노드에서 소요된 시간을 결과에 포함합니다.
일부 시스템에서 반복적으로 사용 되기 떄문에 쿼리를 크게 느리게 만들 수 있으므로, 정확한 시간이 아닌 실제 행 수만 필요한 경우 이 매개변수를 FALSE로 설정하는 것이 유용할 수 있습니다.
노드 수준의 타이밍이 이 옵션으로 비활성화 되더라도 전체 문장의 실행 시간은 항상 측정됩니다.
이 매개변수는 반드시 ANALYZE가 활성화된 상태에서만 사용할 수 있습니다. 기본 값은 TRUE입니다.
쿼리 계획 뒤에 요약 정보(예: 총 타이밍 정보)를 포함합니다. 기본적으로 ANALYZE 사용 될 때 포함됩니다. ANALYZE를 사용하지 않는 경우에도 활성화할 수 있습니다.
TEXT, XML, JSON 또는 YAML 등 출력 형식을 지정합니다. 텍스트가 아닌 출력에는 텍스트 출력 형식과 동일한 정보가 포함되지만 프로그램에서 구문 분석하기가 더 용이합니다. 이 매개변수의 기본 값은 TEXT 입니다 .
각 옵션의 값으로 불린형을 사용하는데, TRUE, ON, 1 은 그 옵션의 기능을 사용하는 것이며, FALSE, OFF, 0 은 그 옵션의 기능을 사용하지 않는 것이다. 또한 옵션을 지정하면서 그냥 옵션 이름만 지정할 수도 있는데, 이 때는 그 옵션 값으로 TRUE를 지정한 것으로 간주한다.
postgres=# explain (analyze, summary false) select * from foo;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..145.00 rows=10000 width=4)
(actual time=0.127..1.893 rows=10000 loops=1)
(1개 행)
실행 계획을 살펴볼 SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, 또는 CREATE TABLE AS 구문을 지정합니다.
Prepare
prepare은 준비된 명령문을 생성합니다. prepare 문이 실행되면 지정된 statement가 분석되고 재작성됩니다. 이후에 EXECUTE 명령이 실행되면 준비된 명령어를 실행합니다.
기능 개요
PREPARE name [ ( data_type [, ...] ) ] AS statement
파라미터
name
해당 prepare statement에 이름입니다.
생성된 prepare statement에 대한 매개변수의 데이터 유형입니다. 특정 매개변수의 데이터 유형이 지정되지 않거나 unknown로 지정된 경우 매개변수가 처음 참조되는 context에서 추론됩니다. 매개변수를 참조하려면 $1, $2 등을 사용하세요.
SELECT, INSERT, UPDATE, DELETE, or VALUES를 의미합니다.
예시
postgres=# prepare query2(VARCHAR) as select * from department
where loc = $1;
PREPARE
postgres=# execute query2('BOSTON');
deptno | dname | loc
--------+----------------+--------
40 | OPERATIONS | BOSTON
44 | Department_3 | BOSTON
45 | Department_4 | BOSTON
46 | Department_5 | BOSTON
....이 쿼리는 PREPARE 문으로 loc 컬럼을 기반으로 쿼리를 준비합니다. 이 쿼리에서는 department 테이블에서 loc 컬럼의 값을 바인드 변수로 받아 해당 위치에 대한 부서 정보를 가져옵니다.
여기서는 BOSTON 위치에 대한 부서 정보를 조회하고 있습니다. execute query2('BOSTON') 명령어를 통해 준비된 쿼리를 실행하며, 해당 위치에 속하는 부서들의 정보를 반환합니다.
auto_explain
auto_explain을 활성화하면 쿼리 실행 시 실행 계획이 자동으로 기록됩니다. 즉, 쿼리가 실행될 때 실행 계획이 자동으로 저장되어 분석 및 디버깅을 할 수 있습니다.
파라미터
auto_explain.log_min_duration (integer)
플랜이 기록되는 최소 명령문 실행 시간(ms)입니다.
따라서 auto_explain의 다른 파라미터를 사용하기 위해서는 반드시 설정해야 합니다.
0으로 설정하면 모든 계획이 기록됩니다. -1으로 설정 할 시 로그 작성을 비활성화 합니다.
이 파라미터를 이용해서 느린 쿼리를 확인 할 수 있습니다.
예를 들어 250ms으로 설정하면, 250ms이상 실행되는 모든 statement가 로그로 작성됩니다.
auto_explain.log_analyze (boolean)
EXPLAIN ANALYZE 에 대한 플랜을 로그에 작성합니다.
auto_explain.log_buffers (boolean)
실행 계획이 기록될 때 버퍼 사용 통계를 인쇄할지 여부를 제어합니다. auto_explain.log_analyze를 활성화 해야 플랜이 로그에 작성됩니다.
auto_explain.log_wal (boolean)
실행 계획이 기록될 때 WAL 사용 통계를 인쇄할지 여부를 제어합니다. auto_explain.log_analyze를 활성화 해야 플랜이 로그에 작성됩니다.
auto_explain.log_timing (boolean)
실행 계획이 기록될 때 노드별 타이밍 정보를 인쇄할지 여부를 제어합니다. auto_explain.log_analyze를 활성화 해야 플랜이 로그에 작성됩니다.
auto_explain.log_triggers (boolean)
실행 계획이 기록될 때 트리거 실행 통계가 포함되도록 합니다. auto_explain.log_analyze를 활성화 해야 플랜이 로그에 작성됩니다.
auto_explain.log_verbose (boolean)
실행 계획이 기록될 때 자세한 세부 정보를 인쇄할지 여부를 제어합니다. auto_explain.log_analyze를 활성화 해야 플랜이 로그에 작성됩니다.
auto_explain.log_settings (boolean)
실행 계획이 기록될 때 수정된 구성 옵션에 대한 정보가 인쇄되는지 여부를 제어합니다. auto_explain.log_analyze를 활성화 해야 플랜이 로그에 작성됩니다.
auto_explain.log_format (enum)
사용할 출력 형식을 EXPLAIN 선택합니다. 허용되는 값은 text, xml, json 및 yaml 입니다.
auto_explain.log_level (enum)
쿼리 플랜을 기록할 로그 방식을 선택합니다. 유효한 값으로는 debug5, debug4, debug3, debug2, debug1, info, notice, warning, log 입니다.
auto_explain.log_nested_statements (boolean)
중첩된 문(함수 내에서 실행되는 문)이 로깅 대상으로 간주됩니다. 꺼져 있으면 최상위 쿼리 계획만 기록됩니다.
auto_explain.sample_rate (real)
각 세션의 명령문 중 일부만 설명하도록 합니다. 기본값은 1이며, 이는 모든 쿼리를 설명한다는 의미입니다.
중첩된 문의 경우 모두 설명되거나 설명되지 않습니다.
auto_explain example
shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '0' auto_explain.log_analyze = true;
postgresql.conf 파일에서 auto_explain을 활성화 해야 합니다.
또한 auto_explain.log_min_duration을 이용해서 쿼리 실행 시간이 0 밀리세컨드 이상인 경우에도 실행 계획을 로그에 남기도록 설정합니다. 따라서 모든 쿼리의 실행 계획을 로그에 작성하도록 합니다.
auto_explain.log_analyze를 true로 설정하여 explain analyze가 로그에 기록되게 합니다.
postgres=# LOAD 'auto_explain'; LOAD postgres=# set auto_explain.log_min_duration = 0; SET postgres=# set auto_explain.log_analyze = true; SET
postgresql.conf 파일에서 auto_explain을 설정 할 수 있지만 슈퍼 유저는 즉시 매개변수를 변경 할 수 있습니다.postgres=# EXPLAIN ANALYZE SELECT * FROM project JOIN employee ON project.deptno = employee.deptno JOIN department ON project.deptno = department.deptno;
auto_explain을 이용해서 로그로 쿼리 플랜을 확인 할 수 있습니다.
Prepare과 Auto_explain을 이용한 테스트
테스트 개요
prepare을 이용해서 쿼리문을 준비하고, execute을 이용하여 쿼리에 대한 실행 계획은 확인합니다.
auto_explain을 활용하여 동일한 쿼리를 여러 번 실행하고, 로그를 분석하여 실행 계획이 일정하게 유지 여부 및 바인드 변수의 값을 변경하여 쿼리를 실행하고, 실행 계획이 변경되는지 확인합니다.
테스트 시나리오 및 결과
테스트 전 postgresql.conf 파일 수정
shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = 0 auto_explain.log_analyze = true
postgresql.conf 파일에서 auto_explain을 활성화 합니다.
또한 auto_explain.log_min_duration을 이용해서 쿼리 실행 시간이 0 밀리초 이상인 경우에도 실행 계획을 로그에 남기도록 설정합니다. 따라서 모든 쿼리의 실행 계획을 로그에 작성하도록 합니다.
auto_explain.log_analyze true로 설정하여 explain analyze가 로그에 기록되게 합니다.
prepare으로 쿼리문 생성, execute을 이용하여 쿼리 실행
postgres=# prepare query(int, int) AS SELECT * FROM project JOIN department ON project.deptno = department.deptno WHERE department.deptno > $1 and department.deptno < $2; PREPARE
prepare을 이용해서 쿼리문을 준비합니다.
postgres=# explain analyze execute query(40,100);
QUERY PLAN
----------------------------------------------------------------------------------------
Hash Join (cost=10.03..30.80 rows=54 width=49)
Hash Cond: (project.deptno = department.deptno)
-> Seq Scan on project (cost=0.00..18.11 rows=1011 width=24)
-> Hash (cost=9.35..9.35 rows=54 width=25)
-> Index Scan using pk_dept on department
(cost=0.28..9.35 rows=54 width=25)
Index Cond: ((deptno > 40) AND (deptno < 100))
(6개 행)준비된 쿼리에 값을 넣어 쿼리 플랜을 확인합니다.
auto_explain을 통해 해당 쿼리 플랜을 로그에서 확인합니다.
project 테이블을 전체 스캔하고, department 테이블을 인덱스를 이용하여 필요한 조건에 맞게 액세스합니다. 그리고 이 두 테이블 간에 Hash Join을 수행하여 결과를 반환합니다.
auto_explain을 활용하여 동일한 쿼리를 여러 번 실행하고, 로그를 분석postgres=# execute query(40,100); postgres=# execute query(40,100); postgres=# execute query(40,100); postgres=# execute query(40,100); postgres=# execute query(40,100);
execute를 이용하여 query를 5번 실행했습니다.- 1번 실행 : 쿼리 실행 시간 1.710ms
- 2번 실행 : 쿼리 실행 시간 1.442ms
- 3번 실행 : 쿼리 실행 시간 1.405ms
- 4번 실행 : 쿼리 실행 시간 0.802ms
- 5번 실행 : 쿼리 실행 시간 1.377ms
쿼리를 5번 실행 결과, 동일한 쿼리에 대해 일관된 실행 계획을 관찰할 수 있고 이를 통해 해당 쿼리의 실행 계획이 일정하게 유지됨을 확인할 수 있었습니다.
auto_explain을 통해 로그를 확인하여 실행 계획과 실행 시간을 추적함으로써 최적화 유지여부를 확인할 수 있습니다.
바인드 변수의 값을 변경하여 쿼리를 실행하고, 실행 계획이 확인
postgres=# execute query(100,1000);
PREPARE로 준비한 쿼리에 바인드 변수로 이전 값보다 큰 값을 대입하였습니다.
쿼리 실행 시간은 2.737ms로 증가했지만 PLAN은 똑같이 유지됨을 볼 수 있습니다.
auto_explain을 이용하여 바인드 변수 값을 변경하면서 한 쿼리에 대해서 PLAN이 일정하게 유지 여부를 확인해 볼 수 있습니다.
결론
EXPLAIN은 개별 쿼리의 실행 계획을 분석하는 데 사용되고, PREPARE를 이용하여 쿼리를 준비하면 실행 계획을 확인하면서 쿼리 재 사용성을 높일 수 있으며, auto_explain은 자동으로 모든 쿼리의 실행 계획을 로그에 작성합니다.
이 테스트를 통해 PREPARE을 사용하여 쿼리를 준비하고 auto_explain을 통해 쿼리 실행 계획과 실행 시간을 확인했습니다. 준비된 쿼리에 대한 실행 계획이 일관되게 유지되는지, 바인드 변수 값을 변경해도 실행 계획이 동일하게 유지되는지 확인할 수 있습니다. 이를 통해 쿼리 최적화를 지속적으로 추적할 수 있습니다.
본 문서는 postgresql을 기반으로한 opensql 기술 문서입니다.