728x90
반응형
PostgreSQL Table partition
0. Overview
파티션 테이블 생성 방법 2가지
Inheritance : Trigger나 rule을 이용하여 데이터를 child 테이블에 넣는 방식. 개별 child 테이블에 인덱스를 각각 생성해 줘야 함.
각각의 child 테이블에 직접 vacuum, analyze 명령을 수행해야 한다.
Declarative : 10 부터 지원. Trigger나 rule을 이용하지 않아도 됨.
V10은 Parent 테이블에 인덱스 생성 시 Child 테이블에 인덱스가 생성되지 않아서 기존처럼 개별적으로 child 테이블에 인덱스를 생성해 줘야 함
V11부터는 parent 테이블에 인덱스 생성 시 Child 테이블에 인덱스도 상속되어서 개별 child 테이블에 자동 생성 됨.
~ 10 까지는 Range, List 파티션만 지원 (SET constraint_exclusion = on; 파티션 푸르닝에 영향)
11 ~ Hash 파티션 지원 (SET enable_partition_pruning = on; 파티션 푸르닝에 영향)
V11에서 파티션 테이블을 Inheritance(Trigger), Inheritance(rule) , Declarative로 생성한 후
Insert테스트를 진행해본 결과
Declarative : 45초
Inheritance(rule) : 3분 36초
Inheritance(Trigger) : 1분 6초
1. Partition 점검 PL/pgSQL
2. Inheritance 방식으로 파티션 테이블 생성
트리거나 rule을 이용하여 파티션에 데이터를 적재 함.
ALTER TABLE child 테이블 NO INHERIT Parent 테이블; 명령을 이용하여 detach
2.1 트리거 사용 파티션
테이블 생성
| Parent Table | create table part_test_tri ( ci int , cj int , logdate date ) ; |
| Child Table | create table part_test_tri_202201 ( check( logdate >= date '2022-01-01' and logdate < date '2022-02-01')) inherits (part_test_tri) ; create table part_test_tri_202202 ( check( logdate >= date '2022-02-01' and logdate < date '2022-03-01')) inherits (part_test_tri) ; create table part_test_tri_202203 ( check( logdate >= date '2022-03-01' and logdate < date '2022-04-01')) inherits (part_test_tri) ; create table part_test_tri_202204 ( check( logdate >= date '2022-04-01' and logdate < date '2022-05-01')) inherits (part_test_tri) ; create table part_test_tri_202205 ( check( logdate >= date '2022-05-01' and logdate < date '2022-06-01')) inherits (part_test_tri) ; |
insert function 생성
| create or replace function f_part_test_tri_insert() returns trigger as $$ begin if (NEW.logdate >= date '2022-05-01' and NEW.logdate < date '2022-06-01') then insert into part_test_tri_202205 values (NEW.*) ; elsif ( NEW.logdate >= date '2022-04-01' and NEW.logdate < date '2022-05-01') then insert into part_test_tri_202204 values (NEW.*) ; elsif ( NEW.logdate >= date '2022-03-01' and NEW.logdate < date '2022-04-01') then insert into part_test_tri_202203 values (NEW.*) ; elsif ( NEW.logdate >= date '2022-02-01' and NEW.logdate < date '2022-03-01') then insert into part_test_tri_202202 values (NEW.*) ; elsif ( NEW.logdate >= date '2022-01-01' and NEW.logdate < date '2022-02-01') then insert into part_test_tri_202201 values (NEW.*) ; else raise exception 'Out of range' ; end if ; return null ; end ; $$ language plpgsql ; |
insert trigger 생성
| create trigger t_part_test_tri_insert before insert on part_test_tri for each row execute procedure f_part_test_tri_insert() ; |
테이블 생성 확인
| \d+ part_test_tri Table "public.part_test_tri" +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | Column | Type | Collation | Nullable | Default | Storage | Stats target | Description | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | ci | integer | | | | plain | | | | cj | integer | | | | plain | | | | logdate | date | | | | plain | | | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ Triggers: t_part_test_tri_insert BEFORE INSERT ON part_test_tri FOR EACH ROW EXECUTE PROCEDURE f_part_test_tri_insert() Child tables: part_test_tri_202201, part_test_tri_202202, part_test_tri_202203, part_test_tri_202204, part_test_tri_202205 |
데이터 Insert
| do $$ begin for i in 1..150 loop for j in 1..10000 loop insert into part_test_tri values ( i , j , to_date('20211231','YYYYMMDD')+i ); end loop; end loop; end $$; |
인덱스 추가
| create index part_test_tri_202201_idx on part_test_tri_202201(ci) ; create index part_test_tri_202202_idx on part_test_tri_202202(ci) ; create index part_test_tri_202203_idx on part_test_tri_202203(ci) ; create index part_test_tri_202204_idx on part_test_tri_202204(ci) ; create index part_test_tri_202205_idx on part_test_tri_202205(ci) ; |
2.2 Rule 사용 파티션
테이블 생성
| Parent Table | create table part_test_rule ( ci int , cj int , logdate date ) ; |
| Child Table | create table part_test_rule_202201 ( check( logdate >= date '2022-01-01' and logdate < date '2022-02-01')) inherits (part_test_rule) ; create table part_test_rule_202202 ( check( logdate >= date '2022-02-01' and logdate < date '2022-03-01')) inherits (part_test_rule) ; create table part_test_rule_202203 ( check( logdate >= date '2022-03-01' and logdate < date '2022-04-01')) inherits (part_test_rule) ; create table part_test_rule_202204 ( check( logdate >= date '2022-04-01' and logdate < date '2022-05-01')) inherits (part_test_rule) ; create table part_test_rule_202205 ( check( logdate >= date '2022-05-01' and logdate < date '2022-06-01')) inherits (part_test_rule) ; |
룰 생성
| CREATE RULE r_part_test_rule_202201 AS ON INSERT TO part_test_rule WHERE ( logdate >= date '2022-01-01' and logdate < date '2022-02-01' ) DO INSTEAD insert into part_test_rule_202201 values (NEW.*); CREATE RULE r_part_test_rule_202202 AS ON INSERT TO part_test_rule WHERE ( logdate >= date '2022-02-01' and logdate < date '2022-03-01' ) DO INSTEAD insert into part_test_rule_202202 values (NEW.*); CREATE RULE r_part_test_rule_202203 AS ON INSERT TO part_test_rule WHERE ( logdate >= date '2022-03-01' and logdate < date '2022-04-01' ) DO INSTEAD insert into part_test_rule_202203 values (NEW.*); CREATE RULE r_part_test_rule_202204 AS ON INSERT TO part_test_rule WHERE ( logdate >= date '2022-04-01' and logdate < date '2022-05-01' ) DO INSTEAD insert into part_test_rule_202204 values (NEW.*); CREATE RULE r_part_test_rule_202205 AS ON INSERT TO part_test_rule WHERE ( logdate >= date '2022-05-01' and logdate < date '2022-06-01' ) DO INSTEAD insert into part_test_rule_202205 values (NEW.*); |
테이블 생성 확인
| \d+ part_test_rule Table "public.part_test_rule" +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | Column | Type | Collation | Nullable | Default | Storage | Stats target | Description | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | ci | integer | | | | plain | | | | cj | integer | | | | plain | | | | logdate | date | | | | plain | | | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ Rules: r_part_test_rule_202201 AS ON INSERT TO part_test_rule WHERE new.logdate >= '2022-01-01'::date AND new.logdate < '2022-02-01'::date DO INSTEAD INSERT INTO part_test_rule_202201 (ci, cj, logdate) VALUES (new.ci, new.cj, new.logdate) r_part_test_rule_202202 AS ON INSERT TO part_test_rule WHERE new.logdate >= '2022-02-01'::date AND new.logdate < '2022-03-01'::date DO INSTEAD INSERT INTO part_test_rule_202202 (ci, cj, logdate) VALUES (new.ci, new.cj, new.logdate) r_part_test_rule_202203 AS ON INSERT TO part_test_rule WHERE new.logdate >= '2022-03-01'::date AND new.logdate < '2022-04-01'::date DO INSTEAD INSERT INTO part_test_rule_202203 (ci, cj, logdate) VALUES (new.ci, new.cj, new.logdate) r_part_test_rule_202204 AS ON INSERT TO part_test_rule WHERE new.logdate >= '2022-04-01'::date AND new.logdate < '2022-05-01'::date DO INSTEAD INSERT INTO part_test_rule_202204 (ci, cj, logdate) VALUES (new.ci, new.cj, new.logdate) r_part_test_rule_202205 AS ON INSERT TO part_test_rule WHERE new.logdate >= '2022-05-01'::date AND new.logdate < '2022-06-01'::date DO INSTEAD INSERT INTO part_test_rule_202205 (ci, cj, logdate) VALUES (new.ci, new.cj, new.logdate) Child tables: part_test_rule_202201, part_test_rule_202202, part_test_rule_202203, part_test_rule_202204, part_test_rule_202205 |
데이터 insert
| do $$ begin for i in 1..150 loop for j in 1..10000 loop insert into part_test_rule values ( i , j , to_date('20211231','YYYYMMDD')+i ); end loop; end loop; end $$; |
인덱스 생성
| create index part_test_rule_202201_idx on part_test_rule_202201(ci) ; create index part_test_rule_202202_idx on part_test_rule_202202(ci) ; create index part_test_rule_202203_idx on part_test_rule_202203(ci) ; create index part_test_rule_202204_idx on part_test_rule_202204(ci) ; create index part_test_rule_202205_idx on part_test_rule_202205(ci) ; |
2.3 Detach, Attach
테이블 및 인덱스 확인
| select relname , relpages, reltuples from pg_class where relname like 'part_test_tri%' ; +--------------------------+----------+-----------+ | relname | relpages | reltuples | +--------------------------+----------+-----------+ | part_test_tri_202201_idx | 852 | 310000 | | part_test_tri_202202_idx | 771 | 280000 | | part_test_tri_202203_idx | 852 | 310000 | | part_test_tri_202204_idx | 825 | 300000 | | part_test_tri_202205_idx | 825 | 300000 | | part_test_tri_202201 | 1676 | 310000 | | part_test_tri_202202 | 1514 | 280000 | | part_test_tri_202203 | 1676 | 310000 | | part_test_tri_202204 | 1622 | 300000 | | part_test_tri_202205 | 1622 | 300000 | | part_test_tri | 0 | 0 | +--------------------------+----------+-----------+ select count(*) from part_test_tri ; +---------+ | count | +---------+ | 1500000 | +---------+ |
select relname , relpages, reltuples from pg_class where relname like 'part_test_rule%' ; +---------------------------+----------+-----------+ | relname | relpages | reltuples | +---------------------------+----------+-----------+ | part_test_rule_202202_idx | 771 | 280000 | | part_test_rule_202203_idx | 852 | 310000 | | part_test_rule_202201_idx | 852 | 310000 | | part_test_rule_202204_idx | 825 | 300000 | | part_test_rule_202205_idx | 825 | 300000 | | part_test_rule_202201 | 1676 | 310000 | | part_test_rule_202202 | 1514 | 280000 | | part_test_rule_202203 | 1676 | 310000 | | part_test_rule_202204 | 1622 | 300000 | | part_test_rule_202205 | 1622 | 300000 | | part_test_rule | 0 | 0 | +---------------------------+----------+-----------+ select count(*) from part_test_rule ; +---------+ | count | +---------+ | 1500000 | +---------+ |
2.3.1 Detach
| ALTER TABLE part_test_tri_202205 NO INHERIT part_test_tri ; select count(*) from part_test_rule ; +---------+ | count | +---------+ | 1200000 | +---------+ select count(*) from part_test_tri_202205 ; +--------+ | count | +--------+ | 300000 | +--------+ /d+ part_test_tri Table "public.part_test_tri" +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | Column | Type | Collation | Nullable | Default | Storage | Stats target | Description | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | ci | integer | | | | plain | | | | cj | integer | | | | plain | | | | logdate | date | | | | plain | | | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ Triggers: 생략 ... Child tables: part_test_tri_202201, part_test_tri_202202, part_test_tri_202203, part_test_tri_202204 |
ALTER TABLE part_test_rule_202205 NO INHERIT part_test_rule ; select count(*) from part_test_rule ; +---------+ | count | +---------+ | 1200000 | +---------+ select count(*) from part_test_rule_202205 ; +--------+ | count | +--------+ | 300000 | +--------+ /d+ part_test_rule Table "public.part_test_rule" +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | Column | Type | Collation | Nullable | Default | Storage | Stats target | Description | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | ci | integer | | | | plain | | | | cj | integer | | | | plain | | | | logdate | date | | | | plain | | | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ Rules: 생략.. Child tables: part_test_rule_202201, part_test_rule_202202, part_test_rule_202203, part_test_rule_202204 |
2.3.2 Attach
| ALTER TABLE part_test_tri_202205 INHERIT part_test_tri ; select count(*) from part_test_tri ; +---------+ | count | +---------+ | 1500000 | +---------+ |
ALTER TABLE part_test_rule_202205 INHERIT part_test_rule ; select count(*) from part_test_rule ; +---------+ | count | +---------+ | 1500000 | +---------+ |
2.3.3 Parent Table 삭제
Child 테이블까지 모두 삭제 할 경우, Parent 테이블을 삭제 할 때 Cascade를 사용해야 한다.
개별 Child 테이블은 Attach 후 drop table 명령으로 삭제
3. Declarative 방식으로 파티션 테이블 생성(V10부터 지원)
트리거나 Rule을 사용하지 않아도 됨.
ALTER TABLE 메인테이블 DETACH PARTITION 파티션 테이블; -- detach
V11 부터는 Parent Table에 인덱스를 생성하게 되면, 자동으로 Child 테이블에도 인덱스가 생성되게 된다.
그래서 기존 V10까지는 pg_inherits테이블에 파티션 테이블에 대한 정보만 저장되었으나, V11부터는 인덱스에 대한 정보도 추가 되게 된다.
테이블 생성
| Parent Table | create table part_test ( ci int , cj int , logdate date ) partition by range (logdate) ; |
| Child Table | create table part_test_202201 partition of part_test for values from ('2022-01-01') to ('2022-02-01') ; create table part_test_202202 partition of part_test for values from ('2022-02-01') to ('2022-03-01') ; create table part_test_202203 partition of part_test for values from ('2022-03-01') to ('2022-04-01'); create table part_test_202204 partition of part_test for values from ('2022-04-01') to ('2022-05-01') ; create table part_test_202205 partition of part_test for values from ('2022-05-01') to ('2022-06-01') ; |
테이블 생성 정보 확인
| \d+ part_test Table "public.part_test" +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | Column | Type | Collation | Nullable | Default | Storage | Stats target | Description | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ | ci | integer | | | | plain | | | | cj | integer | | | | plain | | | | logdate | date | | | | plain | | | +---------+---------+-----------+----------+---------+---------+--------------+-------------+ Partition key: RANGE (logdate) Partitions: part_test_202201 FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'), part_test_202202 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), part_test_202203 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'), part_test_202204 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'), part_test_202205 FOR VALUES FROM ('2022-05-01') TO ('2022-06-01') |
데이터 Insert
| do $$ begin for i in 1..150 loop for j in 1..10000 loop insert into part_test values ( i , j , to_date('20211231','YYYYMMDD')+i ); end loop; end loop; end $$; |
인덱스 생성
| V10 | create index part_test_202201_idx on part_test_202201(ci) ; create index part_test_202202_idx on part_test_202202(ci) ; create index part_test_202203_idx on part_test_202203(ci) ; create index part_test_202204_idx on part_test_202204(ci) ; create index part_test_202205_idx on part_test_202205(ci) ; |
| V11 ~ | create index part_test_idx on part_test(ci) ; |
select * from pg_inherits ;
+----------+-----------+----------+
| inhrelid | inhparent | inhseqno |
+----------+-----------+----------+
| 16395 | 16392 | 1 |
| 16398 | 16392 | 1 |
| 16401 | 16392 | 1 |
| 16404 | 16392 | 1 |
| 16407 | 16392 | 1 |
+----------+-----------+----------+
4. Partition Pruning
테스트에 사용할 테이블들 정보 확인
위에서 사용한 PL/pgSQL을 이용하여 정보 확인 가능
테스트에는 V11 서버를 사용함.
테스트 결과 요약 :
Declarative를 이용할 경우, date 또는 to_date 모두 파티션 푸르닝은 되나, 속도 차이가 있으므로 date를 사용하는 것이 좋아 보인다.
Inheritance를 이용할 경우, date를 이용하여 파티션을 나누고, 조건에 date를 이용해야지만 파티션 푸르닝이 된다. 전체 child 스캔이 발생해도 date를 사용하는 것이 성능적으로 좋다.
| NOTICE: part_test_tri (inherits with trigger) NOTICE: | NOTICE: \-- part_test_tri_202201 : T/I size ( 13 MB / 6816 kB ) pages ( 1676 ) rows ( 310000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_tri_202201_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202201_idx ON public.part_test_tri_202201 USING btree (ci) NOTICE: | NOTICE: \-- part_test_tri_202202 : T/I size ( 12 MB / 6168 kB ) pages ( 1514 ) rows ( 280000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_tri_202202_idx : size ( 6168 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202202_idx ON public.part_test_tri_202202 USING btree (ci) NOTICE: | NOTICE: \-- part_test_tri_202203 : T/I size ( 13 MB / 6816 kB ) pages ( 1676 ) rows ( 310000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_tri_202203_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202203_idx ON public.part_test_tri_202203 USING btree (ci) NOTICE: | NOTICE: \-- part_test_tri_202204 : T/I size ( 13 MB / 6600 kB ) pages ( 1622 ) rows ( 300000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_tri_202204_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202204_idx ON public.part_test_tri_202204 USING btree (ci) NOTICE: | NOTICE: \-- part_test_tri_202205 : T/I size ( 13 MB / 6600 kB ) pages ( 1622 ) rows ( 300000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_tri_202205_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202205_idx ON public.part_test_tri_202205 USING btree (ci) NOTICE: NOTICE: part_test_rule (inherits with rule) NOTICE: | NOTICE: \-- part_test_rule_202201 : T/I size ( 13 MB / 6816 kB ) pages ( 1676 ) rows ( 310000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_rule_202201_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_rule_202201_idx ON public.part_test_rule_202201 USING btree (ci) NOTICE: | NOTICE: \-- part_test_rule_202202 : T/I size ( 12 MB / 6168 kB ) pages ( 1514 ) rows ( 280000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_rule_202202_idx : size ( 6168 kB ) idx_scan ( 0 ) CREATE INDEX part_test_rule_202202_idx ON public.part_test_rule_202202 USING btree (ci) NOTICE: | NOTICE: \-- part_test_rule_202203 : T/I size ( 13 MB / 6816 kB ) pages ( 1676 ) rows ( 310000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_rule_202203_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_rule_202203_idx ON public.part_test_rule_202203 USING btree (ci) NOTICE: | NOTICE: \-- part_test_rule_202204 : T/I size ( 13 MB / 6600 kB ) pages ( 1622 ) rows ( 300000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_rule_202204_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_rule_202204_idx ON public.part_test_rule_202204 USING btree (ci) NOTICE: | NOTICE: \-- part_test_rule_202205 : T/I size ( 13 MB / 6600 kB ) pages ( 1622 ) rows ( 300000 ) indexes ( 1 ) NOTICE: | NOTICE: \-- part_test_rule_202205_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_rule_202205_idx ON public.part_test_rule_202205 USING btree (ci) NOTICE: NOTICE: part_test (Declarative) NOTICE: | NOTICE: \-- part_test_202201 : T/I size ( 13 MB / 13 MB ) pages ( 1676 ) rows ( 310000 ) indexes ( 2 ) NOTICE: | NOTICE: \-- part_test_202201_ci_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202201_ci_idx ON public.part_test_202201 USING btree (ci) NOTICE: | NOTICE: \-- part_test_202202 : T/I size ( 12 MB / 12 MB ) pages ( 1514 ) rows ( 280000 ) indexes ( 2 ) NOTICE: | NOTICE: \-- part_test_202202_ci_idx : size ( 6168 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202202_ci_idx ON public.part_test_202202 USING btree (ci) NOTICE: | NOTICE: \-- part_test_202203 : T/I size ( 13 MB / 13 MB ) pages ( 1676 ) rows ( 310000 ) indexes ( 2 ) NOTICE: | NOTICE: \-- part_test_202203_ci_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202203_ci_idx ON public.part_test_202203 USING btree (ci) NOTICE: | NOTICE: \-- part_test_202204 : T/I size ( 13 MB / 13 MB ) pages ( 1622 ) rows ( 300000 ) indexes ( 2 ) NOTICE: | NOTICE: \-- part_test_202204_ci_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202204_ci_idx ON public.part_test_202204 USING btree (ci) NOTICE: | NOTICE: \-- part_test_202205 : T/I size ( 13 MB / 13 MB ) pages ( 1622 ) rows ( 300000 ) indexes ( 2 ) NOTICE: | NOTICE: \-- part_test_202205_ci_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202205_ci_idx ON public.part_test_202205 USING btree (ci) |
4.1 Declarative
3월달의 특정 기간의 데이터를 조회 하는데 전체 child 테이블을 액세스 하는지, 원하는 대로 특정 child 테이블을 조회 하는지 테스트 진행
조회 기간은 3월 5일부터 3월 20일까지의 데이터를 조회 하는데, to_date와 date를 이용하여 조회할 진행 함
파라미터 ON
| set enable_partition_pruning = on ; |
플랜 확인
| explain select count(*) from part_test where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27258.44..27258.45 rows=1 width=8) | | -> Gather (cost=27258.22..27258.43 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26258.22..26258.23 rows=1 width=8) | | -> Parallel Append (cost=0.00..26091.17 rows=66822 width=0) | | Subplans Removed: 4 | | -> Parallel Seq Scan on part_test_202203 (cost=0.00..5323.06 rows=94331 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ explain select count(*) from part_test where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27258.44..27258.45 rows=1 width=8) | | -> Gather (cost=27258.22..27258.43 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26258.22..26258.23 rows=1 width=8) | | -> Parallel Append (cost=0.00..26091.17 rows=66822 width=0) | | Subplans Removed: 4 | | -> Parallel Seq Scan on part_test_202203 (cost=0.00..5323.06 rows=94331 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ explain select count(*) from part_test where logdate between date '2022-03-05' and date '2022-03-20' ; +---------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +---------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=6118.89..6118.90 rows=1 width=8) | | -> Gather (cost=6118.78..6118.89 rows=1 width=8) | | Workers Planned: 1 | | -> Partial Aggregate (cost=5118.78..5118.79 rows=1 width=8) | | -> Parallel Append (cost=0.00..4882.95 rows=94331 width=0) | | -> Parallel Seq Scan on part_test_202203 (cost=0.00..4411.29 rows=94331 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | +---------------------------------------------------------------------------------------------------------+ 파티션 푸르닝은 다 되는데 코스트 값이 다 다르다. to_date를 사용할 경우는 둘 다 코스트가 27258.45로 나오는데, date를 사용할 경우 코스트가 6118이 나온다. 실제 쿼리를 수행해 보자 (쿼리는 아래의 순서대로 수행) select count(*) from part_test where logdate between date '2022-03-05' and date '2022-03-20' ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 116.982 ms select count(*) from part_test where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 443.255 ms select count(*) from part_test where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 536.942 ms 실제 쿼리를 수행해 보니 date를 이용한 경우가 대충 4배 정도 더 빠르다. |
파라미터 OFF
| set enable_partition_pruning = off ; |
플랜 확인
| explain select count(*) from part_test where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27258.44..27258.45 rows=1 width=8) | | -> Gather (cost=27258.22..27258.43 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26258.22..26258.23 rows=1 width=8) | | -> Parallel Append (cost=0.00..26091.17 rows=66822 width=0) | | -> Parallel Seq Scan on part_test_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_202203 (cost=0.00..5323.06 rows=94331 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_202205 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ explain select count(*) from part_test where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27258.44..27258.45 rows=1 width=8) | | -> Gather (cost=27258.22..27258.43 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26258.22..26258.23 rows=1 width=8) | | -> Parallel Append (cost=0.00..26091.17 rows=66822 width=0) | | -> Parallel Seq Scan on part_test_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_202203 (cost=0.00..5323.06 rows=94331 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_202205 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ explain select count(*) from part_test where logdate between date '2022-03-05' and date '2022-03-20' ; +---------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +---------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=22846.67..22846.68 rows=1 width=8) | | -> Gather (cost=22846.46..22846.67 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=21846.46..21846.47 rows=1 width=8) | | -> Parallel Append (cost=0.00..21679.40 rows=66822 width=0) | | -> Parallel Seq Scan on part_test_202201 (cost=0.00..4411.29 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_202203 (cost=0.00..4411.29 rows=94331 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_202204 (cost=0.00..4269.06 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_202205 (cost=0.00..4269.06 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_202202 (cost=0.00..3984.59 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | +---------------------------------------------------------------------------------------------------------+ 실제 쿼리를 수행해 보자 (쿼리는 아래의 순서대로 수행) select count(*) from part_test where logdate between date '2022-03-05' and date '2022-03-20' ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 355.319 ms select count(*) from part_test where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 1927.607 ms (00:01.928) select count(*) from part_test where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 2217.092 ms (00:02.217) 역시 전체 Child 테이블을 조회 하는데도 date를 사용하는 속도가 현저히 빠르다. |
4.2 Inheritance(rule/trigger)
파라미터 ON
| set enable_partition_pruning = on ; |
플랜 확인(rule)
| explain select count(*) from part_test_rule where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27256.53..27256.54 rows=1 width=8) | | -> Gather (cost=27256.32..27256.53 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26256.32..26256.33 rows=1 width=8) | | -> Parallel Append (cost=0.00..26089.90 rows=66568 width=0) | | -> Parallel Seq Scan on part_test_rule_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule_202203 (cost=0.00..5323.06 rows=93972 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule_202205 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ explain select count(*) from part_test_rule where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27256.53..27256.54 rows=1 width=8) | | -> Gather (cost=27256.32..27256.53 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26256.32..26256.33 rows=1 width=8) | | -> Parallel Append (cost=0.00..26089.90 rows=66568 width=0) | | -> Parallel Seq Scan on part_test_rule_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule_202203 (cost=0.00..5323.06 rows=93972 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule_202205 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ explain select count(*) from part_test_rule where logdate between date '2022-03-05' and date '2022-03-20' ; +-------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=5910.74..5910.75 rows=1 width=8) | | -> Gather (cost=5910.53..5910.74 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=4910.53..4910.54 rows=1 width=8) | | -> Parallel Append (cost=0.00..4744.11 rows=66565 width=0) | | -> Seq Scan on part_test_rule (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_rule_202203 (cost=0.00..4411.29 rows=93972 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | +-------------------------------------------------------------------------------------------------------------+ 위의 결과를 확인해 보면 date를 사용하는 경우만 파티션 푸르닝이 되고 나머지는 전체 파티션에 대한 스캔이 일어나는 것을 확인 할 수 있다. 이유는 rule이나 trigger는 파티션 데이터를 넣는 부분(insert function or rule)에 logdate >= date '2022-02-01' and logdate < date '2022-03-01' 처럼 데이터 조건을 체크하는 부분이 있는데, 이 부분과 동일하게 where절을 사용하지 않을 경우 푸르닝이 되지 않는다. 실제 쿼리를 수행해 보자 select count(*) from part_test_rule where logdate between date '2022-03-05' and date '2022-03-20' ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 138.249 ms select count(*) from part_test_rule where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 2068.087 ms (00:02.068) select count(*) from part_test_rule where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 2198.934 ms (00:02.199) |
플랜확인 (trigger)
| explain select count(*) from part_test_tri where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27256.53..27256.54 rows=1 width=8) | | -> Gather (cost=27256.32..27256.53 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26256.32..26256.33 rows=1 width=8) | | -> Parallel Append (cost=0.00..26089.90 rows=66568 width=0) | | -> Parallel Seq Scan on part_test_tri_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri_202203 (cost=0.00..5323.06 rows=93972 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri_202205 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_tri where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 2143.009 ms (00:02.143) explain select count(*) from part_test_tri where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27256.53..27256.54 rows=1 width=8) | | -> Gather (cost=27256.32..27256.53 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26256.32..26256.33 rows=1 width=8) | | -> Parallel Append (cost=0.00..26089.90 rows=66568 width=0) | | -> Parallel Seq Scan on part_test_tri_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri_202203 (cost=0.00..5323.06 rows=93972 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri_202205 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_tri where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 2014.080 ms (00:02.014) explain select count(*) from part_test_tri where logdate between date '2022-03-05' and date '2022-03-20' ; +------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=5910.74..5910.75 rows=1 width=8) | | -> Gather (cost=5910.53..5910.74 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=4910.53..4910.54 rows=1 width=8) | | -> Parallel Append (cost=0.00..4744.11 rows=66565 width=0) | | -> Seq Scan on part_test_tri (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_tri_202203 (cost=0.00..4411.29 rows=93972 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | +------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_tri where logdate between date '2022-03-05' and date '2022-03-20' ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 87.649 ms 역시나 date를 사용해야지만 파티션 푸르닝이 된다. |
4.3 Inheritance(rule/trigger) -- date를 사용하지 않고 to_date를 사용하게 생성 후 비교 테스트
date를 사용하지 않고 to_date를 사용하는 방식으로 테이블(part_test_rule2)을 생성 후 푸르닝 테스트를 진행해 보자
조건에 date가 아니고 to_date를 주었을 경우 파티션 푸르닝이 되는지 확인 용
결과 요약 :
to_date를 이용하여 데이터를 분리 시키도록 하였으나, 조회 조건에 to_date를 사용할 경우 파티션 푸르닝이 되질 않음. (조건에 date를 준 것이 속도도 빠르다)
tigger나 rule을 이용할 경우 date로 데이터를 분리 시키고, 조회 조건에 date를 사용하는 것이 성능에 효과적이다.
to_date를 사용하는 방식으로 part_test_rule2 파티션 테이블 생성
| create table part_test_rule2 ( ci int , cj int , logdate date ) ; -- child table create table part_test_rule2_202201 ( check( logdate >= to_date ('2022-01-01','YYYY-MM-DD') and logdate < to_date('2022-02-01','YYYY-MM-DD'))) inherits (part_test_rule2) ; create table part_test_rule2_202202 ( check( logdate >= to_date ( '2022-02-01','YYYY-MM-DD') and logdate < to_date ( '2022-03-01','YYYY-MM-DD'))) inherits (part_test_rule2) ; create table part_test_rule2_202203 ( check( logdate >= to_date ( '2022-03-01','YYYY-MM-DD') and logdate < to_date ( '2022-04-01','YYYY-MM-DD'))) inherits (part_test_rule2) ; create table part_test_rule2_202204 ( check( logdate >= to_date ( '2022-04-01','YYYY-MM-DD') and logdate < to_date ( '2022-05-01','YYYY-MM-DD'))) inherits (part_test_rule2) ; create table part_test_rule2_202205 ( check( logdate >= to_date ( '2022-05-01','YYYY-MM-DD') and logdate < to_date ( '2022-06-01','YYYY-MM-DD'))) inherits (part_test_rule2) ; CREATE RULE r_part_test_rule2_202201 AS ON INSERT TO part_test_rule2 WHERE ( logdate >= to_date ('2022-01-01','YYYY-MM-DD') and logdate < to_date ('2022-02-01','YYYY-MM-DD') ) DO INSTEAD insert into part_test_rule2_202201 values (NEW.*); CREATE RULE r_part_test_rule2_202202 AS ON INSERT TO part_test_rule2 WHERE ( logdate >= to_date ('2022-02-01','YYYY-MM-DD') and logdate < to_date ('2022-03-01','YYYY-MM-DD') ) DO INSTEAD insert into part_test_rule2_202202 values (NEW.*); CREATE RULE r_part_test_rule2_202203 AS ON INSERT TO part_test_rule2 WHERE ( logdate >= to_date ('2022-03-01','YYYY-MM-DD') and logdate < to_date ('2022-04-01','YYYY-MM-DD') ) DO INSTEAD insert into part_test_rule2_202203 values (NEW.*); CREATE RULE r_part_test_rule2_202204 AS ON INSERT TO part_test_rule2 WHERE ( logdate >= to_date ('2022-04-01','YYYY-MM-DD') and logdate < to_date ('2022-05-01','YYYY-MM-DD') ) DO INSTEAD insert into part_test_rule2_202204 values (NEW.*); CREATE RULE r_part_test_rule2_202205 AS ON INSERT TO part_test_rule WHERE ( logdate >= to_date( '2022-05-01','YYYY-MM-DD') and logdate < to_date ('2022-06-01','YYYY-MM-DD') ) DO INSTEAD insert into part_test_rule2_202205 values (NEW.*); do $$ begin for i in 1..150 loop for j in 1..10000 loop insert into part_test_rule2 values ( i , j , to_date('20211231','YYYYMMDD')+i ); end loop; end loop; end $$; |
플랜을 확인해 보자
| explain select count(*) from part_test_rule2 where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27290.56..27290.57 rows=1 width=8) | | -> Gather (cost=27290.34..27290.55 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26290.34..26290.35 rows=1 width=8) | | -> Parallel Append (cost=0.00..26123.91 rows=66571 width=0) | | -> Parallel Seq Scan on part_test_rule2_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202203 (cost=0.00..5323.06 rows=93972 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202205 (cost=0.00..34.00 rows=6 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_rule2 where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 2193.900 ms (00:02.194) explain select count(*) from part_test_rule2 where logdate >= to_date ('2022-03-05','YYYY-MM-DD') and logdate < to_date ('2022-03-20','YYYY-MM-DD') ; +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27259.63..27259.64 rows=1 width=8) | | -> Gather (cost=27259.41..27259.62 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26259.41..26259.42 rows=1 width=8) | | -> Parallel Append (cost=0.00..26103.29 rows=62447 width=0) | | -> Parallel Seq Scan on part_test_rule2_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202203 (cost=0.00..5323.06 rows=88149 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202205 (cost=0.00..34.00 rows=6 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_rule2 where logdate >= to_date ('2022-03-05','YYYY-MM-DD') and logdate < to_date ('2022-03-20','YYYY-MM-DD') ; +--------+ | count | +--------+ | 150000 | +--------+ (1 row) Time: 2244.484 ms (00:02.244) explain select count(*) from part_test_rule2 where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27290.56..27290.57 rows=1 width=8) | | -> Gather (cost=27290.34..27290.55 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26290.34..26290.35 rows=1 width=8) | | -> Parallel Append (cost=0.00..26123.91 rows=66571 width=0) | | -> Parallel Seq Scan on part_test_rule2_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202203 (cost=0.00..5323.06 rows=93972 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule2 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_rule2_202205 (cost=0.00..34.00 rows=6 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_rule2 where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 2034.739 ms (00:02.035) explain select count(*) from part_test_rule2 where logdate between date '2022-03-05' and date '2022-03-20' ; +--------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=22872.79..22872.80 rows=1 width=8) | | -> Gather (cost=22872.58..22872.79 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=21872.58..21872.59 rows=1 width=8) | | -> Parallel Append (cost=0.00..21706.15 rows=66571 width=0) | | -> Parallel Seq Scan on part_test_rule2_202201 (cost=0.00..4411.29 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_rule2_202203 (cost=0.00..4411.29 rows=93972 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_rule2 (cost=0.00..4269.06 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_rule2_202204 (cost=0.00..4269.06 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_rule2_202202 (cost=0.00..3984.59 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_rule2_202205 (cost=0.00..28.00 rows=6 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | +--------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_rule2 where logdate between date '2022-03-05' and date '2022-03-20' ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 411.314 ms 모두 Child 테이블에 대한 Full Scan이 수행되고, 파티션 푸르닝이 되질 않지만 date를 사용할 경우 속도 차이가 거의 5배 정도 난다. rule 방식일 때만 파티션 푸르닝이 되질 않는 것인지 트리거 방식으로 만들어서 같이 테스트 진행 |
to_date를 사용하는 방식으로 part_test_tri2 파티션 테이블 생성
| -- Parent table create table part_test_tri2 ( ci int , cj int , logdate date ) ; -- child table create table part_test_tri2_202201 ( check( logdate >= to_date('2022-01-01','YYYY-MM-DD') and logdate < to_date('2022-02-01','YYYY-MM-DD'))) inherits (part_test_tri2) ; create table part_test_tri2_202202 ( check( logdate >= to_date('2022-02-01','YYYY-MM-DD') and logdate < to_date('2022-03-01','YYYY-MM-DD'))) inherits (part_test_tri2) ; create table part_test_tri2_202203 ( check( logdate >= to_date('2022-03-01','YYYY-MM-DD') and logdate < to_date('2022-04-01','YYYY-MM-DD'))) inherits (part_test_tri2) ; create table part_test_tri2_202204 ( check( logdate >= to_date('2022-04-01','YYYY-MM-DD') and logdate < to_date('2022-05-01','YYYY-MM-DD'))) inherits (part_test_tri2) ; create table part_test_tri2_202205 ( check( logdate >= to_date('2022-05-01','YYYY-MM-DD') and logdate < to_date('2022-06-01','YYYY-MM-DD'))) inherits (part_test_tri2) ; -- insert function create or replace function f_part_test_tri2_insert() returns trigger as $$ begin if (NEW.logdate >= to_date('2022-05-01','YYYY-MM-DD') and NEW.logdate < to_date('2022-06-01','YYYY-MM-DD')) then insert into part_test_tri2_202205 values (NEW.*) ; elsif ( NEW.logdate >= to_date('2022-04-01','YYYY-MM-DD') and NEW.logdate < to_date('2022-05-01','YYYY-MM-DD')) then insert into part_test_tri2_202204 values (NEW.*) ; elsif ( NEW.logdate >= to_date('2022-03-01','YYYY-MM-DD') and NEW.logdate < to_date('2022-04-01','YYYY-MM-DD')) then insert into part_test_tri2_202203 values (NEW.*) ; elsif ( NEW.logdate >= to_date('2022-02-01','YYYY-MM-DD') and NEW.logdate < to_date('2022-03-01','YYYY-MM-DD')) then insert into part_test_tri2_202202 values (NEW.*) ; elsif ( NEW.logdate >= to_date('2022-01-01','YYYY-MM-DD') and NEW.logdate < to_date('2022-02-01','YYYY-MM-DD')) then insert into part_test_tri2_202201 values (NEW.*) ; else raise exception 'Out of range' ; end if ; return null ; end ; $$ language plpgsql ; -- insert trigger create trigger t_part_test_tri2_insert before insert on part_test_tri2 for each row execute procedure f_part_test_tri2_insert() ; do $$ begin for i in 1..150 loop for j in 1..10000 loop insert into part_test_tri2 values ( i , j , to_date('20211231','YYYYMMDD')+i ); end loop; end loop; end $$; |
플랜을 확인해 보자
| explain select count(*) from part_test_tri2 where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27256.53..27256.54 rows=1 width=8) | | -> Gather (cost=27256.32..27256.53 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26256.32..26256.33 rows=1 width=8) | | -> Parallel Append (cost=0.00..26089.90 rows=66568 width=0) | | -> Parallel Seq Scan on part_test_tri2_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202203 (cost=0.00..5323.06 rows=93972 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202205 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2 (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate <= to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_tri2 where logdate between to_date ('2022-03-05','YYYY-MM-DD') and to_date ('2022-03-20','YYYY-MM-DD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 2087.663 ms (00:02.088) explain select count(*) from part_test_tri2 where logdate >= to_date ('2022-03-05','YYYY-MM-DD') and logdate < to_date ('2022-03-20','YYYY-MM-DD') ; +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27225.60..27225.61 rows=1 width=8) | | -> Gather (cost=27225.39..27225.60 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26225.39..26225.40 rows=1 width=8) | | -> Parallel Append (cost=0.00..26069.28 rows=62444 width=0) | | -> Parallel Seq Scan on part_test_tri2_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202203 (cost=0.00..5323.06 rows=88149 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202205 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | | -> Parallel Seq Scan on part_test_tri2 (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= to_date('2022-03-05'::text, 'YYYY-MM-DD'::text)) AND (logdate < to_date('2022-03-20'::text, 'YYYY-MM-DD'::text))) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_tri2 where logdate >= to_date ('2022-03-05','YYYY-MM-DD') and logdate < to_date ('2022-03-20','YYYY-MM-DD') ; +--------+ | count | +--------+ | 150000 | +--------+ (1 row) Time: 2174.951 ms (00:02.175) explain select count(*) from part_test_tri2 where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=27256.53..27256.54 rows=1 width=8) | | -> Gather (cost=27256.32..27256.53 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=26256.32..26256.33 rows=1 width=8) | | -> Parallel Append (cost=0.00..26089.90 rows=66568 width=0) | | -> Parallel Seq Scan on part_test_tri2_202201 (cost=0.00..5323.06 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202203 (cost=0.00..5323.06 rows=93972 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202204 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202205 (cost=0.00..5151.41 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri2_202202 (cost=0.00..4808.12 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | | -> Parallel Seq Scan on part_test_tri2 (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= to_date('20220305'::text, 'YYYYMMDD'::text)) AND (logdate <= to_date('20220320'::text, 'YYYYMMDD'::text))) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_tri2 where logdate between to_date ('20220305','YYYYMMDD') and to_date ('20220320','YYYYMMDD') ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 1969.876 ms (00:01.970) explain select count(*) from part_test_tri2 where logdate between date '2022-03-05' and date '2022-03-20' ; +-------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +-------------------------------------------------------------------------------------------------------------+ | Finalize Aggregate (cost=22844.77..22844.78 rows=1 width=8) | | -> Gather (cost=22844.55..22844.76 rows=2 width=8) | | Workers Planned: 2 | | -> Partial Aggregate (cost=21844.55..21844.56 rows=1 width=8) | | -> Parallel Append (cost=0.00..21678.13 rows=66568 width=0) | | -> Parallel Seq Scan on part_test_tri2_202201 (cost=0.00..4411.29 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_tri2_202203 (cost=0.00..4411.29 rows=93972 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_tri2_202204 (cost=0.00..4269.06 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_tri2_202205 (cost=0.00..4269.06 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_tri2_202202 (cost=0.00..3984.59 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | | -> Parallel Seq Scan on part_test_tri2 (cost=0.00..0.00 rows=1 width=0) | | Filter: ((logdate >= '2022-03-05'::date) AND (logdate <= '2022-03-20'::date)) | +-------------------------------------------------------------------------------------------------------------+ select count(*) from part_test_tri2 where logdate between date '2022-03-05' and date '2022-03-20' ; +--------+ | count | +--------+ | 160000 | +--------+ (1 row) Time: 369.237 ms |
반응형