필자도 왜 이런 현상이 발생하는지는 모르겠다.
일단 프로젝트 내 SQL 작성 가이드에 아래의 패턴은 사용하지 않도록 공지를 하였다.
사용자가 값을 입력할 수도 있고 입력하지 않을 수도 있는 조건에 coalesce를 사용할 때에는 해당 컬럼의 null 허용 여부를 확인해야 한다.
결론적으로 해당 컬럼이 null 허용이라면 coalesce 방식을 사용해서는 안된다.
analdb=# select * from t1;
prod_id | prod_nm
---------+-----------------
PP3 | 999998TEST_NAME
PP2 | 999997TEST_NAME
PP4 | 999999TEST_NAME
PP1 | 999996TEST_NAME
PP0 |
위와 같이 prod_nm 은 null 허용 컬럼이다. 총 5개의 row가 있다.
아래 SQL 작성자는 $1 변수에 값이 없으면 full table scan 하도록 작성하엿다.
하지만 실제로 수행해 보면 prod_nm 이 null인 row는 추출에서 누락되었다.
이는 옵티마이저가 prod_nm is not null 인 조건을 추가한 것으로 추정된다.
(실행계획에서는 is not null 조건이 표시되지는 않고 있다.)
analdb=# prepare foo(varchar) as
analdb-# select *
analdb-# from t1
analdb-# where prod_nm = coalesce($1,prod_nm);
PREPARE
Time: 1.872 ms
analdb=# execute foo(null);
prod_id | prod_nm
---------+-----------------
PP3 | 999998TEST_NAME
PP2 | 999997TEST_NAME
PP4 | 999999TEST_NAME
PP1 | 999996TEST_NAME
(4 rows)
Time: 0.826 ms
실행계획
Seq Scan on portal.t1 (cost=0.00..13.50 rows=1 width=256) (actual time=0.026..0.028 rows=
4 loops=1)
Output: prod_id, prod_nm
Filter: ((t1.prod_nm)::text = (COALESCE(t1.prod_nm))::text)
Rows Removed by Filter: 1
Planning Time: 0.078 ms
Execution Time: 0.047 ms
(6 rows)
만약 해당 컬럼에 인덱스가 있다면, 아래와 같이 수정하는 것이 성능상 유리하다.
SELECT *
FROM T1
WHERE $1 IS NOT NULL
AND PROD_NM = $1
UNION ALL
SELECT *
FROM T1
WHERE $1 IS NULL
AND 1=1;
Added on July 7, 2022
위 글에서 필자는 "이는 옵티마이저가 prod_nm is not null 인 조건을 추가한 것으로 추정된다." 라고 하였다. 그러나 가만히 생각해 보면 null 값이 빠지는 것은 당연하다. $1에 null 이 입력되면 위 쿼리는 아래와 같이 변형 된다.
select *
from t1
where prod_nm = prod_nm;
당연히 null은 비교대상이 될 수 없으므로, 위 쿼리를 실행하면 prod_nm 이 null인 row는 출력되지 않는 것이다.
https://cafe.naver.com/dbian/5442 게시물에 '보니랑' 님이 쓴 댓글을 참조하기 바란다.
