My Books

My Slides

rss 아이콘 이미지

2017년 8월 PostgreSQL 1-Day 튜닝 교육 자료입니다.

PostgreSQL 2017.07.31 20:46 Posted by 시연아카데미

2017년 8월 교육을 위해서 기존 교육 자료에 아래의 내용을 추가했습니다.

 

아키텍처

  •  PostgreSQL Plan Caching과 Oracle Shared Pool 비교
  •  Unlogged 테이블, Temporary 테이블, 일반 테이블 속성 및 성능 비교
  •  Modulo 연산을 이용한 XID 비교 로직 
  •  파티션 개수에 따른 입력 성능 테스트 (버전 9.6 및 10)

 

옵타마이저

  • 클래식 해시 조인 수행 절차 
  • Grace 해시 조인 수행 절차 
  • Simple View Merging 예제 변경  
  • JPPD 예제 변경 및 JPPD 구현 방안 추가

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

Modulo 연산을 이용한 PostgreSQL XID 비교 로직 설명

PostgreSQL 2017.07.11 23:11 Posted by 시연아카데미

PostgreSQL은 XID 간의 비교를 위해서 Modulo 연산을 사용합니다. 

 

개념은 간단합니다.

 

현재 XID 기준으로 2^31 (대략 20억) 이전 데이터는 OLD 데이터이고, 2^31 이후 데이터는 NEW 데이터라는 개념입니다.

 

그런데 수치를 대입해서 계산해보면 이해가 되지 않는 부분이 있습니다.

 

그동안은 그냥 그러려니 하고 넘겼던 부분인데, 6월 수강자 분중에서 이 부분을 질문하신 분이 계셔서 조금 더 자세히 정리했습니다.

 

Modulo 연산을 이용한 PosgreSQL XID 비교 로직 설명 from 시연아카데미

 

D1, ID2 값을 입력하면서 직접 확인해보고 싶은 분들은 아래의 C-Source를 이용해보세요.

 

컴파일 및 사용 예제

 

$ cc -o comp comp.c

$ comp 4294967295 3
id1=(unsigned int) 4294967295            id1=(int) -1
id2=(unsigned int) 3                          id2=(int) 3
[DEBUG] diff = (int) (id1 - id2) => [-4]
[DEBUG] return (diff < 0)
[1] = Result of TransactionIdPrecedes(id1,id2)

 

comp.c

 

#include <stdio.h>
#include <stdlib.h>
#include<stdbool.h>

typedef unsigned int TransactionId;
#define TransactionIdIsNormal(xid)           ((xid) >= 3)

bool
TransactionIdPrecedes(TransactionId id1, TransactionId id2)
{
        /*
         ** If either ID is a permanent XID then we can just do unsigned
         ** comparison.  If both are normal, do a modulo-2^32 comparison.
         **/
        int           diff;

        if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2)) {
                printf("[DEBUG] id1 or id2 isn't a Normal XID, Therefore, it does not perform 2^31 modular operations\n");
                printf("[DEBUG] return (id1 < id2), id1=%u id2=%u\n", id1, id2);
                return (id1 < id2);
        }

        diff = (int) (id1 - id2);
        printf("[DEBUG] diff = (int) (id1 - id2) => [%d] \n", (int) (id1 - id2));
        printf("[DEBUG] return (diff < 0) \n");

        return (diff < 0 );
}

main(int argc, char *argv[])
{
        TransactionId id1;
        TransactionId id2;
        int res;

        if (argc != 3) {
                printf("usage> comp <id1> <id2>\n");
                exit(-1);
        }

        id1 = atoi(argv[1]);
        id2 = atoi(argv[2]);

        printf("id1=(unsigned int) %u\t\t id1=(int) %d\n", id1, id1);
        printf("id2=(unsigned int) %u\t\t id2=(int) %d\n", id2, id2);

        printf("[%d] = Result of TransactionIdPrecedes(id1,id2)\n", TransactionIdPrecedes(id1, id2));
}

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

PostgreSQL 1-Day 교육 자료 업데이트되었습니다.

PostgreSQL 2017.06.10 23:29 Posted by 시연아카데미

2017년 6월 교육을 위해서 기존 교육 자료에 파티션, Outer Join, 통계 정보등의 내용을 추가했습니다.

 

 

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

PostgreSQL 10 - Parallel Index Scan 소개

PostgreSQL 2017.05.24 22:37 Posted by 시연아카데미

이번 시간에 소개할 내용은 PostgreSQL 10 버전부터 지원하는 Parallel Index Scan 기능입니다.

 

기능을 확인하기 전까지는 ORACLE의 Parallel Index Fast Full Scan 기능과 유사할 것으로 생각했는데, 조금 더 스마트합니다. (박수!!)

 

PostgreSQL 10 버전의 Parallel Index Scan 기능은 Index Range Scan시에도 병렬 처리가 가능합니다.

 

즉, ORACLE은 Index Fast Full Scan 방식을 이용해서 전체 인덱스 블록을 스캔할 때만 병렬 처리가 가능하지만, PostgreSQL은 인덱스의 특정 범위에 대한 병렬 처리도 가능합니다.

 

알고리즘은 다음과 같습니다.

  1. 조건절 범위에 해당하는 "Start 리프 블록"과 "End 리프 블록"의 위치를 계산합니다.
  2. 처리 범위가 인덱스 병렬 처리를 할 정도로 큰지 확인합니다.
  3. 만일 크다면, 크기에 따라서 Worker 개수를 설정한 후에, "Start" ~ "End" 범위를 나눠서 처리합니다.
  4. 만일 작다면, 싱글 프로세스로 처리합니다. 
      

이처럼, 인덱스 Range 스캔 시에도 병렬 처리를 지원함으로써 튜닝 시에 더욱 다양한 방법을 적용할 수 있게 됐습니다. (튜너 입장에서는 좋은 무기를 하나 얻은 셈입니다)

 

그럼 테스트 내용을 살펴보겠습니다.

 

 

테스트 환경 구성

 

테스트 환경은 이전 포스트에서 사용한 환경을 이용합니다.

 

그리고 np1 테이블의 c1 칼럼에 인덱스를 생성합니다.

 

create index np1_c1_idx on np1(c1);

 

Parallel 처리와 관련된 파라미터 확인 및 설정 변경

 

아래의 파라미터 중에서 Worker 개수와 관련된 파라미터인 max_parallel_workers는 24로, max_parallel_workers_per_gather는 8로 변경한 후에 재기동합니다.

 

postgres=# select name, setting, unit from pg_settings where name like '%parallel%';
              name               | setting | unit
---------------------------------+---------+------
 force_parallel_mode             | off     |
 max_parallel_workers            | 8       |
 max_parallel_workers_per_gather | 2       |
 min_parallel_index_scan_size    | 64      | 8kB
 min_parallel_table_scan_size    | 1024    | 8kB

 

 

