[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 중에 비어 있는 곳이 있으면 제일 작은 숫자부터 리턴해준다.

99999부터 역방향으로 빈방을 찾아야 한다면?

GENERATE_SERIES는 기본적으로 오름차순으로 시퀀스를 생성하지만,
세 번째 인자(step) 를 음수로 주면 내림차순 시퀀스를 만들 수 있습다.

역순(9999 → 1)으로 생성

SELECT generate_series(9999, 1, -1) AS num;

실무 예시: 비어 있는 번호 찾기 (역순)

예를 들어 고객 테이블에 customer_no가 있고,
1~9999 중에서 사용되지 않은 번호 중 가장 큰 값(뒤에서부터 비어있는 번호) 을 찾고 싶다면:

SELECT gs.num
FROM generate_series(9999, 1, -1) AS gs(num)
LEFT JOIN customer c ON c.customer_no = gs.num
WHERE c.customer_no IS NULL
LIMIT 1;

이렇게 하면:

• 99999부터 1까지 차례로 확인하면서
• 아직 사용되지 않은 번호(customer_no가 없는 값)를 찾고
• 가장 큰(뒤에서부터 첫 번째) 비어 있는 번호를 반환한다.