AlloyDB 설명 계획 가져오기 및 분석

실행 계획을 가져와 분석하면 성능 병목 현상을 파악하고 PostgreSQL용 AlloyDB 데이터베이스 작업을 최적화할 수 있습니다. 실행 계획 또는 EXPLAIN 계획은 AlloyDB 데이터베이스 엔진이 SQL 쿼리를 실행하는 방식을 자세히 나타낸 것입니다. 실행 계획은 AlloyDB 데이터베이스에서 요청된 데이터를 검색하기 위해 실행하는 테이블 스캔, 조인, 정렬, 집계와 같은 작업의 순서를 간략하게 설명하는 노드 트리로 구성됩니다. 이 계획의 각 단계를 노드라고 합니다.

EXPLAIN 명령어를 사용하여 실행 계획을 가져옵니다. 이 명령어는 AlloyDB 쿼리 플래너가 지정된 SQL 문에 대해 생성하는 계획을 반환합니다. 쿼리 플래너(옵티마이저라고도 함)는 지정된 SQL 쿼리를 실행하는 가장 효율적인 방법을 결정합니다.

실행 계획에는 다음 구성요소가 포함됩니다.

계획 노드: 쿼리 실행의 다양한 단계를 나타냅니다(예: 스캔, 조인 또는 정렬 작업).
실행 시간: EXPLAIN 계획에는 각 단계의 예상 실행 시간이나 실제 실행 시간이 포함되어 있어 데이터베이스 병목 현상을 파악하는 데 도움이 됩니다.
버퍼 사용량: 디스크에서 읽은 데이터 양과 캐시에서 읽은 데이터 양을 보여주므로 디스크 읽기 문제를 파악하는 데 도움이 됩니다.
매개변수 설정: 계획에는 쿼리 실행 중에 적용되는 파라미터 설정이 표시됩니다.

PostgreSQL 및 확장된 AlloyDB는 다음 문의 실행 계획을 지원합니다.

SELECT
INSERT
UPDATE
DECLARE CURSOR
CREATE AS
CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
EXECUTE

시작하기 전에

AlloyDB 클러스터와 인스턴스가 있어야 합니다. 자세한 내용은 클러스터 및 클러스터의 기본 인스턴스 만들기를 참조하세요.

실행 계획 생성

psql, pgAdmin 또는 DBeaver와 같은 클라이언트 애플리케이션에서 실행 계획을 생성합니다. AlloyDB는 텍스트 또는 JSON 형식으로 실행 계획을 생성할 수 있습니다.

실행 계획을 생성하려면 다음 단계를 수행합니다.

인스턴스에 psql 클라이언트를 연결합니다.

텍스트 형식으로 실행 계획을 생성하려면 다음 명령어를 실행합니다.

SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format text)

JSON 형식으로 실행 계획을 생성하려면 다음 명령어를 실행합니다.
```
SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format json)
```
EXPLAIN 명령어에는 텍스트 또는 JSON 형식으로 지정된 쿼리에 대한 자세한 실행 계획을 생성할 수 있는 사용 가능한 모든 옵션(예: analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary)이 포함되어 있습니다. analyze 옵션은 실제 런타임 통계와 쿼리 플래너 추정치를 제공하기 위해 쿼리가 실행됨을 의미합니다.

EXPLAIN 계획 데이터 보기 및 분석

실행 계획을 가져온 후 결과를 보고 분석합니다.

기본적으로 EXPLAIN 출력에는 서버 측 쿼리 활동이 표시됩니다. 엔드 투 엔드 왕복 시간을 측정하려면 psql에서 /timing 옵션을 사용하고 결과를 /dev/null에 덤프합니다.

생성한 실행 계획을 보려면 SQL 쿼리 전에 EXPLAIN 명령어를 사용합니다.

EXPLAIN SELECT...: 쿼리를 실행하지 않고 옵티마이저에서 선택할 계획을 표시합니다.
EXPLAIN ANALYZE SELECT...: 쿼리를 실행하고 예측된 계획과 실제 실행 통계(실제 실행 시간 및 행 수 포함)를 모두 표시합니다.

