database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 10179 게시물 읽기
No. 10179
파티션테이블 바인드변수 처리
작성자
궁금
작성일
2020-09-08 16:42
조회수
188

여기서 답변주신 내용 덕분에 테스트 진행이 조금씩 되고 있습니다. 감사합니다.

추가로 궁금한 부분이 있어서 문의 드립니다.

대량 update 를 위해 파티션테이블 관리가 반드시 필요한데

일반테이블과 성능차이가 두배이상으로 나와서 테스트한 내용 같이 첨부해봤습니다.

 

 

>> 일반테이블 : 1~5번째 plan은 상수 , 6번째부터는 bind 변수로 처리하면서 속도가 크게 향상 됨

상수 // Index Cond: ((t1.a = 1) AND (t1.b = '11'::numeric))

바인드 // Index Cond: ((t1.a = $1) AND (t1.b = $2))

 

>> 파티션테이블 : bind 변수처리가 되지 않고 계속 리터럴 처리 되면서 cpu 사용량 및 속도저하

 

 

파티션테이블에선 바인드변수 처리가 안되는걸까요? 십만건이상 수행할 때 속도차가 큽니다.

테스트는 13 beta 버전으로 하고 있습니다만 v12도 같지 않을까 싶습니다...

의견 부탁드립니다.

 

 

 

<일반테이블>

 

create table t1 (a serial, b numeric(10), c numeric(10), CONSTRAINT t1_pk PRIMARY KEY (a));

insert into t1(b,c) values(generate_series(1,200), 1111);

insert into t1(b,c) values(generate_series(201,300), 2222);

insert into t1(b,c) values(generate_series(301,400), 3333);

insert into t1(b,c) values(generate_series(401,500), 4444);

insert into t1(b,c) values(generate_series(501,600), 5555);

CREATE unique INDEX t1_ab ON t1 (a,b);

 

<파티션테이블>

 

create table p1 (a serial, b numeric(10), c numeric(10)

) PARTITION BY RANGE (b);

 

CREATE TABLE p1_01 PARTITION OF p1 FOR VALUES FROM (MINVALUE) TO (200) ;

CREATE TABLE p1_02 PARTITION OF p1 FOR VALUES FROM (200) TO (300) ;

CREATE TABLE p1_03 PARTITION OF p1 FOR VALUES FROM (300) TO (400) ;

CREATE TABLE p1_04 PARTITION OF p1 FOR VALUES FROM (400) TO (500) ;

CREATE TABLE p1_05 PARTITION OF p1 FOR VALUES FROM (500) TO (601) ;

insert into p1(b,c) values(generate_series(1,200), 1111);

insert into p1(b,c) values(generate_series(201,300), 2222);

insert into p1(b,c) values(generate_series(301,400), 3333);

insert into p1(b,c) values(generate_series(401,500), 4444);

insert into p1(b,c) values(generate_series(501,600), 5555);

CREATE UNIQUE INDEX p1_01_pk on p1_01(a);

CREATE UNIQUE INDEX p1_02_pk on p1_02(a);

CREATE UNIQUE INDEX p1_03_pk on p1_03(a);

CREATE UNIQUE INDEX p1_04_pk on p1_04(a);

CREATE UNIQUE INDEX p1_05_pk on p1_05(a);

CREATE UNIQUE INDEX p1_ab ON p1 (a, b);

 

 

begin;

prepare t1_upd as update t1 set c=c+1 where a=$1 and b=$2;

explain (analyze, buffers, verbose, timing) execute t1_upd(1,11);

 

 

 

begin;

prepare p1_upd as update p1 set c=c+1 where a=$1 and b=$2;

explain (analyze, buffers, verbose, timing) execute p1_upd(1,11);

 

 

 

 

<< 일반테이블 실행계획 >>

 

 

testdb=*# explain (analyze, buffers, verbose, timing) execute t1_upd(1,11);

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------

Update on public.t1 (cost=0.28..2.50 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=1)

Buffers: shared hit=2

-> Index Scan using t1_ab on public.t1 (cost=0.28..2.50 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=1)

Output: a, b, (c + '1'::numeric), ctid

Index Cond: ((t1.a = 1) AND (t1.b = '11'::numeric))

Buffers: shared hit=2

Planning Time: 0.048 ms

Execution Time: 0.017 ms

(8 rows)

 

testdb=*# explain (analyze, buffers, verbose, timing) execute t1_upd(1,11);

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------

Update on public.t1 (cost=0.28..2.50 rows=1 width=31) (actual time=0.010..0.011 rows=0 loops=1)

Buffers: shared hit=2

-> Index Scan using t1_ab on public.t1 (cost=0.28..2.50 rows=1 width=31) (actual time=0.010..0.010 rows=0 loops=1)

Output: a, b, (c + '1'::numeric), ctid

Index Cond: ((t1.a = $1) AND (t1.b = $2))