-- 재기동 후에 변경 내용 확인

 

재기동 후에 파라미터가 정상적으로 변경된 것을 확인합니다.

 

postgres=# select name, setting, unit from pg_settings where name like '%parallel%';
              name               | setting | unit
---------------------------------+---------+------
 force_parallel_mode             | off     |
 max_parallel_workers            | 24      |
 max_parallel_workers_per_gather | 8       |
 min_parallel_index_scan_size    | 64      | 8kB
 min_parallel_table_scan_size    | 1024    | 8kB

 

-- min_parallel_table_scan_size 파라미터의 의미

 

해당 파라미터는 테이블 병렬 처리 여부를 결정하는 테이블 최소 크기를 정의합니다. 기본설정값은 8MB입니다. 즉, 8MB 이상이면 병렬 처리 대상입니다. 그리고 해당 크기의 3배수 단위로 Worker가 1개씩 추가되며, 최대 7까지 할당됩니다.

 

-- min_parallel_index_scan_size 파라미터의 의미

 

해당 파라미터는 인덱스 병렬 처리 여부를 결정하는 인덱스 최소 크기를 정의합니다. 기본설정값은 512kB입니다. 즉, 512kB 이상이면 병렬 처리 대상입니다. 그리고 해당 크기의 3배수 단위로 Worker가 1개씩 추가되며, 최대 7까지 할당됩니다.

 

 

Parallel Index Scan 동작 방식 세부 내용

 

테이블 병렬 처리는 통계 정보를 이용합니다. 그런데 인덱스 병렬 처리는 조건절에 해당하는 범위를 이용해서 결정합니다. 이 부분은 소스를 잠깐 보는 게 도움이 될 것 같습니다.

 

아래 소스를 보면, 처리 대상 인덱스 페이지수를 계산한 결과를 index_pages 변수에 저장하고, 해당 값과 min_parallel_index_scan_size 값을 비교합니다.

 

그리고 min_parallel_index_scan_size * 3을 수행하면서 Worker 개수를 1씩 증가한다는 것을 알 수 있습니다.

 

그리고 소스의 아랫 부분을 보면, Worker의 개수는 "테이블 병렬 처리를 위해 계산된 수치와 인덱스 병렬 처리를 위해 계산된 수치 중에서 더 작은 수치로 설정"한다는 것을 알 수 있습니다.

 

src/backend/optimizer/path/allpaths.c

if (index_pages >= 0)
{
        int                     index_parallel_workers = 1;
        int                     index_parallel_threshold;

        /* same calculation as for heap_pages above */
		index_parallel_threshold = Max(min_parallel_index_scan_size, 1);        
		while (index_pages >= (BlockNumber) (index_parallel_threshold * 3)) 
        {
                index_parallel_workers++;
                index_parallel_threshold *= 3;
                if (index_parallel_threshold > INT_MAX / 3)
                        break;          /* avoid overflow */
        }		

        if (parallel_workers > 0) 
                parallel_workers = Min(parallel_workers, index_parallel_workers);
        else
                parallel_workers = index_parallel_workers;

 

Parallel Worker 개수 산정 방식

 

테이블과 인덱스 스캔 시에 사용되는 Parallel Worker 개수는 다음과 같습니다.

 

표-1. Parallel Worker 개수

 

테스트 #1: Parallel Index Scan으로 수행 (범위는 백만)

 

아래의 쿼리를 수행하면, Worker 프로세스는 3개가 론칭됩니다. 왜 3개인지 살펴볼까요?

 

테이블 블록 수는 23,249이므로, 표-1에 따라서 Worker는 3개입니다.

 

그리고 인덱스 처리 범위는 5,465이므로, 표-1에 따라서 Worker는 5개입니다. 3개와 5개 중에서 최솟값을 선택하므로 3개의 Worker가 론칭되는 것입니다.

 

 
explain (analyze, buffers) 
select count(*) from np1 where c1 between 1000000 and 2000000 and dummy='dummy';

[DEBUG_INDEX_PQ] heap_pages=[23249.000000] min_parallel_table_scan_size=[1024]  index_pages=[2703.000000] min_parallel_index_scan_size=[64]

                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=32710.82..32710.83 rows=1 width=8) (actual time=263.027..263.027 rows=1 loops=1)
   Buffers: shared hit=12035
   ->  Gather  (cost=32710.50..32710.81 rows=3 width=8) (actual time=263.014..263.023 rows=4 loops=1)
         Workers Planned: 3
         Workers Launched: 3
         Buffers: shared hit=12035
         ->  Partial Aggregate  (cost=31710.50..31710.51 rows=1 width=8) (actual time=250.533..250.533 rows=1 loops=4)
               Buffers: shared hit=11612
               ->  Parallel Index Scan using np1_c1_idx on np1  (cost=0.43..30915.98 rows=317806 width=0) (actual time=0.032..186.220 rows=250000 loops=4)
                     Index Cond: ((c1 >= 1000000) AND (c1 <= 2000000))
                     Filter: (dummy = 'dummy'::bpchar)
                     Buffers: shared hit=11612
 Planning time: 0.123 ms
 Execution time: 264.310 ms

 

테스트 #2: Serial Index Scan으로 수행 (범위는 2백만)

 

Parallel Index Scan의 성능을 확인해볼까요? max_parallel_workers_per_gather 파라미터를 0으로 설정한 후에 Serial Index Scan으로 수행하면 Parallel Index Scan보다 50% 이상 느립니다.

 

물론, 처리 범위와 환경에 따라서 성능 차이는 다르겠지만, Parallel Index Scan은 매우 효과적인 튜닝 방안인 것으로 보입니다.

 

set max_parallel_workers_per_gather=0;

explain (analyze, buffers)
select count(*) from np1 where c1 between 1000000 and 2000000 and dummy='dummy';
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=41721.38..41721.39 rows=1 width=8) (actual time=431.104..431.104 rows=1 loops=1)
   Buffers: shared hit=9105
   ->  Index Scan using np1_c1_idx on np1  (cost=0.43..39258.39 rows=985198 width=0) (actual time=0.044..299.974 rows=1000001 loops=1)
         Index Cond: ((c1 >= 1000000) AND (c1 <= 2000000))
         Filter: (dummy = 'dummy'::bpchar)
         Buffers: shared hit=9105
 Planning time: 0.119 ms
 Execution time: 431.169 ms

 

참고로, Parallel Index Scan은 Index Only Scan 방식 및 Bitmap Index Scan 방식에서도 동작합니다.

 

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

이전 포스트에서 언급한 것과 같이 PostgreSQL은 v9.6까지 파티션 조인 Pruning을 제공하지 않습니다.

 

