PostgreSQL의 실행계획 옵션

 

 
본 문서의 내용은 PostgreSQL 버젼에 따라 다를 수 있습니다. 본 문서에서 사용한 버젼은 아래와 같습니다.

• AWS RDS PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit

PostgreSQL의 EXPLAIN OPTION

PostgreSQL은 MySQL에 비해 다양하고 많은 정보를 실행계획에 표현해줍니다.
PostgreSQL의 EXPLAIN 명령어 옵션들에 대해 간단히 살펴보겠습니다.

• ANALYZE: 실제 쿼리를 수행하고 사용한 실행계획을 보여준다.
  • ANALYZE는 실제 SQL이 실행되므로, INESRT, UPDATE, DELETE에서는 사용해서는 안된다.
• VERBOSE: 쿼리 계획에 추가적인 정보를 포함, 출력 컬럼과 각 노드의 OID
  • OID: Object Identifier, PG에서 객체를 구분하는 ID
  • SELECT * FROM pg_class WHERE oid = '테이블_OID';
• BUFFERS: 쿼리 실행 중에 사용된 공유 버퍼, 로컬 버퍼, 그리고 쓰기 버퍼에 대한 정보
  • ANALYZE와 함께 사용해야 의미가 있다.
• COSTS: 각 계획 단계의 추정 비용. 기본적으로 활성화되어 있음
  • COSTS OFF 로 예상 비용 표시 제외 가능
• TIMING: 각 계획 단계의 시간 표시
  •  ANALYZE 사용시 자동 활성화
• FORMAT: 결과를 다른 형식으로 반환. 
  • TEXT, XML, JSON, YAML입니다.

 
SQL 튜닝을 위해 가장 활발히 사용할 부분은 ANALYZE와 BUFFERS입니다.
COSTS의 경우는 OFF도 가능합니다.
저는 교육 자료를 만들때 주로 아래와 같은 옵션으로 SQL의 실행계획을 추출합니다. (실제 튜닝 중이라면, 통계 구성을 예상할 수 있는 COSTS도 같이 보는게 좋겠죠.)

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ NestLoop(t1 t2) */
        t1.* ,t2.ord_amt
        ,(SELECT x.rgn_nm FROM m_rgn x WHERE x.rgn_id = t1.rgn_id) rgn_nm
FROM    m_cus t1 LEFT OUTER JOIN (
           SELECT  a.cus_id, sum(a.ord_amt) ord_amt
           FROM    t_ord a
           GROUP BY a.cus_id) t2 on (t2.cus_id = t1.cus_id)
WHERE   t1.rgn_id = 'A';

 
위와 같이 EXPLAIN에 ANALYZE와 BUFFERS 그리고 COSTS OFF를 하면 아래와 같은 실행계획을 확인할 수 있습니다.

Nested Loop Left Join (actual time=1.167..1.440 rows=30 loops=1)
  Join Filter: ((t2.cus_id)::text = (t1.cus_id)::text)
  Rows Removed by Join Filter: 1354
  Buffers: shared hit=122
  ->  Seq Scan on m_cus t1 (actual time=0.012..0.024 rows=30 loops=1)
        Filter: ((rgn_id)::text = 'A'::text)
        Rows Removed by Filter: 60
        Buffers: shared hit=2
  ->  Materialize (actual time=0.037..0.041 rows=46 loops=30)
        Buffers: shared hit=60
        ->  Subquery Scan on t2 (actual time=1.106..1.136 rows=89 loops=1)
              Buffers: shared hit=60
              ->  HashAggregate (actual time=1.106..1.127 rows=89 loops=1)
                    Group Key: a.cus_id
                    Batches: 1  Memory Usage: 80kB
                    Buffers: shared hit=60
                    ->  Seq Scan on t_ord a (actual time=0.002..0.229 rows=3047 loops=1)
                          Buffers: shared hit=60
  SubPlan 1
    ->  Index Scan using m_rgn_pk on m_rgn x (actual time=0.001..0.001 rows=1 loops=30)
          Index Cond: ((rgn_id)::text = (t1.rgn_id)::text)
          Buffers: shared hit=60
Planning Time: 0.193 ms
Execution Time: 1.483 ms

 
위 실행계획에서 Buffers 부분이 IO에 대한 항목입니다. shared hit는 모두 메모리에서 처리된 Logical IO입니다.
디스크에서 처리된 Physical IO가 발생하면, Buffers 항목에 read로 별도 표시를 해줍니다.
 
이와 같은 트리형태의 실행계획과 SQL이 실제 실행된 실행계획, 그리고 각 단계별 rows, loops의 수치와 IO 수치 제공은 SQL 튜닝을 더욱 효율적으로 할 수 있도록 해줍니다.
PostgreSQL의 SQL 성능때문에 고민중이라면 이와 같은 실행계획에 익숙해주시기를 권장합니다.
 

 
 

 
이상입니다.
 
P.S. 아래 강의들을 진행하고 있으니, 많은 관심 부탁드립니다.
- StartUP Tuning For PostgreSQL: PostgreSQL을 활용한 SQL 튜닝 입문 교육
  https://cafe.naver.com/dbian/7181
- StartUP Tuning For MySQL: MySQL을 활용한 SQL 튜닝 입문 교육
  https://cafe.naver.com/dbian/6958
- 평생필요한 데이터 분석: 저자 직강!, 주식 데이터를 활용한 SQL 입문자 교육
  https://cafe.naver.com/dbian/7131