TBASE执行慢SQL和DN上直接执行效率差异很大 about tbase HOT 4 OPEN

附相关DDL：
A.CREATE TABLE public.hscs_itf_imp_headers
(
header_id bigint NOT NULL DEFAULT nextval('hscs_itf_imp_headers_header_id_seq'::regclass),
source_system_code character varying(30) COLLATE pg_catalog."default",
batch_num character varying(100) COLLATE pg_catalog."default",
interface_name character varying(240) COLLATE pg_catalog."default",
module_code character varying(30) COLLATE pg_catalog."default" NOT NULL,
。。。
CONSTRAINT idx_73093_primary PRIMARY KEY (header_id)
）大概50个字段;shard by header_id
CREATE INDEX idx_73093_hscs_itf_imp_headers_n1
ON public.hscs_itf_imp_headers USING btree
(itf_header_id);
CREATE INDEX idx_73093_hscs_itf_imp_headers_n2
ON public.hscs_itf_imp_headers USING btree
(batch_num COLLATE pg_catalog."default");
CREATE INDEX idx_73093_hscs_itf_imp_headers_n3
ON public.hscs_itf_imp_headers USING btree
(interface_name COLLATE pg_catalog."default", import_status COLLATE pg_catalog."default");
CREATE INDEX idx_73093_hscs_itf_imp_headers_n4
ON public.hscs_itf_imp_headers USING btree
(batch_num COLLATE pg_catalog."default", source_system_code COLLATE pg_catalog."default", interface_name COLLATE pg_catalog."default", module_code COLLATE pg_catalog."default");

B.CREATE TABLE public.hscs_itf_imp_lines
(
line_id bigint NOT NULL DEFAULT nextval('hscs_itf_imp_lines_line_id_seq'::regclass),
header_id numeric NOT NULL,
source_iterface_id numeric NOT NULL,
...
CONSTRAINT idx_73159_primary PRIMARY KEY (line_id, header_id)
) shard by header_id,大概280个字段，其中200多个是TEXT字段
CREATE INDEX idx_73159_hscs_itf_imp_lines_n1
ON public.hscs_itf_imp_lines USING btree
(header_id, import_status COLLATE pg_catalog."default");
CREATE INDEX idx_73159_hscs_itf_imp_lines_n2
ON public.hscs_itf_imp_lines USING btree
(value2 COLLATE pg_catalog."default", value21 COLLATE pg_catalog."default");
CREATE INDEX idx_73159_hscs_itf_imp_lines_n3
ON public.hscs_itf_imp_lines USING btree
(value2 COLLATE pg_catalog."default");
CREATE INDEX idx_73159_hscs_itf_imp_lines_n4
ON public.hscs_itf_imp_lines USING btree
(source_iterface_id);
CREATE INDEX idx_73159_hscs_itf_imp_lines_n5
ON public.hscs_itf_imp_lines USING btree
(attribute10 COLLATE pg_catalog."default");
CREATE INDEX idx_73159_hscs_itf_imp_lines_n6
ON public.hscs_itf_imp_lines USING btree
(header_id, value2 COLLATE pg_catalog."default", import_status COLLATE pg_catalog."default", process_status COLLATE pg_catalog."default");
C.CREATE TABLE public.yxhscs_itf_ar_interface
(
ar_interface_id bigint NOT NULL DEFAULT nextval('yxhscs_itf_ar_interface_ar_interface_id_seq'::regclass),
accounting_status character varying(30) COLLATE pg_catalog."default",
accounting_date timestamp without time zone,
...
CONSTRAINT idx_76128_primary PRIMARY KEY (ar_interface_id, apply_num)
)PARTITION BY range(accounting_date)
begin (timestamp without time zone '2015-06-01 0:0:0')
step(interval '2 month') partitions(32)
distribute by shard(apply_num);大概120个字段,按日期类型做了分区,shard by apply_num(合同号)
CREATE UNIQUE INDEX idx_76128_yxhscs_itf_ar_interface_n1
ON public.yxhscs_itf_ar_interface USING btree
(apply_num COLLATE pg_catalog."default", serial_number COLLATE pg_catalog."default", accounting_date)
CREATE INDEX idx_76128_yxhscs_itf_ar_interface_n10
ON public.yxhscs_itf_ar_interface USING btree
(i_income_period, accounting_status COLLATE pg_catalog."default", acc_entity COLLATE pg_catalog."default")
CREATE INDEX idx_76128_yxhscs_itf_ar_interface_n2
ON public.yxhscs_itf_ar_interface USING btree
(acc_entity COLLATE pg_catalog."default")
CREATE INDEX idx_76128_yxhscs_itf_ar_interface_n3
ON public.yxhscs_itf_ar_interface USING btree
(payment_status COLLATE pg_catalog."default")
CREATE INDEX idx_76128_yxhscs_itf_ar_interface_n4
ON public.yxhscs_itf_ar_interface USING btree
(serial_number COLLATE pg_catalog."default")
CREATE INDEX idx_76128_yxhscs_itf_ar_interface_n5
ON public.yxhscs_itf_ar_interface USING btree
(apply_num COLLATE pg_catalog."default", i_income_period)
CREATE INDEX idx_76128_yxhscs_itf_ar_interface_n6
ON public.yxhscs_itf_ar_interface USING btree
(income_period COLLATE pg_catalog."default", payment_status COLLATE pg_catalog."default")
CREATE INDEX idx_76128_yxhscs_itf_ar_interface_n7
ON public.yxhscs_itf_ar_interface USING btree
(i_income_period, attribute10 COLLATE pg_catalog."default")
CREATE INDEX idx_76128_yxhscs_itf_ar_interface_n8
ON public.yxhscs_itf_ar_interface USING btree
(unique_code COLLATE pg_catalog."default")
CREATE INDEX idx_76128_yxhscs_itf_ar_interface_n9
ON public.yxhscs_itf_ar_interface USING btree
(accounting_date)

from tbase.

@windtalkerbj 也碰见了同样的问题，DN上没有找到vacuum_delta参数，如何配置这个参数，感谢!

from tbase.

1、你的集群拓扑结构是啥样？CN上的详细执行计划是啥样？
2、可以在CN上人工执行analyze更新统计信息，按照你的说法，看起来是你选择的分布建导致数据有倾斜，统计信息收集不全，后续通过CN的sql在数据重分布时的数据量过大，消耗时间过长

from tbase.

@windtalkerbj @ypma
另外可以尝试在配置文件中将 enable_sampling_analyze设置 为false试试

from tbase.