그런데 EDB 사에서 작성한 문서 (여기 참조)를 보면, 10 버전부터는 선언적인 파티션 기능을 제공할 뿐 아니라 Partition Wise Join (이하 PWJ)도 제공한다고 설명하고 있습니다.

 

매우 반가운 소식이죠.

 

기쁜 마음에, PostgreSQL 베타 10 버전을 설치한 후에 PWJ와 조인 Pruning을 테스트했습니다.

 

음. 예상과 달리 PWJ도 지원되지 않고 조인 Pruning도 여전히 지원되지 않습니다. (정식 버전이 나오면 다시 테스트해봐야겠습니다)

 

테스트 결과를 살펴보겠습니다.

 

 

파티션용 테이블 환경 구성

 

파티션 생성 방법은 PARTITION BY 키워드를 이용합니다. 그리고 각 파티션은 PARTITION OF 키워드를 이용해서 생성합니다.

 

파티션 생성 시에 주의할 점은 "TO" 뒤에 지정된 값은 해당 값을 포함하지 않는다는 점입니다.

 

예를 들어, 2017년 1월 파티션은 "TO" 값을 ('2017-02-01')로 지정해야 합니다.

 

즉, ORACLE로 치면 "VALUES LESS THAN TO_DATE('2017-02-01', 'YYYY-MM-DD')"과 같은 의미입니다.

 

drop table p1 cascade;
drop table p2 cascade;

create table p1 (
    c1        integer,
    logdate   date,
    dummy     char(10)
) partition by range (logdate);

create table p2 (
    c1        integer,
    logdate   date,
	amount    integer,
    dummy     char(10)
) partition by range (logdate);


create table p1_y201701 partition of p1 for values from ('2017-01-01') to ('2017-02-01');
create table p1_y201702 partition of p1 for values from ('2017-02-01') to ('2017-03-01');
create table p1_y201703 partition of p1 for values from ('2017-03-01') to ('2017-04-01');
create table p1_y201704 partition of p1 for values from ('2017-04-01') to ('2017-05-01');
create table p1_y201705 partition of p1 for values from ('2017-05-01') to ('2017-06-01');
create table p1_y201706 partition of p1 for values from ('2017-06-01') to ('2017-07-01');
create table p1_y201707 partition of p1 for values from ('2017-07-01') to ('2017-08-01');
create table p1_y201708 partition of p1 for values from ('2017-08-01') to ('2017-09-01');
create table p1_y201709 partition of p1 for values from ('2017-09-01') to ('2017-10-01');
create table p1_y201710 partition of p1 for values from ('2017-10-01') to ('2017-11-01');
create table p1_y201711 partition of p1 for values from ('2017-11-01') to ('2017-12-01');
create table p1_y201712 partition of p1 for values from ('2017-12-01') to ('2018-01-01');

create table p2_y201701 partition of p2 for values from ('2017-01-01') to ('2017-02-01');
create table p2_y201702 partition of p2 for values from ('2017-02-01') to ('2017-03-01');
create table p2_y201703 partition of p2 for values from ('2017-03-01') to ('2017-04-01');
create table p2_y201704 partition of p2 for values from ('2017-04-01') to ('2017-05-01');
create table p2_y201705 partition of p2 for values from ('2017-05-01') to ('2017-06-01');
create table p2_y201706 partition of p2 for values from ('2017-06-01') to ('2017-07-01');
create table p2_y201707 partition of p2 for values from ('2017-07-01') to ('2017-08-01');
create table p2_y201708 partition of p2 for values from ('2017-08-01') to ('2017-09-01');
create table p2_y201709 partition of p2 for values from ('2017-09-01') to ('2017-10-01');
create table p2_y201710 partition of p2 for values from ('2017-10-01') to ('2017-11-01');
create table p2_y201711 partition of p2 for values from ('2017-11-01') to ('2017-12-01');
create table p2_y201712 partition of p2 for values from ('2017-12-01') to ('2018-01-01');

-- p1 테이블에 일자별로 만건 입력 

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into p1 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, 'dummy');
	 end loop;
  end loop;
end $$;
	
-- p2 테이블에 일자별로 만건 입력

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into p2 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, j, 'dummy');
	 end loop;
  end loop;
end $$;

 

Non-파티션 테이블 환경 구성

 

파티션 테이블의 테스트 결과와 비교하기 위해 Non-파티션 테이블을 생성합니다.

 

drop table np1;
drop table np2;

create table np1 (
    c1        integer,
    logdate   date,
    dummy     char(10)
);

create table np2 (
    c1        integer,
    logdate   date,
	amount    integer,
    dummy     char(10)
);

-- np1 테이블에 일자별로 만건 입력 

do $$	
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into np1 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, 'dummy');
	 end loop;
  end loop;
end $$;

-- np2 테이블에 일자별로 만건 입력

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into np2 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, j, 'dummy');
	 end loop;
  end loop;
end $$;

 

통계 정보 생성 및 크기 확인

 

월별 파티션 테이블 크기 및 건수를 확인합니다.

 

analyze np1;
analyze np2;

analyze p1;
analyze p2;

analyze p1_y201701; 
analyze p1_y201702;
analyze p1_y201703;
analyze p1_y201704;
analyze p1_y201705;
analyze p1_y201706;
analyze p1_y201707;
analyze p1_y201708;
analyze p1_y201709;
analyze p1_y201710;
analyze p1_y201711;
analyze p1_y201712;

analyze p2_y201701; 
analyze p2_y201702;
analyze p2_y201703;
analyze p2_y201704;
analyze p2_y201705;
analyze p2_y201706;
analyze p2_y201707;
analyze p2_y201708;
analyze p2_y201709;
analyze p2_y201710;
analyze p2_y201711;
analyze p2_y201712;

select relname, relpages, reltuples::integer 
from   pg_class
where  relname like 'p1%' or
       relname like 'p2%' or 
	   relname like 'np%'
order by 1;
  relname   | relpages | reltuples
------------+----------+-----------
 np1        |    23249 |   3650000
 np2        |    23249 |   3650000
 p1         |        0 |         0
 p1_y201701 |     1975 |    310000
 p1_y201702 |     1784 |    280000
 p1_y201703 |     1975 |    310000
 p1_y201704 |     1911 |    300000
 p1_y201705 |     1975 |    310000
 p1_y201706 |     1911 |    300000
 p1_y201707 |     1975 |    310000
 p1_y201708 |     1975 |    310000
 p1_y201709 |     1911 |    300000
 p1_y201710 |     1975 |    310000
 p1_y201711 |     1911 |    300000
 p1_y201712 |     1975 |    310000
 p2         |        0 |         0
 p2_y201701 |     1975 |    310000
 p2_y201702 |     1784 |    280000
 p2_y201703 |     1975 |    310000
 p2_y201704 |     1911 |    300000
 p2_y201705 |     1975 |    310000
 p2_y201706 |     1911 |    300000
 p2_y201707 |     1975 |    310000
 p2_y201708 |     1975 |    310000
 p2_y201709 |     1911 |    300000
 p2_y201710 |     1975 |    310000
 p2_y201711 |     1911 |    300000
 p2_y201712 |     1975 |    310000

 

