Greenplum Disaster Recovery 소개
Greenplum 7에서는 Convering 인덱스를 제공
Covering 인덱스는 인덱스 컬럼 이외에 자주 Access되는 컬럼을 추가하여 INDEX ONLY Scan 되도록 수행
테스트 결과,
조회 조건이 INDEX 컬럼과 조회 컬럼을 같이 있을 경우 INDEX ONLY scan으로 플랜이 만들어지나,
필터링 조건이 추가될때 INDEX ONLY scan이 아닌 INDEX scan으로 플랜 생성
성능적인 측면에서는 테스트 데이터 사이즈가 작기 때문에 효과는 미지수이나,
대용량 데이터 처리시에는 추가 테스트가 필요 함.
1. 세션 파라미터 확인 - DEFAULT 설정일 경우
SHOW optimizer; --ON
SHOW enable_indexonlyscan; --ON
SHOW optimizer_enable_dynamicindexonlyscan; --ON
SHOW optimizer_enable_indexonlyscan; --ON
2. 마스터성 테이블일 경우 커버링 인덱스 테스트
DROP TABLE IF EXISTS public.customer;
CREATE TABLE public.customer
(
cust_no int,
cust_nm TEXT,
gender varchar(1),
status_yn varchar(1)
)
WITH (appendonly=TRUE, compresstype=zstd, compresslevel=7)
DISTRIBUTED BY (cust_no)
;
INSERT INTO public.customer
SELECT i, 'cust_nm_'||trim(to_char(i, '000'))
, CASE WHEN (i % 2) = 0 THEN 'M' ELSE 'F' END gender
, CASE WHEN (i % 50) = 0 THEN 'N' ELSE 'Y' END status_yn
FROM generate_series(1, 10000) i
;
--일반 인덱스 테스트
DROP INDEX IF EXISTS public.ixu_customer_cust_no;
CREATE UNIQUE INDEX ixu_customer_cust_no ON public.customer(cust_no);
EXPLAIN ANALYZE
SELECT cust_no, cust_nm
FROM public.customer
WHERE cust_no = 100;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------+
Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..387.96 rows=1 width=16) (actual time=0.857..0.858 rows=1 loops=1) |
-> Bitmap Heap Scan on customer (cost=0.00..387.96 rows=1 width=16) (actual time=0.292..0.294 rows=1 loops=1) |
Recheck Cond: (cust_no = 100) |
Heap Blocks: exact=1 |
-> Bitmap Index Scan on ixu_customer_cust_no (cost=0.00..0.00 rows=0 width=0) (actual time=0.081..0.082 rows=1 loops=1)|
Index Cond: (cust_no = 100) |
Optimizer: GPORCA |
Planning Time: 2.613 ms |
(slice0) Executor memory: 52K bytes. |
(slice1) Executor memory: 368K bytes (seg7). |
Memory used: 128000kB |
Execution Time: 1.585 ms |
--Covering 인덱스 테스트
DROP INDEX IF EXISTS public.ixu_customer_cust_no;
DROP INDEX IF EXISTS public.ixu_customer_cust_no_covering;
CREATE UNIQUE INDEX ixu_customer_cust_no_covering ON public.customer(cust_no) INCLUDE (cust_nm);
--필 조건에 인덱스 컬럼과 조회 컬럼에 including 컬럼이 추가될 때 index only scan 수행
EXPLAIN ANALYZE
SELECT cust_no, cust_nm
FROM public.customer
WHERE cust_no = 100;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------+
Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=16) (actual time=0.794..0.796 rows=1 loops=1) |
-> Index Only Scan using ixu_customer_cust_no_covering on customer (cost=0.00..6.00 rows=1 width=16) (actual time=0.159..0.160 rows=1 loops=1)|
Index Cond: (cust_no = 100) |
Heap Fetches: 0 |
Optimizer: GPORCA |
Planning Time: 2.410 ms |
(slice0) Executor memory: 14K bytes. |
(slice1) Executor memory: 147K bytes (seg7). |
Memory used: 128000kB |
Execution Time: 1.447 ms |
--필터 조건에 커버링 인덱스이외의 컬럼이 있을 경우 index only scan이 수행되지 않음. - 일반 index 스캔 수행
EXPLAIN ANALYZE
SELECT cust_no, cust_nm
FROM public.customer
WHERE cust_no = 100
AND status_yn = 'Y' ;
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------------------------------+
Result (cost=0.00..387.96 rows=1 width=16) (actual time=0.738..0.739 rows=0 loops=1) |
-> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..387.96 rows=1 width=16) (actual time=0.738..0.738 rows=0 loops=1) |
-> Bitmap Heap Scan on customer (cost=0.00..387.96 rows=1 width=16) (actual time=0.000..0.157 rows=0 loops=1) |
Recheck Cond: (cust_no = 100) |
Filter: ((status_yn)::text = 'Y'::text) |
-> Bitmap Index Scan on ixu_customer_cust_no_covering (cost=0.00..0.00 rows=0 width=0) (actual time=0.009..0.009 rows=1 loops=1)|
Index Cond: (cust_no = 100) |
Optimizer: GPORCA |
Planning Time: 3.510 ms |
(slice0) Executor memory: 57K bytes. |
(slice1) Executor memory: 370K bytes (seg7). |
Memory used: 128000kB |
Execution Time: 1.367 ms
3. 이력성 파티션 테이블에서의 커버링 인덱스 테스트
--커버링 인덱스 컬럼 이외의 조건 추가시 일반 index 스캔 수행 (index only scan 수행되지 않음.)
DROP TABLE IF EXISTS public.order_log;
CREATE TABLE public.order_log
(
order_no int,
cust_no int,
prod_nm TEXT,
order_date varchar(8),
order_amt int
)
WITH (appendonly=TRUE, compresstype=zstd, compresslevel=7)
DISTRIBUTED BY (order_no)
PARTITION BY RANGE (order_date)
(
PARTITION p2001 start('20010101') END ('20020101'),
PARTITION p2002 start('20020101') END ('20030101'),
PARTITION p2003 start('20030101') END ('20040101'),
PARTITION p2004 start('20040101') END ('20050101'),
PARTITION p2005 start('20050101') END ('20060101'),
PARTITION p2006 start('20060101') END ('20070101'),
PARTITION p2007 start('20070101') END ('20080101'),
PARTITION p2008 start('20080101') END ('20090101'),
PARTITION p2009 start('20090101') END ('20100101'),
PARTITION p2010 start('20100101') END ('20110101')
)
;
INSERT INTO public.order_log
SELECT i*j order_no
, j%100 cust_no
, 'prod_'||trim(to_char(i%50, '00000')) prod_nm
, to_char('2001-01-01'::date + i, 'yyyymmdd') order_dt
, round(random()*100)::int * 100 sys_dt
FROM generate_series(1, 364) i
, generate_series(1, 10000) j
;
DROP INDEX IF EXISTS public.ixu_order_log_cust_no;
DROP INDEX IF EXISTS public.ixu_order_log_cust_no_convering;
CREATE INDEX ixu_order_log_cust_no ON public.order_log(cust_no);
ANALYZE public.order_log;
SET optimizer =ON;
EXPLAIN ANALYZE
SELECT sum(order_amt)
FROM public.order_log
WHERE cust_no = 1
AND order_date >= '20010101'
AND order_date < '20020101'
;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize Aggregate (cost=0.00..418.62 rows=1 width=8) (actual time=35.349..35.351 rows=1 loops=1) |
-> Gather Motion 8:1 (slice1; segments: 8) (cost=0.00..418.62 rows=1 width=8) (actual time=29.441..35.328 rows=8 loops=1) |
-> Partial Aggregate (cost=0.00..418.62 rows=1 width=8) (actual time=35.095..35.096 rows=1 loops=1) |
-> Dynamic Bitmap Heap Scan on order_log (cost=0.00..418.62 rows=1273 width=4) (actual time=1.889..28.300 rows=4680 loops=1) |
Number of partitions to scan: 1 (out of 10) |
Recheck Cond: (cust_no = 1) |
Filter: ((cust_no = 1) AND ((order_date)::text >= '20010101'::text) AND ((order_date)::text < '20020101'::text)) |
Heap Blocks: exact=1 |
Partitions scanned: Avg 1.0 x 8 workers. Max 1 parts (seg0). |
-> Dynamic Bitmap Index Scan on ixu_order_log_cust_no (cost=0.00..0.00 rows=0 width=0) (actual time=1.274..1.275 rows=4680 loops=1)|
Index Cond: (cust_no = 1) |
Optimizer: GPORCA |
Planning Time: 20.110 ms |
(slice0) Executor memory: 34K bytes. |
(slice1) Executor memory: 2434K bytes avg x 8 workers, 2434K bytes max (seg0). |
Memory used: 128000kB |
Execution Time: 36.671 ms |
SET optimizer =OFF;
EXPLAIN ANALYZE
SELECT sum(order_amt)
FROM public.order_log
WHERE cust_no = 1
AND order_date >= '20010101'
AND order_date < '20020101'
;
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize Aggregate (cost=590.62..590.63 rows=1 width=8) (actual time=33.021..33.023 rows=1 loops=1) |
-> Gather Motion 8:1 (slice1; segments: 8) (cost=590.50..590.60 rows=8 width=8) (actual time=25.173..32.979 rows=8 loops=1) |
-> Partial Aggregate (cost=590.50..590.51 rows=1 width=8) (actual time=31.201..31.202 rows=1 loops=1) |
-> Bitmap Heap Scan on order_log_1_prt_p2001 (cost=371.02..579.31 rows=4474 width=4) (actual time=0.858..28.146 rows=4680 loops=1) |
Recheck Cond: (cust_no = 1) |
Filter: (((order_date)::text >= '20010101'::text) AND ((order_date)::text < '20020101'::text)) |
Heap Blocks: exact=14 |
-> Bitmap Index Scan on order_log_1_prt_p2001_cust_no_idx (cost=0.00..369.90 rows=4474 width=0) (actual time=0.439..0.439 rows=4680 loops=1)|
Index Cond: (cust_no = 1) |
Optimizer: Postgres-based planner |
Planning Time: 0.295 ms |
(slice0) Executor memory: 69K bytes. |
(slice1) Executor memory: 2430K bytes avg x 8 workers, 2430K bytes max (seg0). |
Memory used: 128000kB |
Execution Time: 34.320 ms
--------###################
DROP INDEX IF EXISTS public.ixu_order_log_cust_no;
DROP INDEX IF EXISTS public.ixu_order_log_cust_no_convering;
CREATE INDEX ixu_order_log_cust_no_convering ON public.order_log(cust_no) INCLUDE(order_amt);
ANALYZE public.order_log;
SET optimizer =ON;
EXPLAIN ANALYZE
SELECT sum(order_amt)
FROM public.order_log
WHERE cust_no = 1
AND order_date >= '20010101'
AND order_date < '20020101'
;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize Aggregate (cost=0.00..415.71 rows=1 width=8) (actual time=48.383..48.386 rows=1 loops=1) |
-> Gather Motion 8:1 (slice1; segments: 8) (cost=0.00..415.71 rows=1 width=8) (actual time=43.469..48.363 rows=8 loops=1) |
-> Partial Aggregate (cost=0.00..415.71 rows=1 width=8) (actual time=47.047..47.049 rows=1 loops=1) |
-> Dynamic Bitmap Heap Scan on order_log (cost=0.00..415.71 rows=1152 width=4) (actual time=2.796..47.208 rows=4680 loops=1) |
Number of partitions to scan: 1 (out of 10) |
Recheck Cond: (cust_no = 1) |
Filter: ((cust_no = 1) AND ((order_date)::text >= '20010101'::text) AND ((order_date)::text < '20020101'::text)) |
Heap Blocks: exact=1 |
Partitions scanned: Avg 1.0 x 8 workers. Max 1 parts (seg0). |
-> Dynamic Bitmap Index Scan on ixu_order_log_cust_no_convering (cost=0.00..0.00 rows=0 width=0) (actual time=2.265..2.266 rows=4680 loops=1)|
Index Cond: (cust_no = 1) |
Optimizer: GPORCA |
Planning Time: 30.877 ms |
(slice0) Executor memory: 34K bytes. |
(slice1) Executor memory: 2434K bytes avg x 8 workers, 2434K bytes max (seg0). |
Memory used: 128000kB |
Execution Time: 49.816 ms
SET optimizer =off;
EXPLAIN ANALYZE
SELECT sum(order_amt)
FROM public.order_log
WHERE cust_no = 1
AND order_date >= '20010101'
AND order_date < '20020101'
;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize Aggregate (cost=547.54..547.55 rows=1 width=8) (actual time=46.434..46.435 rows=1 loops=1) |
-> Gather Motion 8:1 (slice1; segments: 8) (cost=547.42..547.52 rows=8 width=8) (actual time=31.647..46.368 rows=8 loops=1) |
-> Partial Aggregate (cost=547.42..547.43 rows=1 width=8) (actual time=34.170..34.171 rows=1 loops=1) |
-> Bitmap Heap Scan on order_log_1_prt_p2001 (cost=337.43..537.30 rows=4049 width=4) (actual time=0.875..39.432 rows=4680 loops=1) |
Recheck Cond: (cust_no = 1) |
Filter: (((order_date)::text >= '20010101'::text) AND ((order_date)::text < '20020101'::text)) |
Heap Blocks: exact=14 |
-> Bitmap Index Scan on order_log_1_prt_p2001_cust_no_order_amt_idx (cost=0.00..336.42 rows=4049 width=0) (actual time=0.526..0.526 rows=4680 loops=1)|
Index Cond: (cust_no = 1) |
Optimizer: Postgres-based planner |
Planning Time: 0.201 ms |
(slice0) Executor memory: 69K bytes. |
(slice1) Executor memory: 2430K bytes avg x 8 workers, 2430K bytes max (seg0). |
Memory used: 128000kB |
Execution Time: 48.352 ms |
1.BRIN (Block Range Index)
- 블럭 범위의 인덱스로 스캔에 불필요한 block를 skip하기 위한 인덱스
- 블럭의 Min, Max 값을 이용하기 때문에, 인덱스 사이즈가 작은 장점이 있음.
- 다만, 블럭에 여러개의 값이 들어갈 경우 효과가 떨어짐.
- BRIN INDEX 설명: https://www.youtube.com/watch?v=--KCYkWbY6M
2.BRIN INDEX 적용 유형
- 같은 데이터가 같은 블럭에 많이 분포될 경우 유리 -> 시계열 데이터
- 일련 번호가 올라가는 구성 유리
3.예제 설명
- 프로젝트 중 2개의 시계열 컬럼이 있었는데, 2개 컬럼 모두 사용하는 경우
. 이중 하나는 Greenplum에서 분석용도로 파티션 컬럼으로 활용, --order_dt
. 다른 하나 컬럼은 원소스 시스템의 파티션 컬럼, ETL/마이그레이션시 검증용 컬럼 --sys_dt
- TABLE 구성
. 1일 1만건 데이터 적재
. 월 파티션 + zstd7 압
. 파티션 키: order_dt
. BRIN INDEX: sys_dt
4. 테스트 결과
- 파티션 테이블에 인덱스 사용시 optimizer=OFF 할 때 인덱스 스캔 수행
- 테이블은 압축되지만, 인덱스는 압축되지 않음.
- Btree index는 90MB 이지만, Brin index는 1.5MB 정도. 60배 정도 사이즈가 줄어 듬.
- 인덱스 스캔시 btree아 brin간에는 차이가 없음.
4.1 SET optimizer = OFF;
- 인덱스 스캔 수행으로 성능 개선
---------------------------------------------------------------------------------------------------
|FULL scan | 파티션 스캔 | 인덱스 스캔 | 인덱스 | 파티션 사이즈 | 파티션 인덱스 사이즈|
|(sys_dt) | order_dt | sys_dt | 생성시간 | zstd 7 압축 | (비압축) |
----------------------------|----------|---------|-----------|-------|------------|----------------|
NO INDEX | 6.71sec | 0.59sec | | | 6.6MB | |
btree idx | | | 0.10sec |19.7sec| 6.6MB | 90.0MB |
brin idx(default) | | | 0.09sec | 7.9sec| 6.6MB | 1.5MB |
brin idx(pages_per_range=4) | | | 0.08sec | 8.2sec| 6.6MB | 1.5MB |
----------------------------------------------------------------------------------------------------
4.2 SET optimizer = ON;
- 인덱스를 사용하더라도 사용하지 않음.
---------------------------------------------------------------------------------------------------
|FULL scan | 파티션 스캔 | 인덱스 스캔 | 인덱스 | 파티션 사이즈 | 파티션 인덱스 사이즈|
|(sys_dt) | order_dt | sys_dt | 생성시간 | zstd 7 압축 | (비압축) |
----------------------------|----------|---------|-----------|-------|------------|----------------|
NO INDEX | 6.71sec | 0.59sec | | | 6.6MB | |
btree idx | | |미사용(6.8s) |20.0sec| 6.6MB | 90.0MB |
brin idx(default) | | |미사용(6.7s) | 8.3sec| 6.6MB | 1.5MB |
brin idx(pages_per_range=4) | | |미사용(6.9s) | 8.2sec| 6.6MB | 1.5MB |
----------------------------------------------------------------------------------------------------
5. 테스트 스크립트
5.1 테이블 생성 및 적재
DROP TABLE IF EXISTS public.order_log;
CREATE TABLE public.order_log
(
order_no int,
cust_no int,
prod_nm varchar(20),
order_dt varchar(8),
sys_dt varchar(8)
)
WITH (appendonly=TRUE, compresstype=zstd, compresslevel=7)
DISTRIBUTED BY (order_no)
PARTITION BY RANGE (order_dt)
(
PARTITION p200101 start('20010101') END ('20010201'),
PARTITION p200102 start('20010201') END ('20010301'),
PARTITION p200103 start('20010301') END ('20010401'),
PARTITION p200104 start('20010401') END ('20010501'),
PARTITION p200105 start('20010501') END ('20010601'),
PARTITION p200106 start('20010601') END ('20010701'),
PARTITION p200107 start('20010701') END ('20010801'),
PARTITION p200108 start('20010801') END ('20010901'),
PARTITION p200109 start('20010901') END ('20011001'),
PARTITION p200110 start('20011001') END ('20011101'),
PARTITION p200111 start('20011101') END ('20011201'),
PARTITION p200112 start('20011201') END ('20020101')
)
;
--데이터 적재
--1년 데이터 일괄 적재
INSERT INTO public.order_log
SELECT i order_no
, i%100 cust_no
, 'prod_'||trim(to_char(i%50, '00000')) prod_nm
, to_char('2001-01-01'::date + i, 'yyyymmdd') order_dt
, to_char('2001-01-01'::date + i + 1, 'yyyymmdd') sys_dt
FROM generate_series(1, 364) i
, generate_series(1, 100000) j
;
Inserted ROWS: 36400000
/*
--1년 데이터를 1일씩 데이터 적재
--카티션 조인한 것과 조회실행 시간과는 차이가 없었음.
TRUNCATE TABLE public.order_log ;
DO $$
BEGIN
FOR i in 1..364 LOOP
INSERT INTO public.order_log
SELECT i order_no
, i%100 cust_no
, 'prod_'||trim(to_char(i%50, '00000')) prod_nm
, to_char('2001-01-01'::date + i, 'yyyymmdd') order_dt
, to_char('2001-01-01'::date + i + 1, 'yyyymmdd') sys_dt
FROM generate_series(1, 100000) j;
END LOOP;
END $$;
*/
ANALYZE public.order_log;
--######################################################################################
6. 인덱스 종류별 사이트 확인 및 조회 시간 추출 (Optimizer=off)
SET optimizer=off;
--------########## Full 스캔, 파티션 스캔
gpadmin=# SELECT count(*) FROM public.order_log;
count
----------
36400000
(1 row)
Time: 6711.915 ms (00:06.712)
--Full Scan + GPORCA off
SELECT count(*)
FROM public.order_log
WHERE sys_dt >= '20010103'
AND sys_dt <= '20010105';
count
--------
300000
(1 row)
Time: 6824.244 ms (00:06.824)
gpadmin=#
--파티션 Scan + GPORCA off
SELECT count(*)
FROM public.order_log
WHERE order_dt >= '20010103'
AND order_dt <= '20010105';
count
--------
300000
(1 row)
Time: 594.700 ms
gpadmin=#
--------########## Btree Index 스캔 + GPORCA off
CREATE INDEX ixb_order_log_sys_dt ON public.order_log USING btree(sys_dt);
CREATE INDEX
Time: 19695.137 ms (00:19.695)
gpadmin=# \dt+ public.order_log*
List of relations
Schema | Name | Type | Owner | Storage | Size | Description
--------+---------------------------------+-------------------+---------+---------+---------+-------------
public | order_log | partitioned table | gpadmin | ao_row | 0 bytes |
public | order_log_1_prt_p200101 | table | gpadmin | ao_row | 6692 kB |
public | order_log_1_prt_p200102 | table | gpadmin | ao_row | 6328 kB |
public | order_log_1_prt_p200103 | table | gpadmin | ao_row | 6878 kB |
public | order_log_1_prt_p200104 | table | gpadmin | ao_row | 6611 kB |
public | order_log_1_prt_p200105 | table | gpadmin | ao_row | 6896 kB |
public | order_log_1_prt_p200106 | table | gpadmin | ao_row | 6616 kB |
public | order_log_1_prt_p200107 | table | gpadmin | ao_row | 6896 kB |
public | order_log_1_prt_p200108 | table | gpadmin | ao_row | 6897 kB |
public | order_log_1_prt_p200109 | table | gpadmin | ao_row | 6715 kB |
public | order_log_1_prt_p200110 | table | gpadmin | ao_row | 6896 kB |
public | order_log_1_prt_p200111 | table | gpadmin | ao_row | 6711 kB |
public | order_log_1_prt_p200112 | table | gpadmin | ao_row | 6896 kB |
gpadmin=# \di+ order_log_1_prt_p2001*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------------+-------+---------+-------------------------+-------+-------------
public | order_log_1_prt_p200101_sys_dt_idx | index | gpadmin | order_log_1_prt_p200101 | 90 MB |
public | order_log_1_prt_p200102_sys_dt_idx | index | gpadmin | order_log_1_prt_p200102 | 84 MB |
public | order_log_1_prt_p200103_sys_dt_idx | index | gpadmin | order_log_1_prt_p200103 | 93 MB |
public | order_log_1_prt_p200104_sys_dt_idx | index | gpadmin | order_log_1_prt_p200104 | 90 MB |
public | order_log_1_prt_p200105_sys_dt_idx | index | gpadmin | order_log_1_prt_p200105 | 93 MB |
public | order_log_1_prt_p200106_sys_dt_idx | index | gpadmin | order_log_1_prt_p200106 | 90 MB |
public | order_log_1_prt_p200107_sys_dt_idx | index | gpadmin | order_log_1_prt_p200107 | 93 MB |
public | order_log_1_prt_p200108_sys_dt_idx | index | gpadmin | order_log_1_prt_p200108 | 93 MB |
public | order_log_1_prt_p200109_sys_dt_idx | index | gpadmin | order_log_1_prt_p200109 | 90 MB |
public | order_log_1_prt_p200110_sys_dt_idx | index | gpadmin | order_log_1_prt_p200110 | 93 MB |
public | order_log_1_prt_p200111_sys_dt_idx | index | gpadmin | order_log_1_prt_p200111 | 90 MB |
public | order_log_1_prt_p200112_sys_dt_idx | index | gpadmin | order_log_1_prt_p200112 | 93 MB |
(12 rows)
--EXPLAIN ANALYZE
SELECT count(*)
FROM public.order_log
WHERE sys_dt >= '20010103'
AND sys_dt <= '20010105';
count
--------
300000
(1 row)
Time: 104.626 ms
DROP INDEX ixb_order_log_sys_dt;
--------########## BRIN Index 스캔 - Default + GPORCA off
CREATE INDEX ixbrin_order_log_sys_dt ON public.order_log USING brin(sys_dt);
CREATE INDEX
Time: 7936.710 ms (00:07.937)
gpadmin=# \di+ order_log_1_prt_p2001*_sys_dt_idx
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------------+-------+---------+-------------------------+---------+-------------
public | order_log_1_prt_p200101_sys_dt_idx | index | gpadmin | order_log_1_prt_p200101 | 1568 kB |
public | order_log_1_prt_p200102_sys_dt_idx | index | gpadmin | order_log_1_prt_p200102 | 1568 kB |
public | order_log_1_prt_p200103_sys_dt_idx | index | gpadmin | order_log_1_prt_p200103 | 1568 kB |
public | order_log_1_prt_p200104_sys_dt_idx | index | gpadmin | order_log_1_prt_p200104 | 1408 kB |
public | order_log_1_prt_p200105_sys_dt_idx | index | gpadmin | order_log_1_prt_p200105 | 1568 kB |
public | order_log_1_prt_p200106_sys_dt_idx | index | gpadmin | order_log_1_prt_p200106 | 1408 kB |
public | order_log_1_prt_p200107_sys_dt_idx | index | gpadmin | order_log_1_prt_p200107 | 1568 kB |
public | order_log_1_prt_p200108_sys_dt_idx | index | gpadmin | order_log_1_prt_p200108 | 1568 kB |
public | order_log_1_prt_p200109_sys_dt_idx | index | gpadmin | order_log_1_prt_p200109 | 1568 kB |
public | order_log_1_prt_p200110_sys_dt_idx | index | gpadmin | order_log_1_prt_p200110 | 1568 kB |
public | order_log_1_prt_p200111_sys_dt_idx | index | gpadmin | order_log_1_prt_p200111 | 1568 kB |
public | order_log_1_prt_p200112_sys_dt_idx | index | gpadmin | order_log_1_prt_p200112 | 1568 kB |
(12 rows)
--EXPLAIN ANALYZE
SELECT count(*)
FROM public.order_log
WHERE sys_dt >= '20010103'
AND sys_dt <= '20010105';
count
--------
300000
(1 row)
Time: 90.161 ms
gpadmin=#
DROP INDEX ixbrin_order_log_sys_dt;
--------########## BRIN Index 스캔 - pages_per_range=4 + GPORCA off
CREATE INDEX ixbrin_order_log_sys_dt ON public.order_log USING brin(sys_dt) WITH (pages_per_range=4);
CREATE INDEX
Time: 8209.303 ms (00:08.209)
gpadmin=# \di+ order_log_1_prt_p2001*_sys_dt_idx
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------------+-------+---------+-------------------------+---------+-------------
public | order_log_1_prt_p200101_sys_dt_idx | index | gpadmin | order_log_1_prt_p200101 | 1568 kB |
public | order_log_1_prt_p200102_sys_dt_idx | index | gpadmin | order_log_1_prt_p200102 | 1568 kB |
public | order_log_1_prt_p200103_sys_dt_idx | index | gpadmin | order_log_1_prt_p200103 | 1568 kB |
public | order_log_1_prt_p200104_sys_dt_idx | index | gpadmin | order_log_1_prt_p200104 | 1408 kB |
public | order_log_1_prt_p200105_sys_dt_idx | index | gpadmin | order_log_1_prt_p200105 | 1568 kB |
public | order_log_1_prt_p200106_sys_dt_idx | index | gpadmin | order_log_1_prt_p200106 | 1408 kB |
public | order_log_1_prt_p200107_sys_dt_idx | index | gpadmin | order_log_1_prt_p200107 | 1568 kB |
public | order_log_1_prt_p200108_sys_dt_idx | index | gpadmin | order_log_1_prt_p200108 | 1568 kB |
public | order_log_1_prt_p200109_sys_dt_idx | index | gpadmin | order_log_1_prt_p200109 | 1568 kB |
public | order_log_1_prt_p200110_sys_dt_idx | index | gpadmin | order_log_1_prt_p200110 | 1568 kB |
public | order_log_1_prt_p200111_sys_dt_idx | index | gpadmin | order_log_1_prt_p200111 | 1568 kB |
public | order_log_1_prt_p200112_sys_dt_idx | index | gpadmin | order_log_1_prt_p200112 | 1568 kB |
--EXPLAIN ANALYZE
SELECT count(*)
FROM public.order_log
WHERE sys_dt >= '20010103'
AND sys_dt <= '20010105';
count
--------
300000
(1 row)
Time: 82.105 ms
gpadmin=#
drop index ixbrin_order_log_sys_dt;
--######################################################################################
7. 인덱스 종류별 사이트 확인 및 조회 시간 추출 (Optimizer=on)
SET optimizer=on;
--------########## Btree Index 스캔 + GPORCA on
CREATE INDEX ixb_order_log_sys_dt ON public.order_log USING btree(sys_dt);
CREATE INDEX
Time: 19992.654 ms (00:19.993)
gpadmin=# \di+ order_log_1_prt_p2001*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------------+-------+---------+-------------------------+-------+-------------
public | order_log_1_prt_p200101_sys_dt_idx | index | gpadmin | order_log_1_prt_p200101 | 90 MB |
public | order_log_1_prt_p200102_sys_dt_idx | index | gpadmin | order_log_1_prt_p200102 | 84 MB |
public | order_log_1_prt_p200103_sys_dt_idx | index | gpadmin | order_log_1_prt_p200103 | 93 MB |
public | order_log_1_prt_p200104_sys_dt_idx | index | gpadmin | order_log_1_prt_p200104 | 90 MB |
public | order_log_1_prt_p200105_sys_dt_idx | index | gpadmin | order_log_1_prt_p200105 | 93 MB |
public | order_log_1_prt_p200106_sys_dt_idx | index | gpadmin | order_log_1_prt_p200106 | 90 MB |
public | order_log_1_prt_p200107_sys_dt_idx | index | gpadmin | order_log_1_prt_p200107 | 93 MB |
public | order_log_1_prt_p200108_sys_dt_idx | index | gpadmin | order_log_1_prt_p200108 | 93 MB |
public | order_log_1_prt_p200109_sys_dt_idx | index | gpadmin | order_log_1_prt_p200109 | 90 MB |
public | order_log_1_prt_p200110_sys_dt_idx | index | gpadmin | order_log_1_prt_p200110 | 93 MB |
public | order_log_1_prt_p200111_sys_dt_idx | index | gpadmin | order_log_1_prt_p200111 | 90 MB |
public | order_log_1_prt_p200112_sys_dt_idx | index | gpadmin | order_log_1_prt_p200112 | 93 MB |
(12 rows)
--EXPLAIN ANALYZE
SELECT count(*)
FROM public.order_log
WHERE sys_dt >= '20010103'
AND sys_dt <= '20010105';
count
--------
300000
(1 row)
Time: 6832.338 ms (00:06.832)
drop index ixb_order_log_sys_dt;
--------########## BRIN Index 스캔 - Default + GPORCA on
CREATE INDEX ixbrin_order_log_sys_dt ON public.order_log USING brin(sys_dt);
CREATE INDEX
Time: 8308.608 ms (00:08.309)
gpadmin=# \di+ order_log_1_prt_p2001*_sys_dt_idx
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------------+-------+---------+-------------------------+---------+-------------
public | order_log_1_prt_p200101_sys_dt_idx | index | gpadmin | order_log_1_prt_p200101 | 1568 kB |
public | order_log_1_prt_p200102_sys_dt_idx | index | gpadmin | order_log_1_prt_p200102 | 1568 kB |
public | order_log_1_prt_p200103_sys_dt_idx | index | gpadmin | order_log_1_prt_p200103 | 1568 kB |
public | order_log_1_prt_p200104_sys_dt_idx | index | gpadmin | order_log_1_prt_p200104 | 1408 kB |
public | order_log_1_prt_p200105_sys_dt_idx | index | gpadmin | order_log_1_prt_p200105 | 1568 kB |
public | order_log_1_prt_p200106_sys_dt_idx | index | gpadmin | order_log_1_prt_p200106 | 1408 kB |
public | order_log_1_prt_p200107_sys_dt_idx | index | gpadmin | order_log_1_prt_p200107 | 1568 kB |
public | order_log_1_prt_p200108_sys_dt_idx | index | gpadmin | order_log_1_prt_p200108 | 1568 kB |
public | order_log_1_prt_p200109_sys_dt_idx | index | gpadmin | order_log_1_prt_p200109 | 1568 kB |
public | order_log_1_prt_p200110_sys_dt_idx | index | gpadmin | order_log_1_prt_p200110 | 1568 kB |
public | order_log_1_prt_p200111_sys_dt_idx | index | gpadmin | order_log_1_prt_p200111 | 1568 kB |
public | order_log_1_prt_p200112_sys_dt_idx | index | gpadmin | order_log_1_prt_p200112 | 1568 kB |
(12 rows)
--EXPLAIN ANALYZE
SELECT count(*)
FROM public.order_log
WHERE sys_dt >= '20010103'
AND sys_dt <= '20010105';
count
--------
300000
(1 row)
Time: 6715.830 ms (00:06.716)
gpadmin=#
DROP INDEX ixbrin_order_log_sys_dt;
--------########## BRIN Index 스캔 - pages_per_range=4 + GPORCA on
CREATE INDEX ixbrin_order_log_sys_dt ON public.order_log
USING brin(sys_dt) WITH (pages_per_range=4);
CREATE INDEX
Time: 8191.656 ms (00:08.192)
gpadmin=# \di+ order_log_1_prt_p2001*_sys_dt_idx
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------------------------------+-------+---------+-------------------------+---------+-------------
public | order_log_1_prt_p200101_sys_dt_idx | index | gpadmin | order_log_1_prt_p200101 | 1568 kB |
public | order_log_1_prt_p200102_sys_dt_idx | index | gpadmin | order_log_1_prt_p200102 | 1568 kB |
public | order_log_1_prt_p200103_sys_dt_idx | index | gpadmin | order_log_1_prt_p200103 | 1568 kB |
public | order_log_1_prt_p200104_sys_dt_idx | index | gpadmin | order_log_1_prt_p200104 | 1408 kB |
public | order_log_1_prt_p200105_sys_dt_idx | index | gpadmin | order_log_1_prt_p200105 | 1568 kB |
public | order_log_1_prt_p200106_sys_dt_idx | index | gpadmin | order_log_1_prt_p200106 | 1408 kB |
public | order_log_1_prt_p200107_sys_dt_idx | index | gpadmin | order_log_1_prt_p200107 | 1568 kB |
public | order_log_1_prt_p200108_sys_dt_idx | index | gpadmin | order_log_1_prt_p200108 | 1568 kB |
public | order_log_1_prt_p200109_sys_dt_idx | index | gpadmin | order_log_1_prt_p200109 | 1568 kB |
public | order_log_1_prt_p200110_sys_dt_idx | index | gpadmin | order_log_1_prt_p200110 | 1568 kB |
public | order_log_1_prt_p200111_sys_dt_idx | index | gpadmin | order_log_1_prt_p200111 | 1568 kB |
public | order_log_1_prt_p200112_sys_dt_idx | index | gpadmin | order_log_1_prt_p200112 | 1568 kB |
(12 rows)
--EXPLAIN ANALYZE
SELECT count(*)
FROM public.order_log
WHERE sys_dt >= '20010103'
AND sys_dt <= '20010105';
count
--------
300000
(1 row)
Time: 6913.908 ms (00:06.914)
drop index ixbrin_order_log_sys_dt;
Greenplum DR를 사용하면, 재해 발생 전 특정 복구 시점으로 복구 지원 Greenplum DR은 Full 백업/복구, Incremental 백업/복구, WAL 로그 기반으로 DR 기능 제공 Greenplum Disaster Recovery 지...
