[PostgreSQL] 사용자 키값으로 max+1 할때, 성능이슈가 있을 경우 해결방법 (자리수 제한이 있을때): 호텔 빈방찾기

현재 사용중인 쿼리가 속도 문제가 있다. 누가 개발한 쿼리인지 모르겠지만…. 뭔가 난잡해보인다. 참고로 실제 테이블과 컬럼은 아니다, 모두 임으로 변경하였다.

SELECT LPAD(MIN(A.USER_DEVICE_ID + 1)::VARCHAR, 5, '0') AS NEXT_USER_DEVICE_ID
FROM (
	SELECT USER_DEVICE_ID::INT4 AS USER_DEVICE_ID
	FROM TB_USER
	WHERE COMPANY_NO = #{compNo}
	AND COALESCE(DEL_YN, 'N') = 'N'
	UNION ALL
	SELECT 0
) A LEFT JOIN (
	SELECT USER_DEVICE_ID::INT4 AS USER_DEVICE_ID
	FROM TB_USER
	WHERE COMPANY_NO = #{compNo}
	AND COALESCE(DEL_YN, 'N') = 'N'
) B
ON A.USER_DEVICE_ID::INT4 + 1 = B.USER_DEVICE_ID::INT4
WHERE B.USER_DEVICE_ID IS NULL	

속도문제가 있고, 데이터가 쌓이면 언제가는 db가 터질 수도 있는 상황이라 개선이 필요하다.

그래서 아래처럼 변경하였다.

SELECT LPAD(COALESCE(
	(SELECT MIN(USER_DEVICE_ID::INT4 + 1)::VARCHAR
	 FROM TB_USER
	 WHERE COMPANY_NO = #{compNo}
	 AND COALESCE(DEL_YN, 'N') = 'N'
	 AND USER_DEVICE_ID::INT4 + 1 NOT IN (
		 SELECT USER_DEVICE_ID::INT4
		 FROM TB_USER
		 WHERE COMPANY_NO = #{compNo}
		 AND COALESCE(DEL_YN, 'N') = 'N'
	 )), '1'), 5, '0') AS NEXT_USER_DEVICE_ID;

위 쿼리도 나중에 데이터가 많아지면 NOT IN절이 문제가 될 수 있어서 아래 쿼리로 변경되었다.

SELECT LPAD(COALESCE(
	(SELECT MIN(USER_DEVICE_ID::INT4 + 1)::VARCHAR
	 FROM TB_USER t1
	 WHERE COMPANY_NO = #{compNo}
	 AND COALESCE(DEL_YN, 'N') = 'N'
	 AND NOT EXISTS (
		 SELECT 1
		 FROM TB_USER t2
		 WHERE t2.COMPANY_NO = #{compNo}
		 AND COALESCE(t2.DEL_YN, 'N') = 'N'
		 AND t2.USER_DEVICE_ID::INT4 = t1.USER_DEVICE_ID::INT4 + 1
	 )), '1'), 5, '0') AS NEXT_USER_DEVICE_ID;

하지만 위 쿼리도 최종적으로는 데이터가 쌓이면 쿼리튜닝이 추가적으로 필요해진다.

쿼리를 변경작업하면서 알게된 사실 하나는 max+1 이 무제한이 아니라 1번 부터 99999까지만 가능한 상태이고, 중간에 빈 자리가 생기면 그 값을 찾아서 사용해야하는 것이다.

개발이 누락된거네??

가만히 생각해보면 “호텔 빈방 찾기”와 유사한 내용이다.

아래 쿼리로 한방에 핸결된다.

SELECT LPAD(TEXT(A),5, '0') AS NEXT_USER_DEVICE_ID
FROM GENERATE_SERIES(1,99999) AS A
WHERE LPAD(TEXT(A),5,'0') NOT IN (
	  SELECT USER_DEVICE_ID FROM TB_USER
	  WHERE COMPANY_NO = #{compNo}
	  AND COALESCE (DEL_YN, 'N') = 'N')
LIMIT 1

1~99999 중에 비어 있는 곳이 있으면 제일 작은 숫자부터 리턴해준다.