(PostgreSQL 성능 튜닝) UNION ALL 결과의 출력 건수 제한하기

스폰서 링크
PostgreSQL
스폰서 링크
스폰서 링크

테스트용 테이블 작성하기

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

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

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

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

  • item1항목에는 문자열 날짜를 저장
  • item2항목에는 문자열 시간을 저장
  • item3항목에는 문자열 날짜/시간을 저장
  • key1의 PK항목에는 TIMESTAMP형 날짜/시간을 저장
truncate table tbl_test1;
insert into tbl_test1
  select 
  i,
  to_char(i,'YYYYMMDD'),
  to_char(i,'HH24:MI:SS.MS'),
  to_char(i,'YYYY/MM/DD HH24:MI:SS.MS')  
 from  generate_series('2021-01-01 00:01'::timestamp, '2022-12-31 23:59'::timestamp,  '1 seconds 73 milliseconds'::interval) i;

truncate table tbl_test2;
insert into tbl_test2
  select 
  i,
  to_char(i,'YYYYMMDD'),
  to_char(i,'HH24:MI:SS.MS'),
  to_char(i,'YYYY/MM/DD HH24:MI:SS.MS')  
 from  generate_series('2021-01-01 00:01'::timestamp, '2022-12-31 23:59'::timestamp,  '1 seconds 37 milliseconds'::interval) i;

실행 결과는 다음과 같습니다. tbl_test1테이블에 58,780,877 행 tbl_test2테이블에 60,821,486행 삽입 되었습니다.

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

INSERT 0 58780877
Query returned successfully in 19 min 11 secs.

INSERT 0 60821486
Query returned successfully in 19 min 37 secs.

성능비교

대량 데이터를 union all해서 취득한 결과에서 선두 5 레코드를 출력하는 2가지 방법을 비교합니다.

첫번째는 대량 데이터를 union all로 하나로 합치고 취득하는 데이터도 대량 데이터입니다. 이 결과에 대해 PK항목으로 정렬한 데이터에서 선두 5 레코드를 출력합니다.

두번째는 각각의 대량 데이터를 PK항목으로 정렬하고 선두 6 레코드를 취득해서 union all문으로 합치면 최대 12 레코드가 됩니다. 이 12 레코드에서 다시 PK항목으로 정렬하고 선두 5 레코드를 출력합니다.

SQL문1

tbl_test1테이블의 58,780,877행 데이터와 tbl_test2테이블에 60,821,486행 데이터를 union all로 하나로 합치고 item1항목으로 정렬한 결과의 처음 5 레코드를 출력합니다.

SET client_min_messages TO notice;
DO $$
DECLARE
  rec RECORD;
  rowcount numeric := 0;
BEGIN

FOR rec IN
  select * from (
    select * from tbl_test1 WHERE item1 > '20220101' 
    union all
    select * from tbl_test1 WHERE item1 > '20220101' 
  ) X  order by key1
 LOOP
    IF (rowcount = 5) THEN
        EXIT;
    END IF;
    RAISE NOTICE '% % % %', rec.key1, rec.item1, rec.item2, rec.item3;
    rowcount := rowcount + 1;
 END LOOP;
END
$$
;

실행 결과는 다음과 같습니다. 실행하는데 4분26초 걸렸습니다.

NOTICE:  2022-01-02 00:00:00.08 20220102 00:00:00.080 2022/01/02 00:00:00.080
NOTICE:  2022-01-02 00:00:00.08 20220102 00:00:00.080 2022/01/02 00:00:00.080
NOTICE:  2022-01-02 00:00:01.153 20220102 00:00:01.153 2022/01/02 00:00:01.153
NOTICE:  2022-01-02 00:00:01.153 20220102 00:00:01.153 2022/01/02 00:00:01.153
NOTICE:  2022-01-02 00:00:02.226 20220102 00:00:02.226 2022/01/02 00:00:02.226
DO

Query returned successfully in 4 min 26 secs..

SQL문2

tbl_test1테이블의 58,780,877행 데이터와 tbl_test2테이블에 60,821,486행 데이터를 union all해서 item1항목으로 정렬한 결과의 처음 5 레코드를 출력합니다.

SET client_min_messages TO notice;
DO $$
DECLARE
  rec RECORD;
  rowcount numeric := 0;
BEGIN

FOR rec IN
  select * from (
   (select * from tbl_test1 WHERE item1 > '20220101' order by key1 limit 6)
    union all
   (select * from tbl_test1 WHERE item1 > '20220101' order by key1 limit 6)
  ) X  order by key1 limit 6
 LOOP
    IF ( rowcount = 5) THEN
	   EXIT;
	END IF;
    RAISE NOTICE '% % % %', rec.key1, rec.item1, rec.item2, rec.item3;
	rowcount := rowcount + 1;
 END LOOP;
END
$$
;

실행 결과는 다음과 같습니다. 실행하는데 55초 걸렸습니다.

NOTICE:  2022-01-02 00:00:00.08 20220102 00:00:00.080 2022/01/02 00:00:00.080
NOTICE:  2022-01-02 00:00:00.08 20220102 00:00:00.080 2022/01/02 00:00:00.080
NOTICE:  2022-01-02 00:00:01.153 20220102 00:00:01.153 2022/01/02 00:00:01.153
NOTICE:  2022-01-02 00:00:01.153 20220102 00:00:01.153 2022/01/02 00:00:01.153
NOTICE:  2022-01-02 00:00:02.226 20220102 00:00:02.226 2022/01/02 00:00:02.226
DO
Query returned successfully in 55 secs 629 msec.

비교 결과

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

SQL문1SQL문2
4분 26 초 55초
제목과 URL을 복사했습니다