Buffers: shared hit=2

Planning Time: 0.047 ms

Execution Time: 0.024 ms

(8 rows)

 

testdb=*# explain (analyze, buffers, verbose, timing) execute t1_upd(1,11);

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------

Update on public.t1 (cost=0.28..2.50 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=1)

Buffers: shared hit=2

-> Index Scan using t1_ab on public.t1 (cost=0.28..2.50 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=1)

Output: a, b, (c + '1'::numeric), ctid

Index Cond: ((t1.a = $1) AND (t1.b = $2))

Buffers: shared hit=2

Planning Time: 0.007 ms

Execution Time: 0.017 ms

(8 rows)

 

 

<< 파티션테이블 실행계획 >>

 

testdb=*# explain (analyze, buffers, verbose, timing) execute p1_upd(1,11);

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------

Update on public.p1 (cost=0.14..2.37 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=1)

Update on public.p1_01 p1_1

Buffers: shared hit=1

-> Index Scan using p1_01_a_b_idx on public.p1_01 p1_1 (cost=0.14..2.37 rows=1 width=31) (actual time=0.004..0.004 rows=0 loops=1)

Output: p1_1.a, p1_1.b, (p1_1.c + '1'::numeric), p1_1.ctid

Index Cond: ((p1_1.a = 1) AND (p1_1.b = '11'::numeric))

Buffers: shared hit=1

Planning Time: 0.083 ms

Execution Time: 0.018 ms

(9 rows)

 

testdb=*# explain (analyze, buffers, verbose, timing) execute p1_upd(1,11);

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------

Update on public.p1 (cost=0.14..2.37 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=1)

Update on public.p1_01 p1_1

Buffers: shared hit=1

-> Index Scan using p1_01_a_b_idx on public.p1_01 p1_1 (cost=0.14..2.37 rows=1 width=31) (actual time=0.004..0.004 rows=0 loops=1)

Output: p1_1.a, p1_1.b, (p1_1.c + '1'::numeric), p1_1.ctid

Index Cond: ((p1_1.a = 1) AND (p1_1.b = '11'::numeric))

Buffers: shared hit=1

Planning Time: 0.084 ms

Execution Time: 0.018 ms

(9 rows)

 

testdb=*# explain (analyze, buffers, verbose, timing) execute p1_upd(1,11);

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------

Update on public.p1 (cost=0.14..2.37 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=1)

Update on public.p1_01 p1_1

Buffers: shared hit=1

-> Index Scan using p1_01_a_b_idx on public.p1_01 p1_1 (cost=0.14..2.37 rows=1 width=31) (actual time=0.004..0.004 rows=0 loops=1)

Output: p1_1.a, p1_1.b, (p1_1.c + '1'::numeric), p1_1.ctid

Index Cond: ((p1_1.a = 1) AND (p1_1.b = '11'::numeric))

Buffers: shared hit=1

Planning Time: 0.084 ms

Execution Time: 0.018 ms

(9 rows)

 

이 글에 대한 댓글이 총 2건 있습니다.

파티션 테이블에서 원하는 하위 테이블만 접근 하려면, PostgreSQL에서는 그 파티션 키가 상수값이어야 했습니다. v12부터 이 한계를 극복하기 위해 열심히 개선중에 있습니다.

prepare 부분을 저렇게 풀었네요.

제가 보기에는 이 하위 파티션 선별 작업 때문에 어쩔 수 없이 바인딩을 포기한 것 같습니다.

PostgreSQL의 한계입니다. 다음 버전에서는 좋아지겠죠. 물론 파티션 테이블에서의 prepare binding이 풀릴 것이라는 보장은 없지만.

김상기(ioseph)님이 2020-09-08 17:04에 작성한 댓글입니다.
이 댓글은 2020-09-08 17:05에 마지막으로 수정되었습니다.

계속 붙들고 시간만 보낼뻔 했네요. 다른 부분에서 성능 개선의 여지를 찾아야 겠습니다.

빠른 답변 감사드립니다!!

궁금님이 2020-09-08 17:20에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
10182인코딩 오류 이유를 알고계신분 잇나요 [1]
김철중
2020-09-21
151
10181특정 사용자가 가지는 view에 대한 조회 권한 주기 [4]
권기혁
2020-09-18
167
10180postgressql bytea[] 타입 [4]
김철수
2020-09-10
205
10179파티션테이블 바인드변수 처리 [2]
궁금
2020-09-08
188
10178datacamp를 통한 sql 입문 [1]
달려라펀치맨
2020-09-06
166
10177plpgsql 에서 세션변수 설정 [2]
궁금
2020-08-24
223
10176postgresql 의 json 타입 관련하여 문의드립니다. [1]
Jaemyoung Seo
2020-08-24
206
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2020 DSN, All rights reserved.
작업시간: 0.043초, 이곳 서비스는
	PostgreSQL v13.0으로 자료를 관리합니다