ANALYZE 없는 EXPLAIN

쿼리 플래너의 예상 쿼리 비용을 표시하려면 ANALYZE 옵션을 사용하지 않고 EXPLAIN 문을 실행합니다.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27)
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(3 rows)

계획 출력에는 다음 데이터가 포함됩니다.

cost = 0.00..1735481.00: 첫 번째 숫자는 첫 번째 행을 가져오는 비용을 나타냅니다. 두 번째 숫자는 마지막 행을 가져오는 비용을 나타냅니다.
rows = 100000000: 쿼리에서 반환하는 예상 행 수입니다.
width = 27: 반환된 행의 예상 너비로, 액세스된 블록을 이해하는 데 도움이 됩니다.

ANALYZE 옵션

실제 실행 통계와 실행 예측치를 표시하려면 ANALYZE 옵션을 추가합니다.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.165..9342.424 rows=100000001 loops=1)
 Planning Time: 0.025 ms
 Execution Time: 13674.794 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(5 rows)

계획 출력에는 다음 데이터가 포함됩니다.

actual time (in ms) = 0.165..9342.424: 첫 번째 행을 반환하는 실제 시간과 모든 행을 반환하는 총 시간을 표시합니다.
rows = 100000001: 반환된 실제 행 수입니다.
loops = 1: 이 값은 중첩 루프 노드에 중요합니다. loops가 1보다 크면 루프당 평균 시간을 표시합니다.
Planning Time: 0.025ms: 플래너에서 실행 경로를 결정하는 데 걸린 시간을 나타냅니다.
Execution Time: 13674.794 ms: 플래너가 경로를 결정한 후 실행에 걸린 시간을 나타냅니다.
Total Execution Time: Planning Time 및 Execution Time의 합계입니다. (0.025 + 13674.794 = 13674.819)

VERBOSE 옵션

실행 계획에 정보를 추가하려면 VERBOSE 옵션을 사용합니다. 다음 예시에서 VERBOSE를 사용하면 테이블 이름에 스키마 자격이 추가되고 쿼리를 다른 모니터링 도구와 상호 연관시키는 데 사용할 수 있는 내부 쿼리 식별자가 표시됩니다.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.164..6568.938 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10875.894 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

계획 출력에는 다음 데이터가 포함됩니다.

Output Node: 쿼리에 포함된 열을 나열합니다. 플래너는 요청된 열보다 더 많은 열을 포함하는 경우가 있습니다. 이는 열을 남겨두는 것이 더 많은 비용이 발생한다고 판단하는 경우입니다.
Query Identifier: pg_stat_statements에 매핑되는 PostgreSQL 식별자입니다.
AlloyDB query id: 쿼리 통계 정보를 상호 연관시키는 데 사용할 수 있는 AlloyDB 쿼리 식별자입니다.

COLUMNAR ENGINE 옵션

AlloyDB 열 기반 엔진의 정보를 표시하려면 COLUMNAR_ENGINE 옵션을 추가합니다.

테이블이 열 기반 엔진에 없으면 다음 실행 계획의 열 기반 확인 열에서 상태를 확인합니다.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.009..6328.154 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10673.310 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

열 기반 엔진에 테이블이 있고 열 기반이 사용되면 열 기반 엔진 사용 방식에 대한 통계와 함께 커스텀 스캔이 표시됩니다.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test where product_id = 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Append  (cost=20.00..27438.78 rows=1166668 width=27) (actual time=0.066..377.029 rows=1000290 loops=1)
   ->  Custom Scan (columnar scan) on public.index_advisor_test  (cost=20.00..27437.66 rows=1166667 width=27) (actual time=0.065..296.904 rows=1000290 loops=1)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
         Rows Removed by Columnar Filter: 98999711
         Bytes fetched from storage cache: 774835915
         Columnar cache search mode: native
         Swap-in Time: 92.708 ms
   ->  Seq Scan on public.index_advisor_test  (cost=0.00..1.11 rows=1 width=27) (never executed)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
 Query Identifier: -4660018746142248761
 Planning Time: 0.217 ms
 Execution Time: 421.114 ms
 AlloyDB query id: 13855683355620344431
 AlloyDB plan id: 2126918133221480510

