설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 75건, 최근 0 건
   

multi subquery 처리 순서 제어 튜닝

글쓴이 : 모델광 날짜 : 2021-04-26 (월) 23:57 조회 : 116

아래 패턴의 쿼리 개선 방법을 기술한다.

아래에서 A, B, C 는 하나의 테이블이 아니라 여러 테입블일 수도 있다.


SELECT count(*)

   FROM A

 WHERE ...

     AND EXISTS (SELECT 1

                       FROM B

                      WHERE ...              )

     AND EXISTS (SELECT 1

                        FROM C

                      WHERE ............       );

위와 같은 패턴은 옵티마이저가 A, B, C 집합을 JOIN 으로 transformation해야 최적의 실행계획이 나온다.

하지만 B와 C 집합이 매우 복잡한 쿼리로 되어 있는 경우 subquery collapse가 발생 못해서,

B와 C 집합이 filter 조건으로 처리되는 경우가 있다.

B와 C 집합이 filter 조건으로 처리되는 경우 성능을 향상시키기 위해서는 많은 row를 filtering 할 수 있는 집합이 먼저 filter 조건으로 사용되어야 한다.

오라클은 위와 같은 상황에서 SQL 문장에 기술한 집합 순으로 처리한다.

즉 위의 쿼리는 B 집합을 먼저 filter 처리하고 C 집합을 나중에 filter 처리한다.

따라서 옵티마이저가 잘못된 처리 순서로 수행하는 경우 SQL만 다시 작성(subquery 위치 변경)해서 성능을 향상시킬 수 있다.

하지만 PostgreSQL은 SQL을 다시 작성해도 옵티마아저가 filter 순서를 변경하지 않는다.

(아래 테스트 내용 참조)

PostgreSQL은 이런 경우 옵티마이저 판단에 영향을 줄 수 있는 hint 가 없다.

하지만 아래와 같이 with 절을 사용해서 filter 순서를 조정할 수 있다.


아래는 위 내용을 증명하는 스크립트 이다.

--테스트 테이블 생성 및 데이터 입력

create table 고객 (

cust_id int,

cust_name varchar(100),

comment char(100));

insert into 고객 

select i, 'TESTNAME'||i, 'dummyy' from generate_series(1,1000) a(i);

select * from 고객;

create table 과태료

(id int, cust_id int, amount bigint);

insert into 과태료

select i, case when i < 10 then i else i+1000 end, i*10

  from generate_series(1,10000) a(i);

  select * from 과태료; 


create table 신고신청(

singo_id int, cust_id int, reg_date date, comment char(200));

insert into 신고신청

select i, mod(i,1000), '2021-01-01'::date + mod(i,1000), 'dummyyyy'

  from generate_series(1,6000) a(i);

analyze 과태료;

analyze 신고신청;

  

--튜닝 대상 SQL

select count(*)

  from 고객 a

 where exists (select 1

                 from 신고신청 b

                where reg_date >= '2021-02-02'

                  and a.cust_id = b.cust_id

                offset 0)  --offset은 subquery collapse가 발생하지 않도록 하기 위해 사용

   and exists (select 1

                 from 과태료 c

                where a.cust_id = c.cust_id

                offset 0);

--실행계획

| Aggregate (actual time=454.738..454.739 rows=1 loops=1)                              |

|   Buffers: shared hit=74676                                                          |

|   ->  Seq Scan on "고객" a (actual time=454.734..454.735 rows=0 loops=1)             |

|         Filter: ((SubPlan 1) AND (SubPlan 2))                                        |

|         Rows Removed by Filter: 1000                                                 |

|         Buffers: shared hit=74676                                                    |

|         SubPlan 1                                                                    |

|           ->  Seq Scan on "신고신청" b (actual time=0.039..0.039 rows=1 loops=1000)  |

|                 Filter: ((reg_date >= '2021-02-02'::date) AND (a.cust_id = cust_id)) |

|                 Rows Removed by Filter: 690                                          |

|                 Buffers: shared hit=21417                                            |

|         SubPlan 2                                                                    |

|           ->  Seq Scan on "과태료" c (actual time=0.427..0.427 rows=0 loops=968)     |

|                 Filter: (a.cust_id = cust_id)                                        |

|                 Rows Removed by Filter: 10000                                        |

|                 Buffers: shared hit=53240                                            |

| Planning Time: 0.100 ms                                                              |

| Execution Time: 454.832 ms  

위 실행계획을 보면 옵티마이저는 b 집합으로 먼저 filter 처리 후, 그 결과를 c집합으로 filter 처리 하였다. 옵티마이저는 "신고신청" 테이블 집합의 row수가 더 적을 것이라고 판단한 것이다.

하지만 실제로는 "과태료" 테이블 집합이 더 row수가 적다면, 위 실행계획은 최적이 아닌 것이다.

오라클 튜닝 패턴으로 SQL을 아래와 같이 수정하였다. (exists 절 밑의 subquery 위치를 변경)

select count(*)

  from 고객 a

 where exists (select 1

                 from 과태료 c

                where a.cust_id = c.cust_id

                offset 0)

   and exists (select 1

                 from 신고신청 b

                where reg_date >= '2021-02-02'

                  and a.cust_id = b.cust_id

                offset 0);

하지만 실행계획은 변함이 없다.

이런 경우 아래와 같이 with clause를 사용하면 원하는 순서대로 filtering 하도록 조정할 수 있다.

WITH subq AS materialized (

  SELECT a.cust_id

    FROM 고객 a

   WHERE EXISTS (SELECT 1

                   FROM 과태료 c

                  WHERE a.cust_id = c.cust_id

                  offset 0

                )

)

SELECT 

  FROM subq d

 WHERE EXISTS (SELECT 1

                 FROM 신고신청 b

                WHERE d.cust_id = b.cust_id

                  AND reg_date >= '2021-02-02'

                  offset 0);

--SQL 튜닝 후 실행계획

 CTE Scan on subq d (actual time=448.614..448.615 rows=0 loops=1)                    |

|   Filter: (SubPlan 3)                                                               |

|   Rows Removed by Filter: 9                                                         |

|   Buffers: shared hit=56171                                                         |

|   CTE subq                                                                          |

|     ->  Seq Scan on "고객" a (actual time=0.011..444.300 rows=9 loops=1)            |

|           Filter: (SubPlan 1)                                                       |

|           Rows Removed by Filter: 991                                               |

|           Buffers: shared hit=54533                                                 |

|           SubPlan 1                                                                 |

|             ->  Seq Scan on "과태료" c (actual time=0.443..0.443 rows=0 loops=1000) |

|                   Filter: (a.cust_id = cust_id)                                     |

|                   Rows Removed by Filter: 9910                                      |

|                   Buffers: shared hit=54514                                         |

|   SubPlan 3                                                                         |

|     ->  Seq Scan on "신고신청" b (actual time=0.478..0.478 rows=0 loops=9)          |

|           Filter: ((reg_date >= '2021-02-02'::date) AND (d.cust_id = cust_id))      |

|           Rows Removed by Filter: 6000                                              |

|           Buffers: shared hit=1638                                                  |

| Planning Time: 0.079 ms                                                             |

| Execution Time: 448.702 ms 


위 실행계획을 보면 with절에서 "과태료" 집합으로 먼저 filter 처리했고, 그 결과 집합을 "신고신청" 집합으로 filter 처리했음을 알 수 있다.

block I/O 가 74676 --> 56176 으로 감소했다.

(튜닝 전에는 "신고신청" 테이블을 968회 scan 했으나, 튜닝 후에는 9회 scan하였다.)


   

postgresdba.com