gporca occurs exception when using statistic from numeric(30, 8) column to optimize plan
chenbaggio opened this issue · 0 comments
chenbaggio commented
- gpdb version
gpdb_6x_stable debug版
- gporca version
gpdb_6x_stable 内置
- 问题描述
对两个表做EXPLAIN, LEFT JOIN 并设立了一个numeric(30,8)列的等值条件, 但是在优化过程中在
函数
gpopt::CExpressionHandle::DeriveRootStats
中会出现异常，具体语句为
pstatsRoot = popLogical->PstatsDerive(m_mp, *this, stats_ctxt);
基本判断是内存有被破坏
- 结果显示
QUERY PLAN
---------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice2; segments: 2) (cost=4163.25..44181.15 rows=136890 width=2340)
-> Hash Right Join (cost=4163.25..44181.15 rows=68445 width=2340)
Hash Cond: (t.ccypd_8 = s.ccypd_8)
-> Broadcast Motion 2:2 (slice1; segments: 2) (cost=0.00..4368.00 rows=11700 width=26)
-> Append (cost=0.00..4017.00 rows=5850 width=26)
-> Seq Scan on jt2_1_prt_1 t (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_2 t_1 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_3 t_2 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_4 t_3 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_5 t_4 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_6 t_5 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_7 t_6 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_8 t_7 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_9 t_8 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_10 t_9 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_11 t_10 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_12 t_11 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_13 t_12 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_14 t_13 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_15 t_14 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_16 t_15 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_17 t_16 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_18 t_17 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_19 t_18 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_20 t_19 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_21 t_20 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_22 t_21 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_23 t_22 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_24 t_23 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_25 t_24 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_26 t_25 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_27 t_26 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_28 t_27 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_29 t_28 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_30 t_29 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_31 t_30 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_32 t_31 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_33 t_32 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_34 t_33 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_35 t_34 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_36 t_35 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_37 t_36 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_38 t_37 (cost=0.00..103.00 rows=150 width=26)
-> Seq Scan on jt2_1_prt_39 t_38 (cost=0.00..103.00 rows=150 width=26)
-> Hash (cost=4017.00..4017.00 rows=5850 width=2366)
-> Append (cost=0.00..4017.00 rows=5850 width=2366)
-> Seq Scan on jt1_1_prt_1 s (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_2 s_1 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_3 s_2 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_4 s_3 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_5 s_4 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_6 s_5 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_7 s_6 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_8 s_7 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_9 s_8 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_10 s_9 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_11 s_10 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_12 s_11 (cost=0.00..103.00 rows=150 width=2366)
-> Seq Scan on jt1_1_prt_13 s_12 (cost=0.00..103.00 rows=150 width=2366)
而换成其它列做条件则恢复正常
- 重现步骤
（1）创建表
CREATE TABLE jt1
(
data_date date not null default '1900-01-01'::date,
br varchar(2) not null default '',
dealno varchar(8) not null default '',
seq varchar(2) not null default '',
ng_cost varchar(9) not null default '',
opics_cost varchar(10) not null default '',
cost_remark varchar(300) not null default '',
trad varchar(4) not null default '',
trdr_id varchar(8) not null default '',
trdr_nm varchar(200) not null default '',
accnm varchar(600) not null default '',
vdate date not null default '1900-01-01'::date,
cust varchar(11) not null default '',
cst_id varchar(18) not null default '',
cat_keywrd varchar(200) not null default '',
brok varchar(11) not null default '',
brok_id varchar(10) not null default '',
brockccy varchar(3) not null default '',
brokamt numeric(30,4) not null default 0,
phonci varchar(1) not null default '',
port varchar(4) not null default '',
cost varchar(11) not null default '',
odate date not null default '1900-01-01'::date,
dealdate date not null default '1900-01-01'::date,
dealtime varchar(8) not null default '',
loper varchar(4) not null default '',
voper varchar(4) not null default '',
brokcdate date not null default '1900-01-01'::date,
custcdate date not null default '1900-01-01'::date,
phonecdate date not null default '1900-01-01'::date,
dealtext varchar(70) not null default '',
phonetext varchar(70) not null default '',
ps varchar(1) not null default '',
prodcode varchar(6) not null default '',
verind varchar(1) not null default '',
revtext varchar(70) not null default '',
swapdeal varchar(8) not null default '' ,
farnearind varchar(1) not null default '',
payauthind varchar(1) not null default '',
swapvdate date not null default '1900-01-01'::date,
revreason varchar(2) not null default '',
inputdate date not null default '1900-01-01'::date,
inputtime varchar(8) not null default '',
dealsrce varchar(10) not null default '',
origocamt numeric(30,4) not null default 0,
trcedate date not null default '1900-01-01'::date,
tracecnt varchar(3) not null default '',
payoper varchar(4) not null default '',
lstmntdate date not null default '1900-01-01'::date,
ccy varchar(3) not null default '',
ccyamt numeric(30,4) not null default 0,
ccyterms varchar(1) not null default '',
ccyrate_8 numeric(30,8) not null default 0,
ccypd_8 numeric(30,8) not null default 0,
ccybamt numeric(30,4) not null default 0,
ccybrate_8 numeric(30, 8) not null default 0,
ccybpd_8 numeric(30, 8) not null default 0,
baseterms varchar(1) not null default '',
netsi varchar(1) not null default '',
spotfwdind varchar(1) not null default '',
netdeal varchar(11) not null default '',
ccysmeans varchar(7) not null default '',
ccysacct varchar(15) not null default '',
ctrsmeans varchar(7) not null default '',
ctrsacct varchar(15) not null default '',
ctrccy varchar(3) not null default '',
cramt numeric(30,4) not null default 0,
ctrrate_8 numeric(30, 8) not null default 0,
ctrpd_8 numeric(30, 8) not null default 0,
ctrbamt numeric(30, 4) not null default 0,
ctrbrate_8 numeric(30, 8) not null default 0,
nsrnoccy varchar(8) not null default '',
nsrccyseq varchar(4) not null default '',
nsrnoctr varchar(8) not null default '',
nsrctrseq varchar(4) not null default '',
brprcindte date not null default '1900-01-01'::date,
recoper varchar(4) not null default '',
payauthdte date not null default '1900-01-01'::date,
recauthdte date not null default '1900-01-01'::date,
verdate date not null default '1900-01-01'::date,
revdate date not null default '1900-01-01'::date,
ccyrevalamt numeric(30,4) not null default 0,
ctrrevalamt numeric(30,4) not null default 0,
ccynpvamt numeric(30, 4) not null default 0,
ctrnpvamt numeric(30, 4) not null default 0,
recauthoper varchar(4) not null default '',
payauthoper varchar(4) not null default '',
siind varchar(1) not null default '',
prodtype varchar(2) not null default '',
spottrad varchar(4) not null default '',
spotport varchar(4) not null default '',
spotcost varchar(10) not null default '',
internalrate_8 numeric(30,8) not null default 0,
corpspread_8 numeric(30, 8) not null default 0,
corpcost varchar(10) not null default '',
brspread_8 numeric(30, 8) not null default 0,
brcost varchar(10) not null default '',
tenor varchar(2) not null default '',
ccyserrdate date not null default '1900-01-01'::date,
ctrserrdate date not null default '1900-01-01'::date,
custenteredind varchar(1) not null default '',
linkdealno varchar(8) not null default '',
linkproduct varchar(6) not null default '',
linkprodtype varchar(2) not null default '',
fincntr1 varchar(10) not null default '',
fincntr2 varchar(10) not null default '',
commamt numeric(30,4) not null default 0,
excoveralllim varchar(1) not null default '',
exccustlim varchar(1) not null default '',
corpccy varchar(3) not null default '',
corpport varchar(4) not null default '',
corptrad varchar(4) not null default '',
revdealno varchar(8) not null default '',
optionind varchar(1) not null default '',
marginamt numeric(30,4) not null default 0,
marginccy varchar(3) not null default '',
corpspreadamt numeric(30,4) not null default 0,
spotccy varchar(3) not null default '',
spotind varchar(1) not null default '',
tenorexceedind varchar(1) not null default '',
ccysettstatus varchar(1) not null default '',
ctrsettstatus varchar(1) not null default '',
custrefno varchar(35) not null default '',
ccyfaildate date not null default '1900-01-01'::date,
ccycleardate date not null default '1900-01-01'::date,
ccyfailoper varchar(4) not null default '',
ccyclearoper varchar(4) not null default '',
ctrfaildate date not null default '1900-01-01'::date,
ctrcleardate date not null default '1900-01-01'::date,
ctrcfailoper varchar(4) not null default '',
ctrclearoper varchar(4) not null default '',
marginfeeamt numeric(30, 4) not null default 0,
marginfeeccy varchar(3) not null default '',
marginfeesettmeans varchar(7) not null default '',
marginfeesettacct varchar(15) not null default '',
marginfeerebamt numeric(30,4) not null default 0,
marginfeerebccy varchar(3) not null default '',
marginfeerebsettmeans varchar(7) not null default '',
marginfeerebsettacct varchar(15) not null default '',
phoneconfoper varchar(4) not null default '',
phoneconftime varchar(8) not null default '',
swiftmatchind varchar(1) not null default '',
siindpay varchar(1) not null default '',
commccy varchar(3) not null default '',
rexratetolind varchar(1) not null default '',
dealnoexcess varchar(8) not null default '',
fixrateind varchar(1) not null default '',
fixratecode varchar(7) not null default '',
revoper varchar(4) not null default '',
ctrpartydealno varchar(8) not null default '',
ctrpartyphoneno varchar(20) not null default '',
ctrpartyphoneoper varchar(4) not null default '',
blockind varchar(4) not null default '',
confcreatedind varchar(1) not null default '',
spotdate date not null default '1900-01-01'::date,
disaggccy varchar(3) not null default '',
disaggspottrad varchar(4) not null default '',
disaggspotport varchar(4) not null default '',
disaggspotcost varchar(10) not null default '',
disaggfwdtrad varchar(4) not null default '',
disaggfwdport varchar(4) not null default '',
disaggfwdcost varchar(10) not null default '',
fwdpremamt numeric(30,4) not null default 0,
amenddate date not null default '1900-01-01'::date,
orgid varchar(9) not null default '',
s_rate_8 numeric(30,8) not null default 0,
p_rate_8 numeric(30,8) not null default 0,
fix_cost_exrt numeric(30,8) not null default 0,
acctngtype varchar(10) not null default '',
cst_shrtnm varchar(30) not null default '',
cst_fullnm varchar(150) not null default '',
sn varchar(120) not null default '',
sic varchar(10) not null default '',
ccode varchar(2) not null default '',
uccode varchar(2) not null default '',
nddfixdate date not null default '1900-01-01'::date,
ps_near varchar(1) not null default '',
dealdate_near date not null default '1900-01-01'::date,
ccy_near varchar(3) not null default '',
ccyamt_near numeric(30, 4) not null default 0,
ccybamt_near numeric(30, 4) not null default 0,
ccyrate_8_near numeric(30, 8) not null default 0,
ctrccy_near varchar(3) not null default '',
ctramt_near numeric(30, 4) not null default 0,
ctrbamt_near numeric(30, 4) not null default 0,
ctrrate_8_near numeric(30, 8) not null default 0,
vdate_near date not null default '1900-01-01'::date,
bracctngtype varchar(10) not null default '',
txn_rcrd_id varchar(10) not null default '',
sfrn_fnmktn_ar_id varchar(23) not null default '',
fst_splt_pos_accnm varchar(240) not null default '',
fnmkcnccy1_amt numeric(31,16) not null default 0,
fnmkcnccy2_amt numeric(31,16) not null default 0,
fnmkcnccy1_nrend_amt numeric(31,16) not null default 0,
fnmkcnccy1_dstl_amt numeric(31,16) not null default 0,
fnmkcnccy2_nrend_amt numeric(31,16) not null default 0,
fnmkcnccy2_dstl_amt numeric(31,16) not null default 0,
dstl_prc numeric(31,16) not null default 0,
nred_prc numeric(31,16) not null default 0,
fnmktccy1cnvr_usd_amt numeric(31,16),
fnmktccy2cnvr_usd_amt numeric(31,16),
fnmktccy1nrecnrusdamt numeric(31,16),
fnmktccy2nrecnrusdamt numeric(31,16),
fnmktccy1dstlcrusdamt numeric(31,16),
fnmktccy2dstlcrusdamt numeric(31,16),
fwd_tpcd varchar(2) not null default '',
swap_tpcd varchar(1) not null default '',
cptr_amt_ind varchar(1) not null default '',
spot_exrt numeric(19,12) not null default 0,
data_upd_date date not null default '1900-01-01'::date,
multi_tenancy_id varchar(32) not null default '',
br_splt_idr varchar(32) not null default '',
etl_branch_id varchar(32) not null default '',
fisrtload_bsn_dt date not null default '1900-01-01'::date,
etl_fisrtload_script_nm varchar(64) not null default '',
etl_udt_script_nm varchar(64) not null default '',
rep_num_rcrd varchar(250) not null default ''
)with(appendonly=true, blocksize=1048576, checksum=true, compresstype=zlib, compresslevel=5)
distributed by(dealno)
partition by list(etl_branch_id)
(
values('010000000'),
values('710010000'),
values('710011000'),
values('710013000'),
values('710014000'),
values('710016000'),
values('710017000'),
values('710018000'),
values('710019000'),
values('710020000'),
values('710021000'),
values('710022000'),
values('710023000'),
values('710024000'),
values('710025000'),
values('710026000'),
values('710027000'),
values('710028000'),
values('710029000'),
values('710030000'),
values('710031000'),
values('710032000'),
values('710034000'),
values('710035000'),
values('710037000'),
values('710038000'),
values('710039000'),
values('710040000'),
values('710042000'),
values('710043000'),
values('710044000'),
values('710045000'),
values('710046000'),
values('710050000'),
values('710060000'),
values('710070000'),
values('710080000'),
values('710086000'),
values('710090000')
);
CREATE TABLE jt2
(
br varchar(2) not null default '',
dealno varchar(8) not null default '',
seq varchar(2) not null default '',
ng_cost varchar(9) not null default '',
opics_cost varchar(10) not null default '',
cost_remark varchar(300) not null default '',
trad varchar(4) not null default '',
trdr_id varchar(8) not null default '',
trdr_nm varchar(200) not null default '',
accnm varchar(600) not null default '',
vdate date not null default '1900-01-01'::date,
cust varchar(11) not null default '',
cst_id varchar(18) not null default '',
cat_keywrd varchar(200) not null default '',
brok varchar(11) not null default '',
brok_id varchar(10) not null default '',
brockccy varchar(3) not null default '',
brokamt numeric(30,4) not null default 0,
phonci varchar(1) not null default '',
port varchar(4) not null default '',
cost varchar(11) not null default '',
odate date not null default '1900-01-01'::date,
dealdate date not null default '1900-01-01'::date,
dealtime varchar(8) not null default '',
loper varchar(4) not null default '',
voper varchar(4) not null default '',
brokcdate date not null default '1900-01-01'::date,
custcdate date not null default '1900-01-01'::date,
phonecdate date not null default '1900-01-01'::date,
dealtext varchar(70) not null default '',
phonetext varchar(70) not null default '',
ps varchar(1) not null default '',
prodcode varchar(6) not null default '',
verind varchar(1) not null default '',
revtext varchar(70) not null default '',
swapdeal varchar(8) not null default '' ,
farnearind varchar(1) not null default '',
payauthind varchar(1) not null default '',
swapvdate date not null default '1900-01-01'::date,
revreason varchar(2) not null default '',
inputdate date not null default '1900-01-01'::date,
inputtime varchar(8) not null default '',
dealsrce varchar(10) not null default '',
origocamt numeric(30,4) not null default 0,
trcedate date not null default '1900-01-01'::date,
tracecnt varchar(3) not null default '',
payoper varchar(4) not null default '',
lstmntdate date not null default '1900-01-01'::date,
ccy varchar(3) not null default '',
ccyamt numeric(30,4) not null default 0,
ccyterms varchar(1) not null default '',
ccyrate_8 numeric(30,8) not null default 0,
ccypd_8 numeric(30,8) not null default 0,
ccybamt numeric(30,4) not null default 0,
ccybrate_8 numeric(30, 8) not null default 0,
ccybpd_8 numeric(30, 8) not null default 0,
baseterms varchar(1) not null default '',
netsi varchar(1) not null default '',
spotfwdind varchar(1) not null default '',
netdeal varchar(11) not null default '',
ccysmeans varchar(7) not null default '',
ccysacct varchar(15) not null default '',
ctrsmeans varchar(7) not null default '',
ctrsacct varchar(15) not null default '',
ctrccy varchar(3) not null default '',
cramt numeric(30,4) not null default 0,
ctrrate_8 numeric(30, 8) not null default 0,
ctrpd_8 numeric(30, 8) not null default 0,
ctrbamt numeric(30, 4) not null default 0,
ctrbrate_8 numeric(30, 8) not null default 0,
nsrnoccy varchar(8) not null default '',
nsrccyseq varchar(4) not null default '',
nsrnoctr varchar(8) not null default '',
nsrctrseq varchar(4) not null default '',
brprcindte date not null default '1900-01-01'::date,
recoper varchar(4) not null default '',
payauthdte date not null default '1900-01-01'::date,
recauthdte date not null default '1900-01-01'::date,
verdate date not null default '1900-01-01'::date,
revdate date not null default '1900-01-01'::date,
ccyrevalamt numeric(30,4) not null default 0,
ctrrevalamt numeric(30,4) not null default 0,
ccynpvamt numeric(30, 4) not null default 0,
ctrnpvamt numeric(30, 4) not null default 0,
recauthoper varchar(4) not null default '',
payauthoper varchar(4) not null default '',
siind varchar(1) not null default '',
prodtype varchar(2) not null default '',
spottrad varchar(4) not null default '',
spotport varchar(4) not null default '',
spotcost varchar(10) not null default '',
internalrate_8 numeric(30,8) not null default 0,
corpspread_8 numeric(30, 8) not null default 0,
corpcost varchar(10) not null default '',
brspread_8 numeric(30, 8) not null default 0,
brcost varchar(10) not null default '',
tenor varchar(2) not null default '',
ccyserrdate date not null default '1900-01-01'::date,
ctrserrdate date not null default '1900-01-01'::date,
custenteredind varchar(1) not null default '',
linkdealno varchar(8) not null default '',
linkproduct varchar(6) not null default '',
linkprodtype varchar(2) not null default '',
fincntr1 varchar(10) not null default '',
fincntr2 varchar(10) not null default '',
commamt numeric(30,4) not null default 0,
excoveralllim varchar(1) not null default '',
exccustlim varchar(1) not null default '',
corpccy varchar(3) not null default '',
corpport varchar(4) not null default '',
corptrad varchar(4) not null default '',
revdealno varchar(8) not null default '',
optionind varchar(1) not null default '',
marginamt numeric(30,4) not null default 0,
marginccy varchar(3) not null default '',
corpspreadamt numeric(30,4) not null default 0,
spotccy varchar(3) not null default '',
spotind varchar(1) not null default '',
tenorexceedind varchar(1) not null default '',
ccysettstatus varchar(1) not null default '',
ctrsettstatus varchar(1) not null default '',
custrefno varchar(35) not null default '',
ccyfaildate date not null default '1900-01-01'::date,
ccycleardate date not null default '1900-01-01'::date,
ccyfailoper varchar(4) not null default '',
ccyclearoper varchar(4) not null default '',
ctrfaildate date not null default '1900-01-01'::date,
ctrcleardate date not null default '1900-01-01'::date,
ctrcfailoper varchar(4) not null default '',
ctrclearoper varchar(4) not null default '',
marginfeeamt numeric(30, 4) not null default 0,
marginfeeccy varchar(3) not null default '',
marginfeesettmeans varchar(7) not null default '',
marginfeesettacct varchar(15) not null default '',
marginfeerebamt numeric(30,4) not null default 0,
marginfeerebccy varchar(3) not null default '',
marginfeerebsettmeans varchar(7) not null default '',
marginfeerebsettacct varchar(15) not null default '',
phoneconfoper varchar(4) not null default '',
phoneconftime varchar(8) not null default '',
swiftmatchind varchar(1) not null default '',
siindpay varchar(1) not null default '',
commccy varchar(3) not null default '',
rexratetolind varchar(1) not null default '',
dealnoexcess varchar(8) not null default '',
fixrateind varchar(1) not null default '',
fixratecode varchar(7) not null default '',
revoper varchar(4) not null default '',
ctrpartydealno varchar(8) not null default '',
ctrpartyphoneno varchar(20) not null default '',
ctrpartyphoneoper varchar(4) not null default '',
blockind varchar(4) not null default '',
confcreatedind varchar(1) not null default '',
spotdate date not null default '1900-01-01'::date,
disaggccy varchar(3) not null default '',
disaggspottrad varchar(4) not null default '',
disaggspotport varchar(4) not null default '',
disaggspotcost varchar(10) not null default '',
disaggfwdtrad varchar(4) not null default '',
disaggfwdport varchar(4) not null default '',
disaggfwdcost varchar(10) not null default '',
fwdpremamt numeric(30,4) not null default 0,
amenddate date not null default '1900-01-01'::date,
orgid varchar(9) not null default '',
s_rate_8 numeric(30,8) not null default 0,
p_rate_8 numeric(30,8) not null default 0,
fix_cost_exrt numeric(30,8) not null default 0,
acctngtype varchar(10) not null default '',
cst_shrtnm varchar(30) not null default '',
cst_fullnm varchar(150) not null default '',
sn varchar(120) not null default '',
sic varchar(10) not null default '',
ccode varchar(2) not null default '',
uccode varchar(2) not null default '',
nddfixdate date not null default '1900-01-01'::date,
ps_near varchar(1) not null default '',
dealdate_near date not null default '1900-01-01'::date,
ccy_near varchar(3) not null default '',
ccyamt_near numeric(30, 4) not null default 0,
ccybamt_near numeric(30, 4) not null default 0,
ccyrate_8_near numeric(30, 8) not null default 0,
ctrccy_near varchar(3) not null default '',
ctramt_near numeric(30, 4) not null default 0,
ctrbamt_near numeric(30, 4) not null default 0,
ctrrate_8_near numeric(30, 8) not null default 0,
vdate_near date not null default '1900-01-01'::date,
bracctngtype varchar(10) not null default '',
txn_rcrd_id varchar(10) not null default '',
sfrn_fnmktn_ar_id varchar(23) not null default '',
fst_splt_pos_accnm varchar(240) not null default '',
fnmkcnccy1_amt numeric(31,16) not null default 0,
fnmkcnccy2_amt numeric(31,16) not null default 0,
fnmkcnccy1_nrend_amt numeric(31,16) not null default 0,
fnmkcnccy1_dstl_amt numeric(31,16) not null default 0,
fnmkcnccy2_nrend_amt numeric(31,16) not null default 0,
fnmkcnccy2_dstl_amt numeric(31,16) not null default 0,
dstl_prc numeric(31,16) not null default 0,
nred_prc numeric(31,16) not null default 0,
fnmktccy1cnvr_usd_amt numeric(31,16),
fnmktccy2cnvr_usd_amt numeric(31,16),
fnmktccy1nrecnrusdamt numeric(31,16),
fnmktccy2nrecnrusdamt numeric(31,16),
fnmktccy1dstlcrusdamt numeric(31,16),
fnmktccy2dstlcrusdamt numeric(31,16),
fwd_tpcd varchar(2) not null default '',
swap_tpcd varchar(1) not null default '',
cptr_amt_ind varchar(1) not null default '',
spot_exrt numeric(19,12) not null default 0,
data_upd_date date not null default '1900-01-01'::date,
multi_tenancy_id varchar(32) not null default '',
stdt date default '1900-01-01'::date,
etdt date default '2999-12-31'::date,
br_splt_idr varchar(32) not null default '',
etl_branch_id varchar(32) not null default '',
data_udt_date date not null default '1900-01-01'::date,
fisrtload_bsn_dt date not null default '1900-01-01'::date,
etl_fisrtload_script_nm varchar(64) not null default '',
etl_udt_script_nm varchar(64) not null default '',
rep_num_rcrd varchar(250) not null default '',
rcrd_del_dt date not null default '2999-12-31'::date
)with(appendonly=true, blocksize=1048576, checksum=true, compresstype=zlib, compresslevel=5)
distributed by(dealno, br)
partition by list(etl_branch_id)
(
values('010000000'),
values('710010000'),
values('710011000'),
values('710013000'),
values('710014000'),
values('710016000'),
values('710017000'),
values('710018000'),
values('710019000'),
values('710020000'),
values('710021000'),
values('710022000'),
values('710023000'),
values('710024000'),
values('710025000'),
values('710026000'),
values('710027000'),
values('710028000'),
values('710029000'),
values('710030000'),
values('710031000'),
values('710032000'),
values('710034000'),
values('710035000'),
values('710037000'),
values('710038000'),
values('710039000'),
values('710040000'),
values('710042000'),
values('710043000'),
values('710044000'),
values('710045000'),
values('710046000'),
values('710050000'),
values('710060000'),
values('710070000'),
values('710080000'),
values('710086000'),
values('710090000')
);
(2) 录入统计量
在配置文件中设置allow_system_table_mods为ON
Insert into pg_statistic values
('jt1'::regclass,54,true,0,3,8250,1,2,0,0,0,1752,1754,0,0,0,'{0.763123,0.00136175,0.00114556,0.000972548,0.000951166,0.000907833,0.000895823,0.0008427,0.00081705,0.000794928,0.000736872,0.000703898,0.000578242,0.000552099,0.0005447,0.000490837,0.00048006,0.000463289,0.000461562,0.000455643,0.00045515,0.000453917,0.000423656,0.000405314,0.000404493,0.0004003,0.000399067,0.000397834,0.000397587,0.000396847,0.000396354,0.000394628,0.000393394,0.000374478,0.000373245,0.000372259,0.000371766,0.000370039,0.000369792,0.00036634,0.00036634,0.00036486,0.000363133,0.000363133,0.000363133,0.000339531,0.000335832,0.000335585,0.000333859,0.000333366,0.000332872,0.000332872,0.000322342,0.000320369,0.00030853,0.000306064,0.000305817,0.000303104,0.000302611,0.000302611,0.000302611,0.000302611,0.000302611,0.000302611,0.000302611,0.000302611,0.000302611,0.000302611,0.000302611,0.000302611,0.000281722,0.000279502,0.000279502,0.000279256,0.000279256,0.000275951,0.00027531,0.000272843,0.00027235,0.00027235,0.00027235,0.00027235,0.00027235,0.00027235,0.00027235,0.0002559,0.000251461,0.000249488,0.000249241,0.000246528,0.000245049,0.000244802,0.000244309,0.000244062,0.000242089,0.000242089,0.000242089,0.000242089,0.000242089,0.000242089}',NULL,NULL,NULL,NULL,'{0.00000000,0.00000001,0.00030000,-0.00020000,-0.00030000,-0.00060000,0.00020000,0.00050000,0.00000002,-0.00040000,-0.00050000,0.00010000,0.00040000,0.00015000,0.00000004,0.00090000,0.00035000,0.00150000,-0.00035000,-0.00130000,-0.00090000,-0.00000001,-0.00400000,0.00001000,0.00060000,0.00350000,-0.00032000,0.00005000,-0.00005000,0.00080000,-0.00100000,0.01000000,0.00600000,0.00033000,0.00300000,0.00120000,0.00003000,0.00028000,0.00070000,0.00250000,0.00800000,0.00200000,-0.00025000,0.00000005,0.00400000,-0.00010000,-0.00080000,0.01300000,0.00014000,-0.00000600,-0.00000003,-0.00200000,-0.00001000,0.00025000,0.00027000,-0.00140000,-0.00110000,-0.00000200,0.02100000,0.01200000,0.03200000,0.02000000,-0.00120000,0.01050000,0.01150000,-0.00700000,-0.00024000,-0.00003500,-0.00500000,0.00500000,-0.00003000,-0.00038000,0.00100000,0.00032000,0.00036000,0.00002000,-0.00029000,0.00008000,-0.00085000,-0.01000000,0.01900000,-0.00150000,-0.00450000,0.01450000,0.00390000,-0.00048000,-0.00042000,0.00055000,-0.00001500,-0.00041000,0.00140000,0.00029000,-0.00039000,0.00000100,0.00230000,-0.00070000,0.00031000,-0.00000002,-0.00000800,0.00009000}'::numeric[],'{-20.35000000,-0.19040000,-0.11500000,-0.08403296,-0.06569230,-0.05010000,-0.03700000,-0.02800000,-0.02300000,-0.01700000,-0.01248994,-0.00936000,-0.00720000,-0.00527381,-0.00380000,-0.00301930,-0.00250000,-0.00210099,-0.00174900,-0.00142300,-0.00122200,-0.00098000,-0.00080616,-0.00066500,-0.00055000,-0.00045000,-0.00038500,-0.00033000,-0.00029195,-0.00024600,-0.00019882,-0.00016500,-0.00012100,-0.00008684,-0.00005976,-0.00003135,-0.00001400,-0.00000300,-0.00000049,-0.00000004,0.00000029,0.00000488,0.00002500,0.00006500,0.00011415,0.00016200,0.00022000,0.00029800,0.00038184,0.00047500,0.00061000,0.00075000,0.00092960,0.00122000,0.00148076,0.00180000,0.00215912,0.00266700,0.00319927,0.00370000,0.00435483,0.00511700,0.00590000,0.00680000,0.00783396,0.00890000,0.00985000,0.01130000,0.01284166,0.01440000,0.01575100,0.01710869,0.01860869,0.02138000,0.02321739,0.02522580,0.02783300,0.03000000,0.03300000,0.03583842,0.03853846,0.04154347,0.04450000,0.04800000,0.05190000,0.05500000,0.05900000,0.06300000,0.06750000,0.06900000,0.07374725,0.08100000,0.09000000,0.09708800,0.10370000,0.11152747,0.12800000,0.14572920,0.16950000,0.34510000,97.91800000}'::numeric[],NULL,NULL,NULL);
Insert into pg_statistic values
('jt2'::regclass,53,true,0,3,22011,1,2,0,0,0,1752,1754,0,0,0,'{0.62357,0.00172744,0.00154802,0.00152693,0.00147996,0.00144152,0.00135151,0.00134204,0.00133005,0.00128447,0.00126633,0.00125836,0.0012475,0.00124235,0.00121081,0.00119603,0.00118248,0.00115161,0.00114146,0.00112296,0.00111885,0.00110388,0.00110207,0.00109112,0.00108902,0.00106971,0.00106531,0.00103757,0.00102989,0.00100258,0.000991696,0.000982067,0.000975131,0.000964496,0.000958286,0.000953822,0.000952544,0.000945639,0.000937085,0.00091773,0.000908386,0.00090386,0.000901857,0.000900984,0.0008931,0.00088584,0.00088306,0.000857258,0.000848337,0.000839529,0.000834093,0.000827283,0.000823393,0.000813356,0.000811787,0.00079486,0.000785697,0.000781905,0.00077959,0.000764308,0.000749581,0.000722357,0.000701452,0.000699269,0.000698651,0.000690134,0.000655895,0.000652761,0.000648324,0.000648283,0.000638616,0.000638336,0.000635691,0.000627966,0.000617345,0.00061377,0.000586331,0.000573624,0.000558743,0.00055516,0.000553479,0.000550867,0.000546439,0.000544729,0.000543542,0.000529939,0.000511406,0.000505969,0.000504168,0.000504168,0.000494132,0.000491009,0.000485111,0.000482248,0.000482248,0.000462479,0.000460328,0.000460328,0.00044248,0.000438407}',NULL,NULL,NULL,NULL,'{0.00000000,0.03650000,0.03300000,0.01000000,0.01450000,0.04400000,0.00950000,0.04350000,0.02900000,0.03000000,0.00600000,0.01500000,0.03400000,0.04450000,0.02750000,0.01600000,0.01200000,0.03050000,0.01300000,0.01050000,0.02700000,0.01150000,0.02800000,0.03150000,0.03600000,0.01400000,0.02950000,0.00900000,0.03250000,0.03200000,0.02400000,0.04300000,0.03750000,0.04430000,0.02000000,0.01800000,0.02500000,0.03500000,0.04500000,0.00700000,0.03100000,0.01100000,0.03350000,0.00800000,0.02300000,0.02600000,0.03700000,0.02850000,0.05000000,0.03950000,0.00400000,0.03550000,0.01700000,0.01650000,0.00500000,0.03800000,0.05500000,0.02650000,0.02250000,0.00450000,0.03450000,0.04650000,0.04250000,0.00750000,0.02100000,0.02450000,0.06200000,0.02200000,0.01750000,0.04600000,0.04700000,0.03850000,0.01850000,0.02350000,0.00650000,0.01550000,0.01350000,0.02820000,0.04100000,0.04800000,0.00850000,0.02550000,0.04000000,0.00880000,0.04950000,0.04850000,0.10000000,0.00920000,0.04360000,0.05750000,0.00580000,0.03900000,-0.00100000,0.03680000,0.02830000,0.00550000,0.04440000,0.00980000,0.02530000,0.01060000}'::numeric[],'{-69.83337900,-0.15270000,-0.03500000,-0.02000000,-0.00850000,-0.00500000,-0.00281153,-0.00160000,-0.00085500,-0.00046600,-0.00030000,-0.00015500,-0.00001940,0.00000202,0.00006700,0.00012000,0.00028100,0.00044000,0.00067000,0.00093640,0.00118500,0.00165500,0.00224100,0.00294500,0.00350000,0.00410000,0.00476666,0.00560000,0.00640000,0.00720000,0.00807700,0.00870000,0.00958400,0.01020000,0.01101630,0.01173333,0.01250000,0.01370000,0.01460000,0.01530000,0.01610000,0.01670000,0.01740000,0.01817500,0.01900000,0.02040000,0.02188400,0.02280000,0.02380000,0.02470000,0.02570000,0.02641700,0.02720000,0.02890000,0.02960000,0.03048900,0.03140600,0.03230000,0.03307600,0.03380000,0.03540000,0.03652400,0.03740000,0.03843900,0.03970000,0.04120000,0.04240000,0.04380000,0.04500000,0.04605300,0.04730000,0.04844700,0.05054400,0.05300000,0.05410000,0.05600000,0.05800000,0.05950000,0.06117600,0.06376600,0.06550000,0.06650000,0.06820000,0.07090000,0.07200000,0.07610000,0.08100000,0.08700000,0.09300000,0.09780000,0.10300000,0.10930000,0.11900000,0.12740000,0.13650000,0.14940000,0.16510000,0.19300000,0.25700000,0.40218000,261.51000000}'::numeric[],NULL,NULL,NULL);
(3) 查询语句
Explain SELECT s.br, s.dealno, s.seq, s.ng_cost,s.opics_cost, s.cost_remark, s.trad, s.trdr_id, s.trdr_nm,
s.accnm, s.vdate, s.cust, s.cst_id, s.cat_keywrd, s.brok, s.brok_id, s.brockccy, s.brokamt, s.phonci, s.port, s.cost, to_date('20201203', 'YYYYMMDD')StDt, to_date('29991231', 'YYYYMMDD') EdDt,to_date('29991231', 'YYYYMMDD') Rcrd_Del_Dt,to_date('00010101', 'YYYYMMDD') DataUpdDt
FROM jt1 s LEFT JOIN jt2 t
ON t.ccypd_8=s.ccypd_8;
- 其它
在用户现场表现为内存消耗巨大，这也许和现场为release版有关