PostgreSQL 쿼리 플랜 확인 (explain 명령어, pev 실행계획 시각화)

PostgreSQL에서 explain 명령어로 실행계획을 확인하는 방법과 오픈소스인 pev(postgres_explan_visualizer)를 이용해 실행 계획을 보기좋게 시각화 하는 방법에 대해 알아보겠습니다.

 

---

postgresql explain 공식문서

https://www.postgresql.org/docs/current/sql-explain.html

EXPLAIN

postgresql에서 실행계획을 보기위해서 explain 명령어를 사용하는데, 다음과 같이 사용합니다.

EXPLAIN [ ( option [, ...] ) ] SQL 문장

EXPLAIN [ ANALYZE ] [ VERBOSE ] SQL 문장
where option can be one of:
    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    GENERIC_PLAN [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

 

---

실행할 sql문장 위에 explain (~~) 구문만 붙여주면 되는데요.

플랜이 어떤 식으로 보이는지 한번 봐보겠습니다.

 

explain

Result  (cost=1892.09..2179.57 rows=5227 width=104)
  ->  Sort  (cost=1892.09..1905.16 rows=5227 width=44)
        Sort Key: (pg_relation_size((c.oid)::regclass, 'main'::text)) DESC
        ->  Hash Join  (cost=49.34..1569.28 rows=5227 width=44)
              Hash Cond: (c.relnamespace = n.oid)
              ->  Seq Scan on pg_class c  (cost=0.00..1435.64 rows=6983 width=72)
                    Filter: (relkind <> 'i'::"char")
              ->  Hash  (cost=40.63..40.63 rows=268 width=68)
                    ->  Seq Scan on pg_namespace n  (cost=0.00..40.63 rows=268 width=68)
                          Filter: (nspname !~ '^pg_toast'::text)

 

explain analyze 

analyze 옵션을 주면 실제 수행하여 실행계획이 나오며, 전체 수행 시간이 같이 출력됩니다.

analyze 뿐아니라 buffers, cost 등도 성능 분석하기에 좋은 지표를 보여줍니다.

Result  (cost=1892.09..2179.57 rows=5227 width=104) (actual time=32.585..175.192 rows=5284 loops=1)
  ->  Sort  (cost=1892.09..1905.16 rows=5227 width=44) (actual time=32.259..33.052 rows=5284 loops=1)
        Sort Key: (pg_relation_size((c.oid)::regclass, 'main'::text)) DESC
        Sort Method: quicksort  Memory: 571kB
        ->  Hash Join  (cost=49.34..1569.28 rows=5227 width=44) (actual time=0.426..30.923 rows=5284 loops=1)
              Hash Cond: (c.relnamespace = n.oid)
              ->  Seq Scan on pg_class c  (cost=0.00..1435.64 rows=6983 width=72) (actual time=0.012..4.206 rows=6964 loops=1)
                    Filter: (relkind <> 'i'::"char")
                    Rows Removed by Filter: 7148
              ->  Hash  (cost=40.63..40.63 rows=268 width=68) (actual time=0.390..0.392 rows=289 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 37kB
                    ->  Seq Scan on pg_namespace n  (cost=0.00..40.63 rows=268 width=68) (actual time=0.008..0.352 rows=289 loops=1)
                          Filter: (nspname !~ '^pg_toast'::text)
                          Rows Removed by Filter: 107
Planning Time: 0.354 ms
Execution Time: 175.649 ms

---

dbeaver의 실행계획 확인

 

postgersql db 접속 툴로 dbeaver를 많이 사용하는데, dbeaver에서도 실행계획 확이 기능을 지원합니다.

쿼리 선택 후 `컨트롤+쉬프트+E` 를 누르거나, 왼쪽에 플랜 확인 버튼을 누르면 explain setting 창이 뜨고 아래처럼 플랜을 확인 할 수 있습니다.

 

dbeaver postgresql explain 실행계획

 

다만.. dbeaver에서 지원하는 UI가 가시성이 너무 안좋네요.. 색깔이라도 입혀주지..😯

 

postgresql 사용자들이 개발하거나 활용하는 플랜 시각화 툴이 있을 것 같아서 찾아봤는데, 좋은 오픈소스를 하나 찾았습니다.

---

pev(postgresql_explain_visualizer) 플랜 시각화 툴

https://github.com/dalibo/pev2

GitHub - dalibo/pev2: Postgres Explain Visualizer 2

위 깃헙에서 postgresql 실행계획을 시각화 할 수 있는 오픈소스를 확인 할 수 있습니다.

 

깃헙 readme 에서는 https://explain.dalibo.com/  사이트 이용을 추천 하는데요.

별도 저장을 안한다고 해도 업무 플랜을 집어넣기는 영 찜찜하네요😅

 

다행히도 깃헙에서 로컬에서 offline으로 실행 할 수 있는 html 파일을 제공 해주니, 이를 활용해봅시다.

 

이 툴에 따르면 실행계획 추출 시 아래 옵션을 사용 하는 것을 추천합니다.

`EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)`

 

자.. 그럼 pev에서 기존 플랜들이 어떻게 나오나 한번 봅시다.

 

 

postgresql pev 화면

 

제출 결과를 보면 각 수행 단계 별로 선으로 이어서 이쁘게 표시해줍니다.

근데 쿼리가 더 복잡해지고 실행계획이 길어지면 이것 또한 한 눈에 보기 힘든데요.

위에 Grid 대시보드로 dbeaver와 비슷하긴해도 시각화를 해주어서 한눈에 확인 할 수 있습니다.

 

postgresql에서 쿼리 튜닝 할 때 pev를 꼭 활용해보세요.