테스트용 테이블 작성하기
다음 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문1 | SQL문2 |
|---|---|
| 4분 26 초 | 55초 |
