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


총 게시물 68건, 최근 0 건
   

null 허용 컬럼에 coalesce(변수명, 컬럼명) 패턴 사용 금지

글쓴이 : 모델광 날짜 : 2021-04-22 (목) 15:42 조회 : 42
최근 개발자가 결과 값이 누락되는 현상이 있다고 해서 필자가 재현해 보았다.
필자도 왜 이런 현상이 발생하는지는 모르겠다.
일단 프로젝트 내 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;





   

postgresdba.com