① 리터럴 조건값의 자료형 불일치
BAD: 127.352ms, hit=288, read=8012
INTEGER 컬럼에 NUMERIC으로 비교
SELECT o.ord_no
FROM startdbpg.tr_ord o
WHERE o.ord_no = 1001.00
;
SQL
복사
실행계획 보기
•
정수 컬럼 ord_no를 소수 1001.00과 비교하자, DB가 컬럼 쪽을 NUMERIC으로 바꿔서 비교합니다((ord_no)::numeric).
•
내가 쓰지 않았을 뿐, 컬럼을 변형한 것과 같습니다. 그래서 인덱스를 쓰지 못하고 약 73만 건을 모두 읽습니다(Seq Scan).
•
실무에서는 애플리케이션에서 소수형 값이 넘어올 때 주로 생깁니다.
GOOD: 0.107ms, hit=4
INTEGER 컬럼에 INTEGER로 비교
SELECT o.ord_no
FROM startdbpg.tr_ord o
WHERE o.ord_no = 1001
;
SQL
복사
실행계획 보기
•
자료형이 같아 컬럼 변환 없이 인덱스로 바로 찾습니다(Index Cond).
② PREPARE 바인드 변수의 자료형 불일치
애플리케이션이 바인드 변수로 값을 넘기는 상황을 SQL로 재현한 것입니다.
BAD: 62.280ms, hit=64, read=8236
INTEGER 컬럼에 NUMERIC 바인드 변수
PREPARE stmt_ord_num(NUMERIC) AS
SELECT o.ord_no
FROM startdbpg.tr_ord o
WHERE o.ord_no = $1
;
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE stmt_ord_num(1001);
SQL
복사
실행계획 보기
•
$1을 NUMERIC으로 선언했기 때문에 정수 1001을 넘겨도 NUMERIC으로 처리됩니다.
•
결과는 ①과 같습니다. 컬럼이 변환되어 인덱스를 쓰지 못하고 약 73만 건을 모두 읽습니다(Seq Scan).
GOOD: 0.081ms, hit=4
INTEGER 컬럼에 INTEGER 바인드 변수
PREPARE stmt_ord_int(INTEGER) AS
SELECT o.ord_no
FROM startdbpg.tr_ord o
WHERE o.ord_no = $1
;
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
EXECUTE stmt_ord_int(1001);
SQL
복사
실행계획 보기
•
$1을 INTEGER로 선언해 컬럼과 자료형이 같습니다.
•
컬럼 변환 없이 인덱스로 바로 찾습니다(Index Cond).
③ VARCHAR 컬럼에 CHAR 조건값
흔한 경우는 아니지만 실제로 발생한 사례입니다. 같은 문자형이라도 자료형이 다르면 결과는 ①과 같습니다. 조건값의 자료형을 함부로 바꾸면 안 됩니다.
BAD: 5881.984ms, hit=2524, read=348033
VARCHAR 컬럼에 CHAR(8) 조건값
SELECT o.ord_no ,o.ord_dtm
FROM startdbpg.tr_ord_big o
WHERE o.ord_ymd = '20240110'::CHAR(8)
;
SQL
복사
실행계획 보기
•
CHAR와 비교하자 DB가 컬럼 쪽을 CHAR(bpchar)로 바꿔서 비교합니다((ord_ymd)::bpchar).
•
그래서 인덱스를 쓰지 못하고 약 2,600만 건을 모두 읽습니다(Seq Scan).
GOOD: 3.067ms, hit=271
VARCHAR 컬럼에 문자열 그대로
SELECT o.ord_no ,o.ord_dtm
FROM startdbpg.tr_ord_big o
WHERE o.ord_ymd = '20240110' -- ::TEXT or ::VARCHAR
;
SQL
복사
실행계획 보기
•
따옴표 문자열을 그대로 쓰면 컬럼 변환 없이 인덱스로 바로 찾습니다(Index Cond).
•
참고: (ord_ymd)::text는 PostgreSQL이 VARCHAR를 TEXT와 사실상 같은 자료형으로 다루기 때문에 붙는 표시입니다. 실제 변환이 아니라서 성능과는 관계없습니다.
KEY POINT
•
컬럼과 비교값의 자료형을 맞추고, 조건값의 자료형을 함부로 바꾸지 않습니다. 리터럴, 바인드 변수, ::CHAR(n) 같은 형변환 모두 해당합니다.
•
자료형이 다르면 DB가 컬럼 쪽을 바꿀 수 있고, 그러면 컬럼 변형처럼 인덱스를 쓰지 못합니다.
•
실행계획의 Filter에 (컬럼)::자료형이 보이면 형변환 때문에 인덱스를 못 쓴 것입니다. 단, VARCHAR 컬럼에 붙는 ::text는 성능과 관계없습니다.
TIP-1. 테이블 컬럼 자료형 확인하기
-- 테이블의 컬럼 자료형 확인하기
SELECT c.column_name ,c.data_type ,c.character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.table_schema = 'startdbpg'
AND c.table_name = 'tr_ord'
AND c.column_name = 'ord_no'
;
SQL
복사
TIP-2. DBeaver에서 PREPARE 만들기
•
DBeaver에서 PREPARE 형태로 SQL을 실행하려면 다음과 같이 DBeaver의 설정을 변경해야 합니다.
◦
파라미터 치환 기능을 OFF 해야 $1, $2가 그대로 서버로 전달됩니다.
◦
Window → Preferences → Editors → SQL Editor → SQL Processing → (Parameters/Variables) → Enable SQL parameters 체크 해제
◦
이 설정을 켜두면 $1이 에디터에서 값으로 치환되어 서버 PREPARE가 값 고정 쿼리로 생성되는 실수를 하게 됩니다.
TIP-3. 애플리케이션 파라미터 자료형
•
바인드 변수의 자료형은 애플리케이션 변수(필드)의 자료형으로 정해집니다. 정수 컬럼이면 변수도 정수형(int, long)으로 선언합니다.
•
BigDecimal(Java), Decimal(Python)은 NUMERIC으로 전달됩니다. 정수 컬럼 조건에는 쓰지 않습니다.
•
문자열은 드라이버가 VARCHAR나 unknown으로 넘기므로 대부분 문제가 없습니다.
TIP-4. VARCHAR와 CHAR의 차이
•
VARCHAR: 가변 길이 문자열로, 입력한 문자열을 그대로 저장합니다.
•
CHAR: 고정 길이 문자열로, 부족한 길이를 공백으로 채웁니다.
•
실행계획의 bpchar는 PostgreSQL에서 CHAR를 가리키는 이름입니다.
DEEP DIVE — SQL TUNER for PostgreSQL 기본원리편