여기서 답변주신 내용 덕분에 테스트 진행이 조금씩 되고 있습니다. 감사합니다.
추가로 궁금한 부분이 있어서 문의 드립니다.
대량 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)
|