(PostgreSQL 성능 튜닝) 대량 데이터 날짜 항목을 검색할때 가장 빠른 SQL Where문 작성하기

PostgreSQL

대량의 데이터에서 date타입 또는 문자타입의 항목에 저장된 날짜 정보를 대상으로 SQL 검색을 하는 경우, Where문을 어떻게 작성하는가에 따라 성능 차이가 납니다. 이 글에서는 SQL Where문 작성 방법에 따라 성능 차이가 얼마나 발생하는지를 비교해 보겠습니다.

테스트용 테이블 작성하기

다음 ddl문으로 대량의 데이터를 저장할 testtbl테이블을 작성합니다. 

DROP TABLE IF EXISTS testtbl;
CREATE TABLE IF NOT EXISTS testtbl
(
    SOME_DATE character varying(10),
    SOME_TIME character varying(12),
    SOME_DATETIME character varying(23),
    SOME_TIMESTAMP TIMESTAMP
);

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

  • SOME_DATE 항목에는 문자열 날짜를 저장
  • SOME_TIME 항목에는 문자열 시간을 저장
  • SOME_DATETIME 항목에는 문자열 날짜/시간을 저장
  • SOME_TIMESTAMP 항목에는 TIMESTAMP형 날짜/시간을 저장
truncate table testtbl;
insert into testtbl
  select to_char(i,'YYYY/MM/DD'),
  to_char(i,'HH24:MI:SS.MS'),
  to_char(i,'YYYY/MM/DD HH24:MI:SS.MS'),  
  i
 from  generate_series('2021-01-01 00:01'::timestamp, '2021-01-31 23:59'::timestamp,  '1 seconds 17 milliseconds'::interval) i;

실행 결과는 다음과 같습니다. 테이블에 2,633,511행 삽입 되었습니다.

INSERT 0 2633511
Query returned successfully in 15 secs 617 msec.

데이터는 다음과 같이 2021-01-01 00:01에서 2021-01-31 23:59까지 1.17초 간격으로 날짜 데이터가 삽입되었습니다.

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

범위 검색 조건문 성능비교

작성한 대량 데이터의 전체 데이터 건수를 확인합니다. 대략 2백6십만건의 레코드가 작성되었습니다.

postgres=# select count(*) from testtbl;
  count
---------
 2633511
(1 row)
Time: 183.357 ms

조건1(문자열 연산자 ||와 substring함수 사용)

다음 Where문은 SOME_DATE와 SOME_TIME의 문자 정보를 문자열 연산자 || 와 substring함수를 사용하여 YYYY/MM/DDHH24:MI 형식으로 변경한 다음에 비교합니다. 문자열 연산자 || 와 substring함수가 2,633,511 레코드만큼 실행되어 비교하기 때문에 처리에 시간이 걸립니다. 여기서는 1.3초의 시간이 소요되었습니다. 2천만 건 또는 2억 건 이상의 데이터를 가진 테이블에서 실행한다면 시간이 더 소요될 수 있습니다.

SELECT count(*) FROM testtbl
WHERE SOME_DATE||substring(SOME_TIME,1,5) >= '2021/01/1000:00' AND
      SOME_DATE||substring(SOME_TIME,1,5) <= '2021/01/1523:59';

 count
--------
 509735
(1 row)
Time: 1303.906 ms (00:01.304)

조건2(문자열 연산자 ||만 사용)

다음은 위 조건1과 달리 substring 함수를 사용하지 않고 똑같은 결과를 얻을 수 있는 SQL Where문입니다. HH24:MI 영역만 취득해서 비교하기 보다는 고정 문자 범위의 최저값과 최고값을 지정하여 substring 함수를 사용하지 않고 같은 결과를 얻습니다. 2,633,511 레코드 만큼의 substring 함수가 실행되지 않기 때문에 조건1의 1.3초보다 빠른 0.8초에 실행이 종료되었습니다. 

SELECT count(*) FROM testtbl
WHERE SOME_DATE||SOME_TIME >= '2021/01/1001:00:00.000' AND
      SOME_DATE||SOME_TIME <= '2021/01/1523:59:59.999';

 count
--------
 506195
(1 row)
Time: 865.384 ms

조건3(문자열 연산자 ||와 substring 함수 둘 다 미사용)

위 조건2에서는 문자 결합을 조건문에서 실행하고 조건3에서는 이미 문자 결합된 항목을 사용했지만 성능 면에서는 그다지 큰 차이가 없습니다.

SELECT count(*) FROM testtbl
WHERE SOME_DATETIME >= '2021/01/10 00:00:00.000' AND
      SOME_DATETIME <= '2021/01/15 23:59:59.999';
 count
--------
 509735
(1 row)
Time: 862.710 ms

조건4(timestamp형 변환 비교)

timestamp형 항목을 비교할 때는 다음과 같이 범위 검색 방법을 사용합니다. 0.2초로 가장 빨리 검색 결과를 보입니다.

SELECT count(*) FROM testtbl
WHERE SOME_TIMESTAMP >= '2021/01/10 00:00:00.000'::timestamp AND
      SOME_TIMESTAMP <= '2021/01/15 23:59:59.999'::timestamp;

 count
--------
 509735
(1 row)
Time: 224.037 ms

조건5(to_timestamp함수 사용)

timestamp형 항목을 비교할 때 위 조건4와 같은 결과를 예상했지만 결과는 5.4초로 가장 느립니다. 사용하지 않는 것을 추천하는 검색 방법입니다.

SELECT count(*) FROM testtbl
WHERE SOME_TIMESTAMP >= to_timestamp('2021/01/10 00:00:00.000','YYYY/MM/DD HH24:MI:SS.MS') AND
      SOME_TIMESTAMP <= to_timestamp('2021/01/15 23:59:59.999','YYYY/MM/DD HH24:MI:SS.MS');

 count
--------
 509735
(1 row)
Time: 5439.917 ms (00:05.440)

비교 결과

위 성능 체크 결과 조건4의 Where문이 가장 빠른 검색 방법임을 알 수 있습니다.

조건1조건2조건3조건4조건5
1.3초0.8초0.8초0.2초5.4초

댓글

제목과 URL을 복사했습니다