PQ Disable

 

1차 테스트 시에는 PQ 수행을 방지하기 위해서 max_parallel_workers_per_gather 파라미터를 0으로 변경합니다. (기본설정값: 2)

 

 

Non-파티션 테이블간에 전체 레코드 조인

 

Non-파티션 테이블 간의 조인 수행 결과, WORK_MEM을 대략 171MB 사용합니다.

 

set work_mem='200MB';
 
explain (analyze, buffers) 
select count(*)
from   np1 a, np2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=229123.24..229123.25 rows=1 width=8) (actual time=5541.242..5541.242 rows=1 loops=1)
   Buffers: shared hit=46498
   ->  Hash Join  (cost=114499.00..229098.19 rows=10019 width=0) (actual time=1976.580..5091.775 rows=3650000 loops=1)
         Hash Cond: ((a.logdate = b.logdate) AND (a.c1 = b.c1))
         Buffers: shared hit=46498
         ->  Seq Scan on np1 a  (cost=0.00..59749.00 rows=3650000 width=8) (actual time=0.009..508.609 rows=3650000 loops=1)
               Buffers: shared hit=23249
         ->  Hash  (cost=59749.00..59749.00 rows=3650000 width=8) (actual time=1965.800..1965.800 rows=3650000 loops=1)
               Buckets: 4194304  Batches: 1  Memory Usage: 175347kB
               Buffers: shared hit=23249
               ->  Seq Scan on np2 b  (cost=0.00..59749.00 rows=3650000 width=8) (actual time=0.024..657.624 rows=3650000 loops=1)
                     Buffers: shared hit=23249
 Planning time: 0.090 ms
 Execution time: 5551.308 ms

 

 

파티션 테이블간에 전체 레코드 조인

 

그럼 파티션 테이블 간에 조인을 수행해볼까요? 만일 PWJ로 수행된다면 수행 속도도 더 빠르고 WORK_MEM도 더 적게 쓸 것이 분명합니다.
PJW는 파티션 단위로 조인을 수행하므로, 더 적은 WORK_MEM을 가지고 더 빠르게 수행된다는 장점이 있으니까요.

그런데 아래의 수행 결과를 보면 수행 속도도 더 느리고 WORK_MEM도 기존과 동일한 171MB를 사용합니다.

 

set work_mem='200MB';

explain (analyze, buffers) 
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1;
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=229131.00..229131.01 rows=1 width=8) (actual time=7544.717..7544.717 rows=1 loops=1)
   Buffers: shared hit=46506
   ->  Hash Join  (cost=114503.00..229106.00 rows=10000 width=0) (actual time=2846.786..7058.230 rows=3650000 loops=1)
         Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
         Buffers: shared hit=46506
         ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.008..1348.818 rows=3650000 loops=1)
               Buffers: shared hit=23253
               ->  Seq Scan on p2_y201701 b  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..54.608 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201702 b_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.044..52.027 rows=280000 loops=1)
                     Buffers: shared hit=1784
               ->  Seq Scan on p2_y201703 b_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.015..54.587 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201704 b_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.011..53.759 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201705 b_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.018..60.088 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201706 b_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.012..53.712 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201707 b_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.028..54.309 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201708 b_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.011..66.090 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201709 b_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.019..53.649 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201710 b_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.011..57.762 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201711 b_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.014..51.902 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201712 b_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.012..54.026 rows=310000 loops=1)
                     Buffers: shared hit=1975
         ->  Hash  (cost=59753.00..59753.00 rows=3650000 width=8) (actual time=2834.113..2834.113 rows=3650000 loops=1)
               Buckets: 4194304  Batches: 1  Memory Usage: 175347kB
               Buffers: shared hit=23253
               ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.038..1359.414 rows=3650000 loops=1)
                     Buffers: shared hit=23253
                     ->  Seq Scan on p1_y201701 a  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.021..54.720 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201702 a_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.014..52.013 rows=280000 loops=1)
                           Buffers: shared hit=1784
                     ->  Seq Scan on p1_y201703 a_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..58.126 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201704 a_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.011..54.138 rows=300000 loops=1)
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201705 a_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.013..57.886 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201706 a_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.010..54.615 rows=300000 loops=1)
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201707 a_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..56.619 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201708 a_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..54.834 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201709 a_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.009..54.792 rows=300000 loops=1)
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201710 a_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.009..57.051 rows=310000 loops=1)
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201711 a_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.009..64.463 rows=300000 loops=1)
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201712 a_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.009..54.757 rows=310000 loops=1)
                           Buffers: shared hit=1975
 Planning time: 0.434 ms
 Execution time: 7550.042 ms

 

PQ로 수행 - 파티션 테이블간에 전체 레코드 조인

 

설마 PWJ가 PQ인 경우에만 동작하는 걸까요?

 

max_parallel_workers_per_gather 파라미터를 24로 설정한 후에 다시 테스트를 수행했습니다.

 

PQ를 사용해도 PWJ는 동작하지 않습니다.

 

속도도 아주 조금 빨라졌을 뿐이고 WORK_MEM도 기존과 동일하게 171MB를 시용합니다.

 

제가 기대했던 것은 파티션마다 1개의 Parallel Worker가 병렬로 (즉, 12개가 동시에) 해시 조인을 수행하는 것이었으나, 아래의 Explain 결과를 보면 Parallel Worker는 1개만 론칭됩니다.

 

Parallel Worker가 1개만 론칭되는 이유는 파티션 1개의 크기가 15MB이기 때문입니다.

 

즉, Parallel Worker의 개수를 정하는 기준은 쿼리에 사용되는 파티션 개수가 아닌, 개별 파티션의 크기임을 알 수 있습니다. (이점은 개선이 필요할것 같습니다)

 

참고로 Parallel 처리 수행 여부를 결정하는 기준은 테이블 크기이고, 9.6 버전까지는 min_parallel_relation_size 파라미터 (기본설정값: 8MB)였으나, 10 버전부터는 min_parallel_table_scan_size 파라미터 (기본설정값: 1024 * 8Kb)를 사용합니다. 그리고 파라미터값의 3배수마다 Parallel Worker를 1개씩 더 할당하고, 최대 할당 개수는 7개입니다.

 

set work_mem='200MB';

