반응형

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

 

    점검 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
반응형

+ Recent posts