通过Explain

Hash join VS Nested join

示例表结构

CREATE TABLE finance.citic_dayend
(
  id integer NOT NULL DEFAULT nextval('finance.t_citic_dayend_id_seq'::regclass),
  date_key integer,
  thoroughfare_id integer,
  success_count bigint,
  success_amount numeric(20,6),
  refund_count bigint,
  refund_amount numeric(20,6),
  bill_rate numeric(10,4),
  mch_fee numeric(20,2),
  cost_rate numeric(10,4),
  third_fee numeric(20,2),
  channel_exe_rate numeric(15,4),
  total_benefit_fee numeric(20,6),
  actual_benefit_fee numeric(20,2),
  a_actual_benefit_fee numeric(20,2),
  channel_id character varying(32),
  channel_name character varying(64),
  merchant_id character varying(16),
  merchant_name character varying(128),
  pay_name character varying(64),
  accept_org_id character varying(32),
  pay_mode character varying(32),
  thoroughfare_name character varying(128),
  channel_path character varying(2048),
  account_name character varying(128),
  benefit_channel character varying(32), -- 受益渠道
  accept_org_name character varying(64), -- 受理机构名称
  one_to_three character varying(8), -- 一分三属性
  rate_id numeric(11,0), -- 费率主键
  channel_path_back character varying(2048) -- 渠道链路channel_path的反排序链路
)
WITH (
  OIDS=FALSE
)
DISTRIBUTED BY (id)
PARTITION BY RANGE(date_key) 
          (
          PARTITION before2017 START (20100101) END (20170101) WITH (appendonly=true, orientation=column, compresstype=zlib, compresslevel=5) 
                    COLUMN id ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN date_key ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN thoroughfare_id ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN success_count ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN success_amount ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN refund_count ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN refund_amount ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN bill_rate ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN mch_fee ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN cost_rate ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN third_fee ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN channel_exe_rate ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN total_benefit_fee ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN actual_benefit_fee ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN a_actual_benefit_fee ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN channel_id ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN channel_name ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN merchant_id ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN merchant_name ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN pay_name ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN accept_org_id ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN pay_mode ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN thoroughfare_name ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN channel_path ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN account_name ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN benefit_channel ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN accept_org_name ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN one_to_three ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN rate_id ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768) 
                    COLUMN channel_path_back ENCODING (compresstype=zlib, compresslevel=5, blocksize=32768), 
          PARTITION year2017 START (20170101) END (20180101), 
          PARTITION year2018 START (20180101) END (20190101), 
          PARTITION year2019 START (20190101) END (20200101)
          )
;
ALTER TABLE finance.citic_dayend
  OWNER TO bi;
GRANT ALL ON TABLE finance.citic_dayend TO bi;
GRANT SELECT ON TABLE finance.citic_dayend TO bi_ro;
COMMENT ON COLUMN finance.citic_dayend.benefit_channel IS '受益渠道';
COMMENT ON COLUMN finance.citic_dayend.accept_org_name IS '受理机构名称';
COMMENT ON COLUMN finance.citic_dayend.one_to_three IS '一分三属性';
COMMENT ON COLUMN finance.citic_dayend.rate_id IS '费率主键';
COMMENT ON COLUMN finance.citic_dayend.channel_path_back IS '渠道链路channel_path的反排序链路';

SQL1： join时不包含date_key