explain (analyze, buffers) 
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=192506.14..192506.15 rows=1 width=8) (actual time=5991.005..5991.006 rows=1 loops=1)
   Buffers: shared hit=70074
   ->  Gather  (cost=192506.03..192506.14 rows=1 width=8) (actual time=5965.123..5990.999 rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         Buffers: shared hit=70074
         ->  Partial Aggregate  (cost=191506.03..191506.04 rows=1 width=8) (actual time=5962.763..5962.763 rows=1 loops=2)
               Buffers: shared hit=69844
               ->  Hash Join  (cost=114503.00..191491.32 rows=5882 width=0) (actual time=3328.256..5687.283 rows=1825000 loops=2)
                     Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
                     Buffers: shared hit=69844
                     ->  Append  (cost=0.00..44723.59 rows=2147061 width=8) (actual time=0.014..785.853 rows=1825000 loops=2)
                           Buffers: shared hit=23253
                           ->  Parallel Seq Scan on p2_y201701 b  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.012..35.618 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201702 b_1  (cost=0.00..3431.06 rows=164706 width=8) (actual time=0.012..30.147 rows=140000 loops=2)
                                 Buffers: shared hit=1784
                           ->  Parallel Seq Scan on p2_y201703 b_2  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.009..34.888 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201704 b_3  (cost=0.00..3675.71 rows=176471 width=8) (actual time=0.006..33.808 rows=150000 loops=2)
                                 Buffers: shared hit=1911
                           ->  Parallel Seq Scan on p2_y201705 b_4  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.009..38.101 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201706 b_5  (cost=0.00..3675.71 rows=176471 width=8) (actual time=0.008..33.637 rows=150000 loops=2)
                                 Buffers: shared hit=1911
                           ->  Parallel Seq Scan on p2_y201707 b_6  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.009..35.359 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201708 b_7  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.008..34.228 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201709 b_8  (cost=0.00..3675.71 rows=176471 width=8) (actual time=0.009..33.737 rows=150000 loops=2)
                                 Buffers: shared hit=1911
                           ->  Parallel Seq Scan on p2_y201710 b_9  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.008..34.722 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                           ->  Parallel Seq Scan on p2_y201711 b_10  (cost=0.00..3675.71 rows=176471 width=8) (actual time=0.007..34.295 rows=150000 loops=2)
                                 Buffers: shared hit=1911
                           ->  Parallel Seq Scan on p2_y201712 b_11  (cost=0.00..3798.53 rows=182353 width=8) (actual time=0.009..33.017 rows=155000 loops=2)
                                 Buffers: shared hit=1975
                     ->  Hash  (cost=59753.00..59753.00 rows=3650000 width=8) (actual time=3311.668..3311.668 rows=3650000 loops=2)
                           Buckets: 4194304  Batches: 1  Memory Usage: 175347kB
                           Buffers: shared hit=46506
                           ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.037..1636.855 rows=3650000 loops=2)
                                 Buffers: shared hit=46506
                                 ->  Seq Scan on p1_y201701 a  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.036..71.783 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201702 a_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.032..63.401 rows=280000 loops=2)
                                       Buffers: shared hit=3568
                                 ->  Seq Scan on p1_y201703 a_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.019..71.326 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201704 a_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.020..67.097 rows=300000 loops=2)
                                       Buffers: shared hit=3822
                                 ->  Seq Scan on p1_y201705 a_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.029..71.276 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201706 a_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.021..66.124 rows=300000 loops=2)
                                       Buffers: shared hit=3822
                                 ->  Seq Scan on p1_y201707 a_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.038..66.720 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201708 a_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.022..75.394 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201709 a_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.021..66.812 rows=300000 loops=2)
                                       Buffers: shared hit=3822
                                 ->  Seq Scan on p1_y201710 a_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.022..70.167 rows=310000 loops=2)
                                       Buffers: shared hit=3950
                                 ->  Seq Scan on p1_y201711 a_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.022..94.346 rows=300000 loops=2)
                                       Buffers: shared hit=3822
                                 ->  Seq Scan on p1_y201712 a_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.029..78.436 rows=310000 loops=2)
                                       Buffers: shared hit=3950
 Planning time: 0.313 ms
 Execution time: 6027.321 ms

 

조인 Pruning 테스트

 

아래의 수행 결과를 보면, 조인 Pruning 또한 동작하지 않는 것을 알 수 있습니다.

 

explain (analyze, buffers)
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1
and    a.logdate between DATE '2017-05-01'
                     and DATE '2017-05-31';
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=129466.00..129466.01 rows=1 width=8) (actual time=3613.260..3613.260 rows=1 loops=1)
   Buffers: shared hit=25228, temp read=10163 written=10157
   ->  Hash Join  (cost=12486.00..129441.00 rows=10000 width=0) (actual time=1066.850..3569.146 rows=310000 loops=1)
         Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
         Buffers: shared hit=25228, temp read=10163 written=10157
         ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.017..1411.893 rows=3650000 loops=1)
               Buffers: shared hit=23253
               ->  Seq Scan on p2_y201701 b  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.016..61.122 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201702 b_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.010..55.294 rows=280000 loops=1)
                     Buffers: shared hit=1784
               ->  Seq Scan on p2_y201703 b_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.008..57.616 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201704 b_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.007..58.504 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201705 b_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..61.685 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201706 b_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.014..64.099 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201707 b_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..62.020 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201708 b_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..59.570 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201709 b_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.013..60.802 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201710 b_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.011..69.859 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201711 b_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.007..57.168 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201712 b_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..60.932 rows=310000 loops=1)
                     Buffers: shared hit=1975
         ->  Hash  (cost=6625.00..6625.00 rows=310000 width=8) (actual time=224.806..224.806 rows=310000 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 4055kB
               Buffers: shared hit=1975, temp written=793
               ->  Append  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.015..130.284 rows=310000 loops=1)
                     Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201705 a  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.014..72.681 rows=310000 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                           Buffers: shared hit=1975
 Planning time: 0.787 ms
 Execution time: 3613.353 ms

 

Partition WISE Join 테스트 - 파티션 1개

 

PWJ가 지원되면, 아래와 같은 쿼리의 성능도 매우 빨라집니다.

 

즉, P1 테이블은 반드시 전체 스캔을 해야하나, P2 테이블은 a.C1 조건에 해당되는 P1_Y201701 파티션과 매칭되는 P2_Y201701 파티션만 액세스하면 되기 때문인데요.

 

아래 결과를 보면, P2 테이블의 모든 파티션을 액세스합니다. 즉, PWJ가 지원되지 않는다는 것을 알 수 있습니다.

 

