TBASE执行慢SQL和DN上直接执行效率差异很大

1,慢SQL: EXPLAIN ANALYZE SELECT

FROM hscs_itf_imp_lines l JOIN hscs_itf_imp_headers h ON l.HEADER_ID = h.HEADER_ID WHERE l.ATTRIBUTE10 IS NULL AND h.INTERFACE_NAME IN ( 'MAL_MONPAY_INTERFACE' ) AND EXISTS ( SELECT apply_num FROM yxhscs_itf_ar_interface WHERE l.value2 = apply_num GROUP BY apply_num ) LIMIT 2000 hscs_itf_imp_lines大概8500W,hscs_itf_imp_headers 150W,yxhscs_itf_ar_interface 2900W 注：本SQL之前一直提示message:start timestamp XXXXX is too old to execute, recentCommitTs is XXXXX, 请假过企鹅的小伙伴后，提示在DN上修改 vacuum_delta 100 ->10000后，SQL可正确执行

2,该语句在TBASE的CN上执行，消耗40000秒，对应执行计划： A:Limit (cost=100.38..124.45 rows=1 width=20444) (actual time=40194212.346..40194212.346 rows=0 loops=1) -> Remote Subquery Scan on all (dn1,dn2) (cost=100.38..124.45 rows=1 width=20444) (actual time=40194212.335..40194212.335 rows=0 loops=1) Planning time: 332.126 ms Execution time: 40195371.888 ms