계획 출력에는 다음 데이터가 포함됩니다.

Query Filter (predicate): 필터를 사용하는 경우 적용된 필터를 표시합니다.
Rows Removed by Columnar Filter: 열 기반 필터에서 삭제한 행 수를 나타냅니다.
Bytes fetched from storage cache: 스토리지 캐시에서 가져온 바이트 수를 표시합니다.
Swap-in Time: 관계가 메모리에 맞지 않는 경우 열 기반 스필 캐시(SSD)에서 데이터를 스왑하는 데 필요한 시간입니다.

SETTINGS 옵션

플래너에서 사용하는 기본값이 아닌 세션, 데이터베이스 또는 전역 설정을 표시하려면 SETTINGS 옵션을 추가합니다.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS) select * from public.index_advisor_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.007..6366.249 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10727.068 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

BUFFERS 옵션

데이터 소스에 대한 정보를 표시하려면 BUFFERS 키워드를 사용합니다. BUFFERS 수는 계획의 특정 단계뿐만 아니라 모든 단계에서 누적됩니다.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.111..10007.193 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2588.597
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning:
   Buffers: shared hit=58 read=2, ultra fast cache hit=2
   I/O Timings: shared read=0.215
 Planning Time: 0.410 ms
 Execution Time: 14825.271 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

버퍼 정보에는 다음이 포함됩니다.

shared hit: PostgreSQL의 기본 공유 버퍼 캐시에서 발견된 8kB 데이터 블록 수입니다.
shared read: 운영체제에서 읽은 8kB 데이터 블록 수입니다. 이는 디스크 I/O를 나타내는 경우가 많습니다.
dirtied: 쿼리에서 변경한 이전에 수정되지 않은 블록의 수입니다(공개 상태 지도 변경).
written: 쿼리 처리 중에 이 백엔드에 의해 캐시에서 삭제된 이전에 더티된 블록의 수입니다. 일반적으로 힌트 비트 또는 공개 상태 지도 변경으로 인해 디스크에 플러시됩니다.
ultra fast cache hit: 초고속 캐시에서 가져온 블록 수입니다.
I/O Timings: 디스크 또는 SSD I/O의 지속 시간(밀리초)입니다.
Planning: 카탈로그 테이블에서 메타데이터난 통계 읽기와 같은 계획 단계에서의 버퍼 활동입니다.
Time spent on I/O during planning: 디스크에서 읽어야 하는 메타데이터가 있으면 I/O 시간을 표시합니다.

WAL 옵션

미리 쓰기 로깅(WAL) 활동에 대한 정보를 제공하려면 WAL 옵션을 사용합니다.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS, WAL) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.010..10147.314 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2590.410
   WAL: records=18 bytes=5178
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning Time: 0.030 ms
 Execution Time: 15033.004 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

WAL 정보에는 다음이 포함됩니다.

WAL records: 일관성을 유지하기 위해 읽은 WAL 레코드 수입니다.
WAL bytes: 일관성을 유지하기 위해 WAL에서 읽은 바이트 수입니다.
Records that have been modified but not yet checkpointed: 수정되었지만 아직 체크포인트되지 않은 레코드를 나타냅니다.

다음 단계

실행 계획 노드 유형 알아보기

AlloyDB 설명 계획 가져오기 및 분석 컬렉션을 사용해 정리하기 내 환경설정을 기준으로 콘텐츠를 저장하고 분류하세요.

시작하기 전에

실행 계획 생성

EXPLAIN 계획 데이터 보기 및 분석

ANALYZE 없는 EXPLAIN

ANALYZE 옵션

VERBOSE 옵션

COLUMNAR ENGINE 옵션

SETTINGS 옵션

BUFFERS 옵션

WAL 옵션

다음 단계

AlloyDB 설명 계획 가져오기 및 분석