postgresql - Postgres - 更新性能下降

Question

有人可以帮助确定为什么以下过去需要 2 小时的语句在没有音量增加的情况下不需要 6 小时。

with P as 
(SELECT DISTINCT CD.CASE_DETAIL_ID, SVL.SERVICE_LEVEL_ID\n       
FROM report_fct CD LEFT JOIN SERVICE_LEVEL SVL ON SVL.ORDER_TYPE_CD =   CD.ORDER_TYPE_CD\n                                                            
AND SVL.SOURCE_ID = CD.SOURCE_ID\n                                                  AND SVL.AREA_ID = CD.HQ_AREA_ID\n                                                   AND SVL.CATEGORY_ID = CD.CATEGORY_ID\n                                              AND SVL.STATE_CD = CD.CUST_STATE\n       
WHERE CD.LINE_OF_BIZ = 'CLOTH'\n       
AND CD.HQ_AREA_ID is NOT NULL\n       
AND CD.SOURCE_ID is NOT NULL\n       
AND CD.CATEGORY_ID is NOT NULL\n       
AND CD.CUST_STATE is NOT NULL)\n
update report_fct rpt\n
set service_level_id = P.service_level_id\n
from P\n
where rpt.case_detail_id = P.case_detail_id;"}


CREATE TABLE report_fct 
...
..
case_detail_id bigint NOT NULL,
...



CREATE INDEX report_fct _ix1
ON report_fct USING btree
(case_detail_id)
TABLESPACE pg_default;


CREATE INDEX report_fct _ix2
    ON report_fct  USING btree
    (insert_dt)
TABLESPACE pg_default;

我的一个疑问是统计数据是否会在这张表上出现偏差，这会导致降级。

relname       inserts      updates  deletes live_tuples dead_tupes  last autovacuum last autoanalyze
report_fct  262746347   5387849450  0           2473523    3573914  5/19/20 3:38    5/19/20 1:13


EXPLAIN:
"Update on report_fct rpt  (cost=24847.47..27881.35 rows=415 width=3772)"
"  CTE p"
"    ->  Unique  (cost=24844.02..24847.05 rows=405 width=16)"
"          ->  Sort  (cost=24844.02..24845.03 rows=405 width=16)"
"                Sort Key: cd.case_detail_id, svl.service_level_id"
"                ->  Nested Loop Left Join  (cost=0.41..24826.48 rows=405 width=16)"
"                      ->  Seq Scan on report_fct cd  (cost=0.00..21915.21 rows=405 width=44)"
"                            Filter: ((hq_area_id IS NOT NULL) AND (source_id IS NOT NULL) AND (category_id IS NOT NULL) AND (cust_state IS NOT NULL) AND ((line_of_biz)::text = 'CLOTH'::text))"
"                      ->  Index Scan using service_level_unq on service_level svl  (cost=0.41..7.18 rows=1 width=45)"
"                            Index Cond: ((area_id = cd.hq_area_id) AND ((order_type_cd)::text = (cd.order_type_cd)::text) AND (source_id = cd.source_id) AND (state_cd = (cd.cust_state)::bpchar) AND (category_id = cd.category_id))"
"  ->  Nested Loop  (cost=0.41..3034.30 rows=415 width=3772)"
"        ->  CTE Scan on p  (cost=0.00..8.10 rows=405 width=56)"
"        ->  Index Scan using report_fct_ix1 on report_fct rpt  (cost=0.41..7.46 rows=1 width=3724)"
"              Index Cond: (case_detail_id = p.case_detail_id)"

标签： postgresql