(PostgreSQL 성능 튜닝) JOIN ON 테이블 결합 조건문에서 함수 제거하기

PostgreSQL
2022.05.13

SELECT * FROM TBL_A1 X JOIN TBL_A2 Y ON 함수(X.ITEM) = 함수(Y.ITEM) 과 같이 테이블을 결합해서 데이터를 취득할 때, 결합 조건 항목에 대해 함수를 사용하는 경우와 사용하지 않는 경우의 성능을 비교 분석한 내용을 소개합니다.

테스트용 테이블 작성하기

JOIN ON 테이블 결합 조건문에서 함수 사용 또는 미사용 시의 성능 비교를 위해 다음 ddl문으로 대량의 데이터를 저장할 tbl_a1, tbl_a2 테이블을 작성합니다. 

DROP TABLE IF EXISTS tbl_a1;
CREATE TABLE IF NOT EXISTS tbl_a1
(
    key1 TIMESTAMP PRIMARY KEY,
    item1 character varying(23),
    item2 character varying(12),
    item3 character varying(23)
);

DROP TABLE IF EXISTS tbl_a2;
CREATE TABLE IF NOT EXISTS tbl_a2
(
    key1 TIMESTAMP PRIMARY KEY,
    item1 character varying(23),
    item2 character varying(12),
    item3 character varying(23)
);

다음 dml문으로 대량의 데이터를 작성합니다.

  • item1항목에는 문자열 날짜를 저장
  • item2항목에는 문자열 시간을 저장
  • item3항목에는 문자열 날짜/시간/요일을 저장
  • key1의 PK항목에는 TIMESTAMP형 날짜/시간을 저장
truncate table tbl_a1;
insert into tbl_a1
  select 
  i,
  to_char(i,'YYYY/MM/DD'),
  to_char(i,'HH24:MI:SS.MS'),
  to_char(i,'YYYYMMDDHH24MISS.MS') || ' ' || to_char(i,'Dy')  
 from  generate_series('2000-01-01 00:01'::timestamp, '2022-12-31 23:59'::timestamp,  '5 minutes'::interval) i;

truncate table tbl_a2;
insert into tbl_a2
  select 
  i,
  to_char(i,'YYYY/MM/DD'),
  to_char(i,'HH24:MI:SS.MS'),
  to_char(i,'YYYYMMDDHH24MISS.MS') || ' ' || to_char(i,'Dy')
 from  generate_series('2000-01-01 00:01'::timestamp, '2022-12-31 23:59'::timestamp,  '8 minutes'::interval) i;

실행 결과는 다음과 같습니다. tbl_a1테이블에 2,419,488행, tbl_a2테이블에 1,512,180행이 삽입 되었습니다.

PostgreSQL에서 대량 데이터를 작성하는 방법에 대해서는 PostgreSQL generate_series함수를 사용해서 대량 데이터 작성하기 글을 참조하십시오.

INSERT 0 2419488
Query returned successfully in 24 secs 1 msec.

INSERT 0 1512180
Query returned successfully in 15 secs 873 msec.

성능비교

위에서 작성한 대량 데이터를 가지고 다음 2가지 SQL문의 실행 결과를 비교합니다.

  • 결합 조건 항목에 대해 함수를 적용하는 경우
  • 결합 조건 항목에 대해 함수를 적용하지 않는 경우

SQL문1

select count(*) as cnt from tbl_a1 X INNER JOIN tbl_a2 Y ON
UPPER(TRIM(X.item3)) = UPPER(TRIM(Y.item3))

실행 결과는 다음과 같습니다. 결과가 표시되기까지 29초 걸렸습니다.

cnt
------
302436

Successfully run. Total query runtime: 29 secs 723 msec.
1 rows affected.

SQL문2

select count(*) as cnt from tbl_a1 X INNER JOIN tbl_a2 Y ON
X.item3 = Y.item3

실행 결과는 다음과 같습니다. 결과가 표시되기까지 3초 걸렸습니다.

cnt
------
302436

Successfully run. Total query runtime: 3 secs 188 msec.
1 rows affected.

비교 결과

위 성능 비교 결과, SQL문2가 가장 빠른 취득 방법임을 알 수 있습니다.

SQL문1SQL문2
29 초3초

조건문에서의 함수를 사용하는 횟수와 데이터 건수에 따라 SQL문 처리에 시간이 걸립니다. 일관성 있게 데이터를 저장할 수 있도록 설계부터 테이블과 항목을 고려한다면 데이터 건수를 줄일 수 없더라도 함수의 사용을 줄일 수는 있습니다.

제목과 URL을 복사했습니다