아래 패턴의 쿼리 개선 방법을 기술한다.
아래에서 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하였다.)