explain (analyze, buffers)
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1
and    a.c1 between 1 and 300000;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=200620.27..200620.28 rows=1 width=8) (actual time=3494.830..3494.830 rows=1 loops=1)
   Buffers: shared hit=46506, temp read=10139 written=10133
   ->  Hash Join  (cost=83678.26..200595.27 rows=10000 width=0) (actual time=466.617..3458.329 rows=300000 loops=1)
         Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
         Buffers: shared hit=46506, temp read=10139 written=10133
         ->  Append  (cost=0.00..59753.00 rows=3650000 width=8) (actual time=0.007..1321.298 rows=3650000 loops=1)
               Buffers: shared hit=23253
               ->  Seq Scan on p2_y201701 b  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.006..53.575 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201702 b_1  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.008..57.478 rows=280000 loops=1)
                     Buffers: shared hit=1784
               ->  Seq Scan on p2_y201703 b_2  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..56.152 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201704 b_3  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.006..52.546 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201705 b_4  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.006..57.502 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201706 b_5  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.007..53.565 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201707 b_6  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..55.867 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201708 b_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.006..56.451 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201709 b_8  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.012..55.934 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201710 b_9  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..59.008 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201711 b_10  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.010..59.359 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201712 b_11  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.051..64.638 rows=310000 loops=1)
                     Buffers: shared hit=1975
         ->  Hash  (cost=78003.00..78003.00 rows=300151 width=8) (actual time=466.224..466.224 rows=300000 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 3947kB
               Buffers: shared hit=23253, temp written=769
               ->  Append  (cost=0.00..78003.00 rows=300151 width=8) (actual time=0.007..383.795 rows=300000 loops=1)
                     Buffers: shared hit=23253
                     ->  Seq Scan on p1_y201701 a  (cost=0.00..6625.00 rows=300140 width=8) (actual time=0.007..65.470 rows=300000 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 10000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201702 a_1  (cost=0.00..5984.00 rows=1 width=8) (actual time=22.058..22.058 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 280000
                           Buffers: shared hit=1784
                     ->  Seq Scan on p1_y201703 a_2  (cost=0.00..6625.00 rows=1 width=8) (actual time=21.949..21.949 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201704 a_3  (cost=0.00..6411.00 rows=1 width=8) (actual time=22.552..22.552 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 300000
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201705 a_4  (cost=0.00..6625.00 rows=1 width=8) (actual time=24.022..24.022 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201706 a_5  (cost=0.00..6411.00 rows=1 width=8) (actual time=28.022..28.022 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 300000
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201707 a_6  (cost=0.00..6625.00 rows=1 width=8) (actual time=23.800..23.800 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201708 a_7  (cost=0.00..6625.00 rows=1 width=8) (actual time=28.858..28.858 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201709 a_8  (cost=0.00..6411.00 rows=1 width=8) (actual time=26.004..26.004 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 300000
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201710 a_9  (cost=0.00..6625.00 rows=1 width=8) (actual time=24.742..24.742 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
                     ->  Seq Scan on p1_y201711 a_10  (cost=0.00..6411.00 rows=1 width=8) (actual time=21.484..21.484 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 300000
                           Buffers: shared hit=1911
                     ->  Seq Scan on p1_y201712 a_11  (cost=0.00..6625.00 rows=1 width=8) (actual time=22.455..22.455 rows=0 loops=1)
                           Filter: ((c1 >= 1) AND (c1 <= 300000))
                           Rows Removed by Filter: 310000
                           Buffers: shared hit=1975
 Planning time: 0.477 ms
 Execution time: 3494.921 ms

 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License


이번 시간에 간단히 살펴볼 내용은 조인 Pruning이 수행되지 않는 문제점을 해결하는 방법입니다.

 

내용은 매우 간단합니다.

 

모든 테이블에 상수 조건을 입력하면 됩니다. (간단하죠?)

 

테스트를 통해 살펴보겠습니다.

 

 

파티션용 테이블 환경 구성

 

P1, P2, 2개의 월별 파티션 테이블 및 필요한 오브젝트를 생성합니다.

 

-- 파티션 테이블 p1, p2 생성 

drop table p1 cascade;
drop table p2 cascade;

create table p1 (
    c1        integer,
    logdate   date,
    dummy     char(10)
);

create table p2 (
    c1        integer,
    logdate   date,
	amount    integer,
    dummy     char(10)
);

create table p1_y201701 (CHECK( logdate >= DATE '2017-01-01' AND logdate < DATE '2017-02-01')) INHERITS (p1);
create table p1_y201702 (CHECK( logdate >= DATE '2017-02-01' AND logdate < DATE '2017-03-01')) INHERITS (p1);
create table p1_y201703 (CHECK( logdate >= DATE '2017-03-01' AND logdate < DATE '2017-04-01')) INHERITS (p1);
create table p1_y201704 (CHECK( logdate >= DATE '2017-04-01' AND logdate < DATE '2017-05-01')) INHERITS (p1);
create table p1_y201705 (CHECK( logdate >= DATE '2017-05-01' AND logdate < DATE '2017-06-01')) INHERITS (p1);
create table p1_y201706 (CHECK( logdate >= DATE '2017-06-01' AND logdate < DATE '2017-07-01')) INHERITS (p1);
create table p1_y201707 (CHECK( logdate >= DATE '2017-07-01' AND logdate < DATE '2017-08-01')) INHERITS (p1);
create table p1_y201708 (CHECK( logdate >= DATE '2017-08-01' AND logdate < DATE '2017-09-01')) INHERITS (p1);
create table p1_y201709 (CHECK( logdate >= DATE '2017-09-01' AND logdate < DATE '2017-10-01')) INHERITS (p1);
create table p1_y201710 (CHECK( logdate >= DATE '2017-10-01' AND logdate < DATE '2017-11-01')) INHERITS (p1);
create table p1_y201711 (CHECK( logdate >= DATE '2017-11-01' AND logdate < DATE '2017-12-01')) INHERITS (p1);
create table p1_y201712 (CHECK( logdate >= DATE '2017-12-01' AND logdate < DATE '2018-01-01')) INHERITS (p1);

create table p2_y201701 (CHECK( logdate >= DATE '2017-01-01' AND logdate < DATE '2017-02-01')) INHERITS (p2);
create table p2_y201702 (CHECK( logdate >= DATE '2017-02-01' AND logdate < DATE '2017-03-01')) INHERITS (p2);
create table p2_y201703 (CHECK( logdate >= DATE '2017-03-01' AND logdate < DATE '2017-04-01')) INHERITS (p2);
create table p2_y201704 (CHECK( logdate >= DATE '2017-04-01' AND logdate < DATE '2017-05-01')) INHERITS (p2);
create table p2_y201705 (CHECK( logdate >= DATE '2017-05-01' AND logdate < DATE '2017-06-01')) INHERITS (p2);
create table p2_y201706 (CHECK( logdate >= DATE '2017-06-01' AND logdate < DATE '2017-07-01')) INHERITS (p2);
create table p2_y201707 (CHECK( logdate >= DATE '2017-07-01' AND logdate < DATE '2017-08-01')) INHERITS (p2);
create table p2_y201708 (CHECK( logdate >= DATE '2017-08-01' AND logdate < DATE '2017-09-01')) INHERITS (p2);
create table p2_y201709 (CHECK( logdate >= DATE '2017-09-01' AND logdate < DATE '2017-10-01')) INHERITS (p2);
create table p2_y201710 (CHECK( logdate >= DATE '2017-10-01' AND logdate < DATE '2017-11-01')) INHERITS (p2);
create table p2_y201711 (CHECK( logdate >= DATE '2017-11-01' AND logdate < DATE '2017-12-01')) INHERITS (p2);
create table p2_y201712 (CHECK( logdate >= DATE '2017-12-01' AND logdate < DATE '2018-01-01')) INHERITS (p2);

-- p1 파티션용 트리거 함수 생성 

CREATE OR REPLACE FUNCTION p1_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN

    IF    ( NEW.logdate >= DATE '2017-01-01' AND NEW.logdate < DATE '2017-02-01') THEN INSERT INTO p1_y201701 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-02-01' AND NEW.logdate < DATE '2017-03-01') THEN INSERT INTO p1_y201702 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-03-01' AND NEW.logdate < DATE '2017-04-01') THEN INSERT INTO p1_y201703 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-04-01' AND NEW.logdate < DATE '2017-05-01') THEN INSERT INTO p1_y201704 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-05-01' AND NEW.logdate < DATE '2017-06-01') THEN INSERT INTO p1_y201705 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-06-01' AND NEW.logdate < DATE '2017-07-01') THEN INSERT INTO p1_y201706 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-07-01' AND NEW.logdate < DATE '2017-08-01') THEN INSERT INTO p1_y201707 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-08-01' AND NEW.logdate < DATE '2017-09-01') THEN INSERT INTO p1_y201708 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-09-01' AND NEW.logdate < DATE '2017-10-01') THEN INSERT INTO p1_y201709 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-10-01' AND NEW.logdate < DATE '2017-11-01') THEN INSERT INTO p1_y201710 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-11-01' AND NEW.logdate < DATE '2017-12-01') THEN INSERT INTO p1_y201711 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-12-01' AND NEW.logdate < DATE '2018-01-01') THEN INSERT INTO p1_y201712 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the p1_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

-- p2 파티션용 트리거 함수 생성 

CREATE OR REPLACE FUNCTION p2_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN

    IF    ( NEW.logdate >= DATE '2017-01-01' AND NEW.logdate < DATE '2017-02-01') THEN INSERT INTO p2_y201701 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-02-01' AND NEW.logdate < DATE '2017-03-01') THEN INSERT INTO p2_y201702 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-03-01' AND NEW.logdate < DATE '2017-04-01') THEN INSERT INTO p2_y201703 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-04-01' AND NEW.logdate < DATE '2017-05-01') THEN INSERT INTO p2_y201704 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-05-01' AND NEW.logdate < DATE '2017-06-01') THEN INSERT INTO p2_y201705 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-06-01' AND NEW.logdate < DATE '2017-07-01') THEN INSERT INTO p2_y201706 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-07-01' AND NEW.logdate < DATE '2017-08-01') THEN INSERT INTO p2_y201707 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-08-01' AND NEW.logdate < DATE '2017-09-01') THEN INSERT INTO p2_y201708 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-09-01' AND NEW.logdate < DATE '2017-10-01') THEN INSERT INTO p2_y201709 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-10-01' AND NEW.logdate < DATE '2017-11-01') THEN INSERT INTO p2_y201710 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-11-01' AND NEW.logdate < DATE '2017-12-01') THEN INSERT INTO p2_y201711 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-12-01' AND NEW.logdate < DATE '2018-01-01') THEN INSERT INTO p2_y201712 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the p2_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
 
-- p1 파티션용 INSERT 트리거 생성

CREATE TRIGGER insert_p1_trigger
    BEFORE INSERT ON p1
    FOR EACH ROW EXECUTE PROCEDURE p1_insert_trigger();
	
-- p2 파티션용 INSERT 트리거 생성

CREATE TRIGGER insert_p2_trigger
    BEFORE INSERT ON p2
    FOR EACH ROW EXECUTE PROCEDURE p2_insert_trigger();
	
-- p1 테이블에 일자별로 만건 입력 

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into p1 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, 'dummy');
	 end loop;
  end loop;
end $$;
	
-- p2 테이블에 일자별로 만건 입력

do $$
begin
  for i in 1..365 loop 
     for j in 1..10000 loop 
	    insert into p2 values((i-1)*10000+j, to_date('20170101','YYYYMMDD')+i-1, j, 'dummy');
	 end loop;
  end loop;
end $$;

 

 

통계 정보 생성 및 크기 확인

 

월별 파티션 테이블에 제대로 데이터가 입력됐는지 확인합니다.

 

analyze p1;
analyze p2;

analyze p1_y201701; 
analyze p1_y201702;
analyze p1_y201703;
analyze p1_y201704;
analyze p1_y201705;
analyze p1_y201706;
analyze p1_y201707;
analyze p1_y201708;
analyze p1_y201709;
analyze p1_y201710;
analyze p1_y201711;
analyze p1_y201712;

analyze p2_y201701; 
analyze p2_y201702;
analyze p2_y201703;
analyze p2_y201704;
analyze p2_y201705;
analyze p2_y201706;
analyze p2_y201707;
analyze p2_y201708;
analyze p2_y201709;
analyze p2_y201710;
analyze p2_y201711;
analyze p2_y201712;

select relname, relpages, reltuples::integer 
from   pg_class
where  relname like 'p1%' or
       relname like 'p2%' 
order by 1;
  relname   | relpages | reltuples
------------+----------+-----------
 p1         |        0 |         0
 p1_y201701 |     1975 |    310000
 p1_y201702 |     1784 |    280000
 p1_y201703 |     1975 |    310000
 p1_y201704 |     1911 |    300000
 p1_y201705 |     1975 |    310000
 p1_y201706 |     1911 |    300000
 p1_y201707 |     1975 |    310000
 p1_y201708 |     1975 |    310000
 p1_y201709 |     1911 |    300000
 p1_y201710 |     1975 |    310000
 p1_y201711 |     1911 |    300000
 p1_y201712 |     1975 |    310000
 p2         |        0 |         0
 p2_y201701 |     1975 |    310000
 p2_y201702 |     1784 |    280000
 p2_y201703 |     1975 |    310000
 p2_y201704 |     1911 |    300000
 p2_y201705 |     1975 |    310000
 p2_y201706 |     1911 |    300000
 p2_y201707 |     1975 |    310000
 p2_y201708 |     1975 |    310000
 p2_y201709 |     1911 |    300000
 p2_y201710 |     1975 |    310000
 p2_y201711 |     1911 |    300000
 p2_y201712 |     1975 |    310000

 

 

입력 조건이 1개의 테이블에만 있는 경우

 

아래와 같이 P1 테이블에 대해서만 날자 조건을 입력하면, P1 테이블에 대한 파티션 Pruning은 수행되지만 P2 테이블에 대한 파티션 Pruing은 수행되지 않습니다.

 

즉, 조인 Pruning을 제공하지 않으므로 P2 테이블에 딸린 모든 파티션을 Seq Scan하게 됩니다.

 

explain (analyze, buffers)
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1
and    a.logdate between DATE '2017-05-01'
                     and DATE '2017-05-31';
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=129351.64..129351.65 rows=1 width=8) (actual time=3230.240..3230.240 rows=1 loops=1)
   Buffers: shared hit=25228, temp read=10163 written=10157
   ->  Hash Join  (cost=12486.01..129349.51 rows=849 width=0) (actual time=1027.855..3191.489 rows=310000 loops=1)
         Hash Cond: ((b.logdate = a.logdate) AND (b.c1 = a.c1))
         Buffers: shared hit=25228, temp read=10163 written=10157
         ->  Append  (cost=0.00..59753.00 rows=3650001 width=8) (actual time=0.008..1308.494 rows=3650000 loops=1)
               Buffers: shared hit=23253
               ->  Seq Scan on p2 b  (cost=0.00..0.00 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)
               ->  Seq Scan on p2_y201701 b_1  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.006..55.073 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201702 b_2  (cost=0.00..4584.00 rows=280000 width=8) (actual time=0.016..50.464 rows=280000 loops=1)
                     Buffers: shared hit=1784
               ->  Seq Scan on p2_y201703 b_3  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.030..54.888 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201704 b_4  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.010..58.291 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201705 b_5  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..57.236 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201706 b_6  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.010..54.321 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201707 b_7  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.015..59.018 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201708 b_8  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..56.012 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201709 b_9  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.015..53.940 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201710 b_10  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.010..55.760 rows=310000 loops=1)
                     Buffers: shared hit=1975
               ->  Seq Scan on p2_y201711 b_11  (cost=0.00..4911.00 rows=300000 width=8) (actual time=0.009..53.782 rows=300000 loops=1)
                     Buffers: shared hit=1911
               ->  Seq Scan on p2_y201712 b_12  (cost=0.00..5075.00 rows=310000 width=8) (actual time=0.007..57.858 rows=310000 loops=1)
                     Buffers: shared hit=1975
         ->  Hash  (cost=6625.00..6625.00 rows=310001 width=8) (actual time=244.772..244.772 rows=310000 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 4055kB
               Buffers: shared hit=1975, temp written=793
               ->  Append  (cost=0.00..6625.00 rows=310001 width=8) (actual time=0.008..149.727 rows=310000 loops=1)
                     Buffers: shared hit=1975
                     ->  Seq Scan on p1 a  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                     ->  Seq Scan on p1_y201705 a_1  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.006..93.462 rows=310000 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                           Buffers: shared hit=1975
 Planning time: 0.440 ms
 Execution time: 3230.319 ms

 

입력 조건을 추가한 경우


위와 같은 문제점때문에, 쿼리 작성 시에는 아래와 같이 모든 테이블에 상수 조건을 입력해야 합니다.

수행 결과를 보면, P1 테이블에서 5월 파티션, P2 테이블에서 5월 파티션만 액세스한 것을 알 수 있습니다.

결과적으로 수행속도도 매우 빠르게 개선되었습니다.

 

explain (analyze, buffers)
select count(*)
from   p1 a, p2 b
where  a.logdate = b.logdate 
and    a.c1      = b.c1
and    a.logdate between DATE '2017-05-01'
                     and DATE '2017-05-31'
and    b.logdate between DATE '2017-05-01'
                     and DATE '2017-05-31';
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=25079.64..25079.65 rows=1 width=8) (actual time=721.734..721.734 rows=1 loops=1)
   Buffers: shared hit=3950, temp read=1598 written=1592
   ->  Hash Join  (cost=12486.01..25077.51 rows=849 width=0) (actual time=219.268..677.870 rows=310000 loops=1)
         Hash Cond: ((a.logdate = b.logdate) AND (a.c1 = b.c1))
         Buffers: shared hit=3950, temp read=1598 written=1592
         ->  Append  (cost=0.00..6625.00 rows=310001 width=8) (actual time=0.011..129.327 rows=310000 loops=1)
               Buffers: shared hit=1975
               ->  Seq Scan on p1 a  (cost=0.00..0.00 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                     Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
               ->  Seq Scan on p1_y201705 a_1  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.006..75.549 rows=310000 loops=1)
                     Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                     Buffers: shared hit=1975
         ->  Hash  (cost=6625.00..6625.00 rows=310001 width=8) (actual time=219.036..219.036 rows=310000 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 4055kB
               Buffers: shared hit=1975, temp written=793
               ->  Append  (cost=0.00..6625.00 rows=310001 width=8) (actual time=0.007..131.560 rows=310000 loops=1)
                     Buffers: shared hit=1975
                     ->  Seq Scan on p2 b  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                     ->  Seq Scan on p2_y201705 b_1  (cost=0.00..6625.00 rows=310000 width=8) (actual time=0.005..78.213 rows=310000 loops=1)
                           Filter: ((logdate >= '2017-05-01'::date) AND (logdate <= '2017-05-31'::date))
                           Buffers: shared hit=1975
 Planning time: 0.522 ms
 Execution time: 721.791 ms


 

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

PostgreSQL 1-Day 튜닝 교육을 시작합니다.

PostgreSQL 2017.04.27 11:43 Posted by 시연아카데미

어제 처음으로 외부에서 PostgreSQL 1-Day 튜닝 교육을 진행했습니다.
진행 후에 수강하신 분들의 반응을 들어보고, 저 나름대로 자체적인 평가를 해본 결과 정식으로 교육을 런칭해도 괜찮을 것같다는 생각이 들었습니다.

 

교육과 관련된 내용은 아래 링크를 참고해주세요.

 

https://siyeonacademy.wordpress.com/postgresql-1-day-tuning-workshop/

 

감사합니다.

 

김시연 올림.

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

개발자를 위한 PostgreSQL 튜닝 워크샵 교육 자료

PostgreSQL 2017.04.24 02:55 Posted by 시연아카데미

우연한 기회에 개발자 대상으로 1일짜리 PostgreSQL 성능 교육을 진행하게 됐습니다.

이를 위해 제 책중에서 액기스만 뽑아서 아주 깔끔한(?) 교육 자료를 만들었습니다.

첫 교육 진행 후에 반응이 괜찮으면 정식 교육으로 론칭 해보려고 합니다.

온사이트 교육도 환영입니다. (제 e-mail로 연락주세요. ^^) 


파트-1: 아키텍처 (개요, Shared Buffer, MVCC, Vacuum, 파티션)







파트-2: 옵티마이저 & 쿼리 튜닝 (Explain, Access 방법, 조인, Query Rewrite, PG_HINT_PLAN, BRIN, Parallel)




저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License


 

티스토리 툴바