SELECT 			cd.date_key,cd.account_name,cd.accept_org_id,cd.accept_org_name,cd.thoroughfare_id,cd.thoroughfare_name,cd.pay_mode,cd.pay_name,cd.channel_id,
		cd.channel_name,cd.merchant_id,cd.merchant_name,cd.success_count,cd.success_amount,cd.refund_count,cd.refund_amount,cd.bill_rate,cd.mch_fee,
		cd.cost_rate,cd.third_fee,t.wft_benefit_rate,t.wft_benefit,t.wft_tec_rate,t.wft_tec_fee, 
		0 wft_tail,cd.rate_id
	FROM (
		select 
			n.date_key,n.thoroughfare_id,n.success_count,n.success_amount,n.refund_count,n.refund_amount,n.bill_rate,n.mch_fee,n.cost_rate,n.third_fee,n.channel_id,n.channel_name,n.merchant_id,n.merchant_name,n.pay_name,n.accept_org_id,n.pay_mode,n.thoroughfare_name,n.account_name,n.accept_org_name,n.rate_id 
		from (
			SELECT row_number() over (PARTITION by c.merchant_id, c.pay_mode, c.rate_id, c.thoroughfare_id) rn,
			c.date_key,c.thoroughfare_id,c.success_count,c.success_amount,c.refund_count,c.refund_amount,c.bill_rate,c.mch_fee,c.cost_rate,c.third_fee,c.channel_id,c.channel_name,c.merchant_id,c.merchant_name,c.pay_name,c.accept_org_id,c.pay_mode,c.thoroughfare_name,c.account_name,c.accept_org_name,c.rate_id 
			FROM finance.citic_dayend c 
			where c.one_to_three='0' AND c.date_key >= 20180601 AND c.date_key<=20180630 AND c.accept_org_id = '755010000003' 
		) n	WHERE n.rn = 1
	)cd JOIN (
		SELECT d.date_key,d.account_name,d.accept_org_id,d.channel_id,d.merchant_id,d.thoroughfare_id,d.pay_mode,d.rate_id,
		SUM(CASE WHEN (d.benefit_channel='ALL_CITI0C_HEAD_784191' OR d.benefit_channel='102173128950') THEN d.channel_exe_rate ELSE 0 END ) wft_benefit_rate,
		SUM(CASE WHEN (d.benefit_channel='ALL_CITI0C_HEAD_784191' OR d.benefit_channel='102173128950') THEN d.actual_benefit_fee ELSE 0 END ) wft_benefit,
		SUM(CASE WHEN (d.benefit_channel!='ALL_CITI0C_HEAD_784191' AND d.benefit_channel!='102173128950') THEN d.channel_exe_rate ELSE 0 END ) wft_tec_rate,
		SUM(CASE WHEN (d.benefit_channel!='ALL_CITI0C_HEAD_784191' AND d.benefit_channel!='102173128950') THEN d.actual_benefit_fee ELSE 0 END ) wft_tec_fee
		FROM finance.citic_dayend d 
		WHERE d.one_to_three='0' AND d.date_key >= 20180601 AND d.date_key<=20180630 AND d.accept_org_id = '755010000003' 
		GROUP BY d.date_key,d.account_name,d.accept_org_id,d.channel_id,d.merchant_id,d.thoroughfare_id,d.pay_mode,d.rate_id 
	) t ON cd.merchant_id=t.merchant_id AND cd.thoroughfare_id=t.thoroughfare_id AND cd.pay_mode=t.pay_mode AND cd.rate_id=t.rate_id

SQL2： join时包含date_key

SELECT 	
cd.date_key,cd.account_name,cd.accept_org_id,cd.accept_org_name,cd.thoroughfare_id,cd.thoroughfare_name,cd.pay_mode,cd.pay_name,cd.channel_id,
		cd.channel_name,cd.merchant_id,cd.merchant_name,cd.success_count,cd.success_amount,cd.refund_count,cd.refund_amount,cd.bill_rate,cd.mch_fee,
		cd.cost_rate,cd.third_fee,t.wft_benefit_rate,t.wft_benefit,t.wft_tec_rate,t.wft_tec_fee, 
		0 wft_tail,cd.rate_id
	FROM (
		select 
			n.date_key,n.thoroughfare_id,n.success_count,n.success_amount,n.refund_count,n.refund_amount,n.bill_rate,n.mch_fee,n.cost_rate,n.third_fee,n.channel_id,n.channel_name,n.merchant_id,n.merchant_name,n.pay_name,n.accept_org_id,n.pay_mode,n.thoroughfare_name,n.account_name,n.accept_org_name,n.rate_id 
		from (
			SELECT row_number() over (PARTITION by c.merchant_id, c.pay_mode, c.rate_id, c.thoroughfare_id) rn,
			c.date_key,c.thoroughfare_id,c.success_count,c.success_amount,c.refund_count,c.refund_amount,c.bill_rate,c.mch_fee,c.cost_rate,c.third_fee,c.channel_id,c.channel_name,c.merchant_id,c.merchant_name,c.pay_name,c.accept_org_id,c.pay_mode,c.thoroughfare_name,c.account_name,c.accept_org_name,c.rate_id 
			FROM finance.citic_dayend c 
			where c.one_to_three='0' AND c.date_key >= 20180601 AND c.date_key<=20180630 AND c.accept_org_id = '755010000003' 
		) n	WHERE n.rn = 1
	)cd JOIN (
		SELECT d.date_key,d.account_name,d.accept_org_id,d.channel_id,d.merchant_id,d.thoroughfare_id,d.pay_mode,d.rate_id,
		SUM(CASE WHEN (d.benefit_channel='ALL_CITI0C_HEAD_784191' OR d.benefit_channel='102173128950') THEN d.channel_exe_rate ELSE 0 END ) wft_benefit_rate,
		SUM(CASE WHEN (d.benefit_channel='ALL_CITI0C_HEAD_784191' OR d.benefit_channel='102173128950') THEN d.actual_benefit_fee ELSE 0 END ) wft_benefit,
		SUM(CASE WHEN (d.benefit_channel!='ALL_CITI0C_HEAD_784191' AND d.benefit_channel!='102173128950') THEN d.channel_exe_rate ELSE 0 END ) wft_tec_rate,
		SUM(CASE WHEN (d.benefit_channel!='ALL_CITI0C_HEAD_784191' AND d.benefit_channel!='102173128950') THEN d.actual_benefit_fee ELSE 0 END ) wft_tec_fee
		FROM finance.citic_dayend d 
		WHERE d.one_to_three='0' AND d.date_key >= 20180601 AND d.date_key<=20180630 AND d.accept_org_id = '755010000003' 
		GROUP BY d.date_key,d.account_name,d.accept_org_id,d.channel_id,d.merchant_id,d.thoroughfare_id,d.pay_mode,d.rate_id 
	) t ON cd.merchant_id=t.merchant_id AND cd.thoroughfare_id=t.thoroughfare_id AND cd.pay_mode=t.pay_mode AND cd.rate_id=t.rate_id