3,在DN1上执行，消耗233秒，对应执行计划： Limit (cost=3224.17..17945.65 rows=2000 width=9936) (actual time=233250.093..233250.093 rows=0 loops=1) -> Nested Loop Semi Join (cost=3224.17..30053846.59 rows=4082555 width=9936) (actual time=233250.089..233250.089 rows=0 loops=1) -> Gather (cost=3223.73..5268494.06 rows=4082555 width=9936) (actual time=1984.672..219937.263 rows=86 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Join (cost=2223.73..4859238.56 rows=1701065 width=9936) (actual time=5385.324..233230.741 rows=43 loops=2) Hash Cond: (l.header_id = (h.header_id)::numeric) -> Parallel Seq Scan on hscs_itf_imp_lines l (cost=0.00..2502785.23 rows=15628109 width=5198) (actual time=0.058..222744.610 rows=18741760 loops=2) Filter: (attribute10 IS NULL) Rows Removed by Filter: 1697783 -> Parallel Hash (cost=2216.35..2216.35 rows=591 width=4738) (actual time=26.879..26.879 rows=488 loops=2) -> Parallel Seq Scan on hscs_itf_imp_headers h (cost=0.00..2216.35 rows=591 width=4738) (actual time=0.673..25.803 rows=488 loops=2) Filter: ((interface_name)::text = 'MAL_MONPAY_INTERFACE'::text) Rows Removed by Filter: 31903 -> Merge Append (cost=0.43..273.77 rows=97 width=8) (actual time=154.785..154.785 rows=0 loops=86) Sort Key: yxhscs_itf_ar_interface.apply_num -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_0 on yxhscs_itf_ar_interface (partition sequence: 0, name: yxhscs_itf_ar_interface_part_0) (cost=0.43..8.56 rows=3 width=8) (actual time=0.015..0.015 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_1 on yxhscs_itf_ar_interface (partition sequence: 1, name: yxhscs_itf_ar_interface_part_1) (cost=0.43..8.56 rows=3 width=8) (actual time=0.009..0.009 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_2 on yxhscs_itf_ar_interface (partition sequence: 2, name: yxhscs_itf_ar_interface_part_2) (cost=0.43..8.56 rows=3 width=8) (actual time=0.008..0.008 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_3 on yxhscs_itf_ar_interface (partition sequence: 3, name: yxhscs_itf_ar_interface_part_3) (cost=0.43..8.56 rows=3 width=8) (actual time=0.344..0.344 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_4 on yxhscs_itf_ar_interface (partition sequence: 4, name: yxhscs_itf_ar_interface_part_4) (cost=0.43..8.56 rows=3 width=8) (actual time=0.572..0.572 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_5 on yxhscs_itf_ar_interface (partition sequence: 5, name: yxhscs_itf_ar_interface_part_5) (cost=0.43..8.56 rows=3 width=8) (actual time=1.457..1.457 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_6 on yxhscs_itf_ar_interface (partition sequence: 6, name: yxhscs_itf_ar_interface_part_6) (cost=0.43..8.56 rows=3 width=8) (actual time=2.399..2.399 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_7 on yxhscs_itf_ar_interface (partition sequence: 7, name: yxhscs_itf_ar_interface_part_7) (cost=0.43..8.56 rows=3 width=8) (actual time=3.124..3.124 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_8 on yxhscs_itf_ar_interface (partition sequence: 8, name: yxhscs_itf_ar_interface_part_8) (cost=0.43..8.56 rows=3 width=8) (actual time=3.969..3.969 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_9 on yxhscs_itf_ar_interface (partition sequence: 9, name: yxhscs_itf_ar_interface_part_9) (cost=0.43..8.56 rows=3 width=8) (actual time=3.977..3.977 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_10 on yxhscs_itf_ar_interface (partition sequence: 10, name: yxhscs_itf_ar_interface_part_10) (cost=0.43..8.56 rows=3 width=8) (actual time=3.258..3.258 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_11 on yxhscs_itf_ar_interface (partition sequence: 11, name: yxhscs_itf_ar_interface_part_11) (cost=0.43..8.56 rows=3 width=8) (actual time=3.630..3.630 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_12 on yxhscs_itf_ar_interface (partition sequence: 12, name: yxhscs_itf_ar_interface_part_12) (cost=0.43..8.56 rows=3 width=8) (actual time=9.651..9.651 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_13 on yxhscs_itf_ar_interface (partition sequence: 13, name: yxhscs_itf_ar_interface_part_13) (cost=0.43..8.56 rows=3 width=8) (actual time=5.094..5.094 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_14 on yxhscs_itf_ar_interface (partition sequence: 14, name: yxhscs_itf_ar_interface_part_14) (cost=0.43..8.56 rows=3 width=8) (actual time=9.988..9.988 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_15 on yxhscs_itf_ar_interface (partition sequence: 15, name: yxhscs_itf_ar_interface_part_15) (cost=0.43..8.56 rows=3 width=8) (actual time=8.599..8.599 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_16 on yxhscs_itf_ar_interface (partition sequence: 16, name: yxhscs_itf_ar_interface_part_16) (cost=0.43..8.56 rows=3 width=8) (actual time=6.085..6.085 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_17 on yxhscs_itf_ar_interface (partition sequence: 17, name: yxhscs_itf_ar_interface_part_17) (cost=0.43..8.56 rows=3 width=8) (actual time=5.871..5.871 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_18 on yxhscs_itf_ar_interface (partition sequence: 18, name: yxhscs_itf_ar_interface_part_18) (cost=0.43..8.56 rows=3 width=8) (actual time=6.907..6.907 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_19 on yxhscs_itf_ar_interface (partition sequence: 19, name: yxhscs_itf_ar_interface_part_19) (cost=0.43..8.56 rows=3 width=8) (actual time=5.857..5.857 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_20 on yxhscs_itf_ar_interface (partition sequence: 20, name: yxhscs_itf_ar_interface_part_20) (cost=0.43..8.56 rows=3 width=8) (actual time=9.075..9.075 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_21 on yxhscs_itf_ar_interface (partition sequence: 21, name: yxhscs_itf_ar_interface_part_21) (cost=0.43..8.56 rows=3 width=8) (actual time=5.314..5.314 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_22 on yxhscs_itf_ar_interface (partition sequence: 22, name: yxhscs_itf_ar_interface_part_22) (cost=0.43..8.56 rows=3 width=8) (actual time=5.416..5.416 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_23 on yxhscs_itf_ar_interface (partition sequence: 23, name: yxhscs_itf_ar_interface_part_23) (cost=0.43..8.56 rows=3 width=8) (actual time=5.927..5.927 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_24 on yxhscs_itf_ar_interface (partition sequence: 24, name: yxhscs_itf_ar_interface_part_24) (cost=0.43..8.56 rows=3 width=8) (actual time=4.862..4.862 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_25 on yxhscs_itf_ar_interface (partition sequence: 25, name: yxhscs_itf_ar_interface_part_25) (cost=0.43..8.56 rows=3 width=8) (actual time=6.522..6.522 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_26 on yxhscs_itf_ar_interface (partition sequence: 26, name: yxhscs_itf_ar_interface_part_26) (cost=0.43..8.56 rows=3 width=8) (actual time=9.027..9.027 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_27 on yxhscs_itf_ar_interface (partition sequence: 27, name: yxhscs_itf_ar_interface_part_27) (cost=0.43..8.56 rows=3 width=8) (actual time=7.632..7.632 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_28 on yxhscs_itf_ar_interface (partition sequence: 28, name: yxhscs_itf_ar_interface_part_28) (cost=0.43..8.56 rows=3 width=8) (actual time=5.757..5.757 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_29 on yxhscs_itf_ar_interface (partition sequence: 29, name: yxhscs_itf_ar_interface_part_29) (cost=0.43..8.56 rows=3 width=8) (actual time=6.805..6.805 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_30 on yxhscs_itf_ar_interface (partition sequence: 30, name: yxhscs_itf_ar_interface_part_30) (cost=0.43..8.56 rows=3 width=8) (actual time=3.365..3.365 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 -> Index Only Scan using idx_76128_yxhscs_itf_ar_interface_n5_part_31 on yxhscs_itf_ar_interface (partition sequence: 31, name: yxhscs_itf_ar_interface_part_31) (cost=0.43..8.56 rows=3 width=8) (actual time=4.172..4.172 rows=0 loops=86) Index Cond: (apply_num = l.value2) Heap Fetches: 0 Planning time: 8.501 ms Execution time: 233303.360 ms

4，我的一点看法(纯猜测)

本来感觉是统计信息未更新，猜想依据：在CN上观察数据量： SELECT oid, relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'hscs_itf_imp_lines%'; 输出： 143351 hscs_itf_imp_lines r 0 0 143364 hscs_itf_imp_lines_line_id_seq S 1 1 观察统计信息： SELECT attname, inherited, n_distinct, array_to_string(most_common_vals, E'\n') as most_common_vals FROM pg_stats WHERE tablename like 'hscs_itf_imp_lines%'; 输出为空;

在DN1上的执行结果：数据量： 143364 hscs_itf_imp_lines r 4.09933e+07 2331980 143377 hscs_itf_imp_lines_line_id_seq S 1 1 统计信息： value137 f 7 0,2250,0.000,1450,1250,450 value138 f 1 0 value139 f 1 0 value140 f 81 0,500,6407.45 ...

可见，在DN上的数据量和统计信息是靠谱的，怀疑这个问题是协处理器(CN)没有读到正确的统计信息，才运行了错误的执行计划？求答，多谢

附相关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)

Tencent / TBase

TBASE执行慢SQL和DN上直接执行效率差异很大 #28