- 버젼 : Aurora PostgreSQL 13.12
파티션 테이블 관련하여 튜닝을 진행하였는데, 운영에서 이전 보다 오히려 성능이 떨어지는 상황이 발생하였다.
이에 대해 정리 해본다.
대략적인 테이블 구조
CREATE TABLE test_lgct_inrk_test (
lgct_inrk_no numeric(16) NOT NULL,
lgct_inrk_dtm timestamp NOT NULL,
lgct_rcpt_no varchar(20) NULL,
lgct_rcpt_dtm timestamp NULL,
or_no varchar(15) NULL,
or_seq numeric(5) NULL,
or_cl_no varchar(15) NULL,
dlv_fom_cd varchar(2) NULL,
ls_updr_id varchar(20) NOT NULL,
ls_updt_dtm timestamp NOT NULL,
lgct_inrk_rslt_cd varchar(10) NULL,
lgct_inrk_rslt_cn text NULL,
CONSTRAINT pk_test_lgct_inrk_test PRIMARY KEY (lgct_inrk_no, lgct_inrk_dtm)
)
PARTITION BY RANGE (lgct_inrk_dtm);
CREATE INDEX ix01_test_lgct_inrk_test ON ONLY test_lgct_inrk_test USING btree (or_no, or_seq);
CREATE INDEX ix02_test_lgct_inrk_test ON ONLY test_lgct_inrk_test USING btree (lgct_inrk_dtm, or_no);
-- Partitions
CREATE TABLE test_lgct_inrk_test_pt_202501 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2025-02-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202502 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-02-01 00:00:00') TO ('2025-03-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202503 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-03-01 00:00:00') TO ('2025-04-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202504 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-04-01 00:00:00') TO ('2025-05-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202505 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-05-01 00:00:00') TO ('2025-06-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202506 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-06-01 00:00:00') TO ('2025-07-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202507 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-07-01 00:00:00') TO ('2025-08-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202508 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-08-01 00:00:00') TO ('2025-09-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202509 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-09-01 00:00:00') TO ('2025-10-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202510 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-10-01 00:00:00') TO ('2025-11-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202511 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-11-01 00:00:00') TO ('2025-12-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202512 PARTITION OF test_lgct_inrk_test FOR VALUES FROM ('2025-12-01 00:00:00') TO ('2026-01-01 00:00:00');
원본 sql
update test_lgct_inrk_test
set ls_updr_id = 'ADMIN'
, ls_updt_dtm = now()
, lgct_rcpt_no = '3654573'
, lgct_rcpt_dtm = now()
, lgct_inrk_rslt_cd = '9999'
where 1=1
and or_no = '2025033013890'
and coalesce(or_cl_no,'') = coalesce(null,'')
and dlv_fom_cd = '01'update 쿼리를 select 쿼리로 변경하여 test를 진행하였고, 아래와 같이 변경 하였다.
튜닝 전 sql
explain analyze
select ls_updr_id,
ls_updt_dtm,
lgct_rcpt_no,
lgct_rcpt_dtm,
lgct_inrk_rslt_cd
from test_lgct_inrk_test
where 1=1
and or_no = '2025033013890'
and coalesce(or_cl_no,'') = coalesce(null,'')
and dlv_fom_cd = '01';
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Append (cost=0.43..238.62 rows=12 width=126) (actual time=5.370..8.010 rows=7 loops=1) |
-> Index Scan using test_lgct_inrk_test_pt_202501_or_no_or_seq_idx on test_lgct_inrk_test_pt_202501 test_lgct_inrk_test_1 (cost=0.43..47.33 rows=1 width=40) (actual time=3.234..3.234 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202502_or_no_or_seq_idx on test_lgct_inrk_test_pt_202502 test_lgct_inrk_test_2 (cost=0.43..47.26 rows=1 width=41) (actual time=1.627..1.627 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_3 (cost=0.43..50.76 rows=1 width=40) (actual time=0.507..1.068 rows=7 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_4 (cost=0.29..27.87 rows=1 width=41) (actual time=1.983..1.983 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202505_or_no_or_seq_idx on test_lgct_inrk_test_pt_202505 test_lgct_inrk_test_5 (cost=0.14..8.17 rows=1 width=170) (actual time=0.013..0.013 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202506_or_no_or_seq_idx on test_lgct_inrk_test_pt_202506 test_lgct_inrk_test_6 (cost=0.14..8.17 rows=1 width=170) (actual time=0.014..0.014 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202507_or_no_or_seq_idx on test_lgct_inrk_test_pt_202507 test_lgct_inrk_test_7 (cost=0.14..8.17 rows=1 width=170) (actual time=0.010..0.010 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202508_or_no_or_seq_idx on test_lgct_inrk_test_pt_202508 test_lgct_inrk_test_8 (cost=0.14..8.17 rows=1 width=170) (actual time=0.011..0.011 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202509_or_no_or_seq_idx on test_lgct_inrk_test_pt_202509 test_lgct_inrk_test_9 (cost=0.14..8.17 rows=1 width=170) (actual time=0.011..0.011 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202510_or_no_or_seq_idx on test_lgct_inrk_test_pt_202510 test_lgct_inrk_test_10 (cost=0.14..8.17 rows=1 width=170) (actual time=0.012..0.012 rows=0 loops=1)|
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202511_or_no_or_seq_idx on test_lgct_inrk_test_pt_202511 test_lgct_inrk_test_11 (cost=0.14..8.17 rows=1 width=170) (actual time=0.011..0.011 rows=0 loops=1)|
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202512_or_no_or_seq_idx on test_lgct_inrk_test_pt_202512 test_lgct_inrk_test_12 (cost=0.14..8.17 rows=1 width=170) (actual time=0.010..0.010 rows=0 loops=1)|
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
Planning Time: 3.169 ms |
Execution Time: 8.119 ms |
튜닝 후 sql
explain analyze
select ls_updr_id,
ls_updt_dtm,
lgct_rcpt_no,
lgct_rcpt_dtm,
lgct_inrk_rslt_cd
from test_lgct_inrk_test
where 1=1
and or_no = '2025033013890'
and coalesce(or_cl_no,'') = coalesce(null,'')
and dlv_fom_cd = '01'
and lgct_inrk_dtm between to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD')
and to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD') + interval '5 day';
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Append (cost=0.44..161.47 rows=12 width=126) (actual time=0.036..0.070 rows=7 loops=1) |
Subplans Removed: 10 |
-> Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_1 (cost=0.43..51.06 rows=1 width=40) (actual time=0.036..0.055 rows=7 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_2 (cost=0.29..27.97 rows=1 width=41) (actual time=0.013..0.013 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
Planning Time: 1.031 ms |
Execution Time: 0.112 ms |and lgct_inrk_dtm between to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD')
and to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD') + interval '5 day';
조건을 추가하여 partition pruning 이 동작하도록 하였다.
이렇게 마무리 하였고, 운영에 적용 하였더니 성능이 미세하게 더 나빠졌다.
다시 확인
explain analyze
update dis01.test_lgct_inrk_test
set ls_updr_id = 'ADMIN'
, ls_updt_dtm = now()
, lgct_rcpt_no = '3654573'
, lgct_rcpt_dtm = now()
, lgct_inrk_rslt_cd = '9999'
where 1=1
and or_no = '2025033013890'
and coalesce(or_cl_no,'') = coalesce(null,'')
and dlv_fom_cd = '01'
and lgct_inrk_dtm between to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD')
and to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD') + interval '5 day';
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Update on test_lgct_inrk_test (cost=0.44..161.53 rows=12 width=339) (actual time=0.571..0.573 rows=0 loops=1) |
Update on test_lgct_inrk_test_pt_202501 test_lgct_inrk_test_1 |
Update on test_lgct_inrk_test_pt_202502 test_lgct_inrk_test_2 |
Update on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_3 |
Update on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_4 |
Update on test_lgct_inrk_test_pt_202505 test_lgct_inrk_test_5 |
Update on test_lgct_inrk_test_pt_202506 test_lgct_inrk_test_6 |
Update on test_lgct_inrk_test_pt_202507 test_lgct_inrk_test_7 |
Update on test_lgct_inrk_test_pt_202508 test_lgct_inrk_test_8 |
Update on test_lgct_inrk_test_pt_202509 test_lgct_inrk_test_9 |
Update on test_lgct_inrk_test_pt_202510 test_lgct_inrk_test_10 |
Update on test_lgct_inrk_test_pt_202511 test_lgct_inrk_test_11 |
Update on test_lgct_inrk_test_pt_202512 test_lgct_inrk_test_12 |
-> Index Scan using test_lgct_inrk_test_pt_202501_lgct_inrk_dtm_or_no_idx on test_lgct_inrk_test_pt_202501 test_lgct_inrk_test_1 (cost=0.44..8.47 rows=1 width=310) (actual time=0.011..0.011 rows=0 loops=1) |
Index Cond: ((lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)) AND ((or_no)::text = '2025033013890'::text)) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202502_lgct_inrk_dtm_or_no_idx on test_lgct_inrk_test_pt_202502 test_lgct_inrk_test_2 (cost=0.44..8.47 rows=1 width=310) (actual time=0.012..0.012 rows=0 loops=1) |
Index Cond: ((lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)) AND ((or_no)::text = '2025033013890'::text)) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text)) |
-> Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_3 (cost=0.43..51.07 rows=1 width=310) (actual time=0.026..0.042 rows=7 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_4 (cost=0.29..27.98 rows=1 width=310) (actual time=0.018..0.018 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202505_or_no_or_seq_idx on test_lgct_inrk_test_pt_202505 test_lgct_inrk_test_5 (cost=0.14..8.19 rows=1 width=354) (actual time=0.012..0.012 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202506_or_no_or_seq_idx on test_lgct_inrk_test_pt_202506 test_lgct_inrk_test_6 (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202507_or_no_or_seq_idx on test_lgct_inrk_test_pt_202507 test_lgct_inrk_test_7 (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202508_or_no_or_seq_idx on test_lgct_inrk_test_pt_202508 test_lgct_inrk_test_8 (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202509_or_no_or_seq_idx on test_lgct_inrk_test_pt_202509 test_lgct_inrk_test_9 (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202510_or_no_or_seq_idx on test_lgct_inrk_test_pt_202510 test_lgct_inrk_test_10 (cost=0.14..8.19 rows=1 width=354) (actual time=0.013..0.013 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202511_or_no_or_seq_idx on test_lgct_inrk_test_pt_202511 test_lgct_inrk_test_11 (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
-> Index Scan using test_lgct_inrk_test_pt_202512_or_no_or_seq_idx on test_lgct_inrk_test_pt_202512 test_lgct_inrk_test_12 (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
Planning Time: 18.737 ms |
Execution Time: 0.962 ms |partition pruning이 되지 않는다.
여기 저기 검색해보니, postgreSQL의 경우 update시 partition pruning 제약 사항이 상당히 있어 보인다.
서브 쿼리 X
함수 사용 X
예약어 X
explain analyze
update dis01.test_lgct_inrk_test
set ls_updr_id = 'ADMIN'
, ls_updt_dtm = now()
, lgct_rcpt_no = '3654573'
, lgct_rcpt_dtm = now()
, lgct_inrk_rslt_cd = '9999'
where 1=1
and or_no = '2025033013890'
and coalesce(or_cl_no,'') = coalesce(null,'')
and dlv_fom_cd = '01'
and lgct_inrk_dtm between '20250330'::timestamp
and '20250330'::timestamp + interval '5 day';
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Update on test_lgct_inrk_test (cost=0.43..78.82 rows=2 width=310) (actual time=0.371..0.372 rows=0 loops=1) |
Update on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_1 |
Update on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_2 |
-> Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_1 (cost=0.43..50.89 rows=1 width=310) (actual time=0.027..0.040 rows=7 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: ((lgct_inrk_dtm >= '2025-03-30 00:00:00'::timestamp without time zone) AND (lgct_inrk_dtm <= '2025-04-04 00:00:00'::timestamp without time zone) AND ((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))|
-> Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_2 (cost=0.29..27.92 rows=1 width=310) (actual time=0.013..0.014 rows=0 loops=1) |
Index Cond: ((or_no)::text = '2025033013890'::text) |
Filter: ((lgct_inrk_dtm >= '2025-03-30 00:00:00'::timestamp without time zone) AND (lgct_inrk_dtm <= '2025-04-04 00:00:00'::timestamp without time zone) AND ((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))|
Planning Time: 0.375 ms |
Execution Time: 0.407 ms |
.