AND cd.date_key=t.date_key

二者的执行计划对比：

优化方法

使用窗口函数，避免Join两个子查询。

SELECT d.date_key,d.account_name,d.accept_org_id,d.rate_id, accept_org_name,thoroughfare_id,thoroughfare_name,pay_mode,pay_name,channel_id, 
channel_name,merchant_id,merchant_name,success_count,success_amount,refund_count,refund_amount,bill_rate,mch_fee, cost_rate,third_fee,
    0 wft_tail,
		SUM(CASE WHEN (d.benefit_channel='ALL_CITI0C_HEAD_784191' OR d.benefit_channel='102173128950') THEN d.channel_exe_rate ELSE 0 END ) 
  OVER (PARTITION BY  d.date_key,d.account_name,d.accept_org_id,d.channel_id,d.merchant_id,d.thoroughfare_id,d.pay_mode,d.rate_id) wft_benefit_rate,
		SUM(CASE WHEN (d.benefit_channel='ALL_CITI0C_HEAD_784191' OR d.benefit_channel='102173128950') THEN d.actual_benefit_fee ELSE 0 END ) 
  OVER (PARTITION BY  d.date_key,d.account_name,d.accept_org_id,d.channel_id,d.merchant_id,d.thoroughfare_id,d.pay_mode,d.rate_id) wft_benefit,
		SUM(CASE WHEN (d.benefit_channel!='ALL_CITI0C_HEAD_784191' AND d.benefit_channel!='102173128950') THEN d.channel_exe_rate ELSE 0 END ) 
  OVER (PARTITION BY  d.date_key,d.account_name,d.accept_org_id,d.channel_id,d.merchant_id,d.thoroughfare_id,d.pay_mode,d.rate_id) wft_tec_rate,
		SUM(CASE WHEN (d.benefit_channel!='ALL_CITI0C_HEAD_784191' AND d.benefit_channel!='102173128950') THEN d.actual_benefit_fee ELSE 0 END ) 
  OVER (PARTITION BY  d.date_key,d.account_name,d.accept_org_id,d.channel_id,d.merchant_id,d.thoroughfare_id,d.pay_mode,d.rate_id) wft_tec_fee
		FROM finance.citic_dayend d 
		WHERE d.one_to_three='0' AND d.date_key >= 20180601 AND d.date_key<=20180630 AND d.accept_org_id = '755010000003'

SQL性能 - 9dian/Index GitHub Wiki

通过Explain

Hash join VS Nested join

二者的执行计划对比：

优化方法

⚠️ GitHub.com Fallback ⚠️

SQL性能 - 9dian/Index GitHub Wiki

通过Explain

Hash join VS Nested join

二者的执行计划对比：

优化方法

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️