最近在适配个MySQL应用的项目，各种SQL改成PG兼容的语法真的是脑壳痛，今天遇到个有意思的案例。
原 MySQL SQL语句：
SELECT DISTINCT l.MALL_NAME '项目', t.CONT_NO '合同编号', t.COMPANY_NAME '租户', t.STORE_NOS '铺位号', (select GROUP_CONCAT(r.FLOOR_NAME SEPARATOR ',') from bfbfbf r where r.id in ( select s.FLOOR_ID from bsssb s where s.id in ( select substring_index(substring_index(t1.store_ids, ',', b.help_topic_id + 1), ',', -1) a from bc t1 left join mysql.help_topic b on b.help_topic_id < (length(t1.store_ids) - length(replace(t1.store_ids, ',', '')) + 1) where t1.CONT_NO = t.CONT_NO) )) '楼层', t.BRAND_NAME '品牌', l1.DICT_NAME '一级业态', l2.DICT_NAME '二级业态', l3.DICT_NAME '三级业态', t.LAYOUT_NAME '签约业态', t.SHARE_SQUARE '套内面积', t.RENT_SQUARE '计租面积', t.STRUCTURE_SQUARE '建筑面积', t.CONT_BEGIN_DATE A, IFNULL( CONT_FAIL_DATE, CONT_END_DATE ) B, CASE t.`STATUS` WHEN '0' THEN '未签约' WHEN '1' THEN '已审核' WHEN '2' THEN '已解约' WHEN '3' THEN '待审核' WHEN '5' THEN '已结算' ELSE '未知类型' END '状态', (SELECT RENT_TYPE FROM bccdq e where e.CONT_NO = t.CONT_NO and e.IS_DEL = '0' and e.RENT_TYPE is not null order by e.CREATED_DATE desc limit 1) '计租方式', (select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202301' and m1.CONT_NO = t.CONT_NO limit 1) '2023年1月销售额', (select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202301' and m1.CONT_NO = t.CONT_NO limit 1) '2023年1月销售笔数', (select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202302' and m1.CONT_NO = t.CONT_NO limit 1) '2023年2月销售额', (select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202302' and m1.CONT_NO = t.CONT_NO limit 1) '2023年2月销售笔数', (select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202303' and m1.CONT_NO = t.CONT_NO limit 1) '2023年3月销售额', (select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202303' and m1.CONT_NO = t.CONT_NO limit 1) '2023年3月销售笔数', (select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202304' and m1.CONT_NO = t.CONT_NO limit 1) '2023年4月销售额', (select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202304' and m1.CONT_NO = t.CONT_NO limit 1) '2023年4月销售笔数', (select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202305' and m1.CONT_NO = t.CONT_NO limit 1) '2023年5月销售额', (select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202305' and m1.CONT_NO = t.CONT_NO limit 1) '2023年5月销售笔数', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-01' and IS_DEL = '0' limit 1) '2023年1月固定租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-02' and IS_DEL = '0' limit 1) '2023年2月固定租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-03' and IS_DEL = '0' limit 1) '2023年3月固定租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-04' and IS_DEL = '0' limit 1) '2023年4月固定租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-05' and IS_DEL = '0' limit 1) '2023年5月固定租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-01' and IS_DEL = '0' limit 1) '2023年1月提成租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-02' and IS_DEL = '0' limit 1) '2023年2月提成租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-03' and IS_DEL = '0' limit 1) '2023年3月提成租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-04' and IS_DEL = '0' limit 1) '2023年4月提成租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-05' and IS_DEL = '0' limit 1) '2023年5月提成租金', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-01' and IS_DEL = '0' limit 1) '2023年1月物业管理费', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-02' and IS_DEL = '0' limit 1) '2023年2月物业管理费', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-03' and IS_DEL = '0' limit 1) '2023年3月物业管理费', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-04' and IS_DEL = '0' limit 1) '2023年4月物业管理费', (select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-05' and IS_DEL = '0' limit 1) '2023年5月物业管理费' FROM bc t LEFT JOIN bms l ON l.id = t.MALL_ID LEFT JOIN ( SELECT * FROM bsggldddict WHERE `LEVEL` = 4 ) l4 ON l4.ID = t.LAYOUT LEFT JOIN ( SELECT * FROM bsggldddict WHERE `LEVEL` = 3 ) l3 ON ( l3.ID = t.LAYOUT OR l4.PARENT_ID = l3.ID ) LEFT JOIN ( SELECT * FROM bsggldddict WHERE `LEVEL` = 2 ) l2 ON ( l2.ID = t.LAYOUT OR l3.PARENT_ID = l2.ID ) LEFT JOIN ( SELECT * FROM bsggldddict WHERE `LEVEL` = 1 ) l1 ON l2.PARENT_ID = l1.ID WHERE l.MALL_TYPE = '1' and t.IS_DEL = '0' AND t.STATUS IN ( 1, 2, 5 ) AND t.CONT_BEGIN_DATE <= '2023-05-31' AND IFNULL( CONT_FAIL_DATE, CONT_END_DATE ) >= '2023-01-01';
这条SQL是首先改成postgresql 兼容的语法，其实要改的内容不多，只有一个标量子查询处需要改写：
(select GROUP_CONCAT(r.FLOOR_NAME SEPARATOR ',') from bfbfbf r where r.id in ( select s.FLOOR_ID from bsssb s where s.id in ( select substring_index(substring_index(t1.store_ids, ',', b.help_topic_id + 1), ',', -1) a from bc t1 left join mysql.help_topic b on b.help_topic_id < (length(t1.store_ids) - length(replace(t1.store_ids, ',', '')) + 1) where t1.CONT_NO = t.CONT_NO) )) '楼层',
这里开发很聪明，使用了个小技巧使用 help_topic_id 递增的 help_topic_id 字段 + 进行 GROUP_CONCAT 函数进行行转列。
postgresql 由于没有 mysql.help_topic 表、GROUP_CONCAT、substring_index 这类函数所以需要等价改写上面的逻辑。
洽洽是这条SQL花了我几个小时来进行逻辑的等价改写，脑壳疼得厉害，还费烟，不过尝试了N次，查了不少函数终于给我改写出来了 ?
MySQL 逻辑等价改写 PG
(SELECT STRING_AGG(r.FLOOR_NAME, ',') FROM bfbfbf r WHERE r.id = ANY (ARRAY( SELECT CAST(SUBSTRING(REGEXP_SPLIT_TO_TABLE(t1.store_ids, ','), '\d+') AS INTEGER) FROM bc t1 WHERE t1.CONT_NO = t.CONT_NO))) AS "楼层",
等研发验证了逻辑等价以后，新的问题又来了，MySQL上这条语句跑15S就能出结果，然后到了PG需要跑 2分多种，擦，看来PG优化器性能也不咋地 ☹
不过只要是慢的问题都好解决，先看看新的SQL执行计划：
SELECT DISTINCT l.MALL_NAME "项目", t.CONT_NO "合同编号", t.COMPANY_NAME "租户", t.STORE_NOS "铺位号", (SELECT STRING_AGG(r.FLOOR_NAME, ',') FROM bfbfbf r WHERE r.id = ANY (ARRAY( SELECT CAST(SUBSTRING(REGEXP_SPLIT_TO_TABLE(t1.store_ids, ','), '\d+') AS INTEGER) FROM bc t1 WHERE t1.CONT_NO = t.CONT_NO))) AS "楼层", t.BRAND_NAME "品牌", l1.DICT_NAME "一级业态", l2.DICT_NAME "二级业态", l3.DICT_NAME "三级业态", t.LAYOUT_NAME "签约业态", t.SHARE_SQUARE "套内面积", t.RENT_SQUARE "计租面积", t.STRUCTURE_SQUARE "建筑面积", t.CONT_BEGIN_DATE A, IFNULL(CONT_FAIL_DATE, CONT_END_DATE) B, CASE t."STATUS" WHEN '0' THEN '未签约' WHEN '1' THEN '已审核' WHEN '2' THEN '已解约' WHEN '3' THEN '待审核' WHEN '5' THEN '已结算' ELSE '未知类型' END "状态", (SELECT RENT_TYPE FROM bccdq e WHERE e.CONT_NO = t.CONT_NO AND e.IS_DEL = '0' AND e.RENT_TYPE IS NOT NULL ORDER BY e.CREATED_DATE DESC LIMIT 1) AS "计租方式", (SELECT CONFIRM_AMOUNT FROM msamsuo m1 WHERE SALE_YM = '202301' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年1月销售额", (SELECT CONFIRM_COUNT FROM msamsuo m1 WHERE SALE_YM = '202301' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年1月销售笔数", (SELECT CONFIRM_AMOUNT FROM msamsuo m1 WHERE SALE_YM = '202302' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年2月销售额", (SELECT CONFIRM_COUNT FROM msamsuo m1 WHERE SALE_YM = '202302' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年2月销售笔数", (SELECT CONFIRM_AMOUNT FROM msamsuo m1 WHERE SALE_YM = '202303' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年3月销售额", (SELECT CONFIRM_COUNT FROM msamsuo m1 WHERE SALE_YM = '202303' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年3月销售笔数", (SELECT CONFIRM_AMOUNT FROM msamsuo m1 WHERE SALE_YM = '202304' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年4月销售额", (SELECT CONFIRM_COUNT FROM msamsuo m1 WHERE SALE_YM = '202304' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年4月销售笔数", (SELECT CONFIRM_AMOUNT FROM msamsuo m1 WHERE SALE_YM = '202305' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年5月销售额", (SELECT CONFIRM_COUNT FROM msamsuo m1 WHERE SALE_YM = '202305' AND m1.CONT_NO = t.CONT_NO LIMIT 1) AS "2023年5月销售笔数", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '01' AND FINANCE_PERIOD = '2023-01' AND IS_DEL = '0' LIMIT 1) AS "2023年1月固定租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '01' AND FINANCE_PERIOD = '2023-02' AND IS_DEL = '0' LIMIT 1) AS "2023年2月固定租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '01' AND FINANCE_PERIOD = '2023-03' AND IS_DEL = '0' LIMIT 1) AS "2023年3月固定租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '01' AND FINANCE_PERIOD = '2023-04' AND IS_DEL = '0' LIMIT 1) AS "2023年4月固定租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '01' AND FINANCE_PERIOD = '2023-05' AND IS_DEL = '0' LIMIT 1) AS "2023年5月固定租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '1010' AND FINANCE_PERIOD = '2023-01' AND IS_DEL = '0' LIMIT 1) AS "2023年1月提成租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '1010' AND FINANCE_PERIOD = '2023-02' AND IS_DEL = '0' LIMIT 1) AS "2023年2月提成租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '1010' AND FINANCE_PERIOD = '2023-03' AND IS_DEL = '0' LIMIT 1) AS "2023年3月提成租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '1010' AND FINANCE_PERIOD = '2023-04' AND IS_DEL = '0' LIMIT 1) AS "2023年4月提成租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '1010' AND FINANCE_PERIOD = '2023-05' AND IS_DEL = '0' LIMIT 1) AS "2023年5月提成租金", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '02' AND FINANCE_PERIOD = '2022-01' AND IS_DEL = '0' LIMIT 1) AS "2023年1月物业管理费", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '02' AND FINANCE_PERIOD = '2022-02' AND IS_DEL = '0' LIMIT 1) AS "2023年2月物业管理费", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '02' AND FINANCE_PERIOD = '2022-03' AND IS_DEL = '0' LIMIT 1) AS "2023年3月物业管理费", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '02' AND FINANCE_PERIOD = '2022-04' AND IS_DEL = '0' LIMIT 1) AS "2023年4月物业管理费", (SELECT RECE_AMOUNT FROM FIRCVWZZZZ e WHERE e.cont_no = t.cont_no AND e.FEE_TYPE = '02' AND FINANCE_PERIOD = '2022-05' AND IS_DEL = '0' LIMIT 1) AS "2023年5月物业管理费" FROM bc t LEFT JOIN bms l ON l.id = t.MALL_ID LEFT JOIN (SELECT * FROM bsggldddict WHERE "LEVEL" = 4) l4 ON l4.ID = t.LAYOUT LEFT JOIN (SELECT * FROM bsggldddict WHERE "LEVEL" = 3) l3 ON (l3.ID = t.LAYOUT OR l4.PARENT_ID = l3.ID) LEFT JOIN (SELECT * FROM bsggldddict WHERE "LEVEL" = 2) l2 ON (l2.ID = t.LAYOUT OR l3.PARENT_ID = l2.ID) LEFT JOIN (SELECT * FROM bsggldddict WHERE "LEVEL" = 1) l1 ON l2.PARENT_ID = l1.ID WHERE l.MALL_TYPE = '1' AND t.IS_DEL = '0' AND t.STATUS IN (1, 2, 5) AND t.CONT_BEGIN_DATE <= '2023-05-31' AND IFNULL(CONT_FAIL_DATE, CONT_END_DATE) >= '2023-01-01'; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- -> Nested Loop Left Join (cost=15.14..1347.57 rows=21 width=188) (actual time=0.205..56.361 rows=1912 loops=1) Join Filter: ((bsggldddict_2.ID = t.LAYOUT) OR (bsggldddict_1.PARENT_ID = bsggldddict_2.ID)) Rows Removed by Join Filter: 28686 -> Nested Loop Left Join (cost=15.14..1338.31 rows=21 width=183) (actual time=0.193..47.921 rows=1912 loops=1) Join Filter: ((bsggldddict_1.ID = t.LAYOUT) OR (bsggldddict.PARENT_ID = bsggldddict_1.ID)) Rows Removed by Join Filter: 103267 -> Nested Loop Left Join (cost=15.14..1314.62 rows=21 width=170) (actual time=0.153..29.852 rows=1912 loops=1) -> Hash Join (cost=15.00..1306.59 rows=21 width=162) (actual time=0.141..18.726 rows=1912 loops=1) Hash Cond: (t.MALL_ID = l.ID) -> Seq Scan on bc t (cost=0.00..1291.40 rows=73 width=144) (actual time=0.015..15.626 rows=4357 loops=1) Filter: (((CONT_BEGIN_DATE)::timestamp without time zone <= '2023-05-31 00:00:00'::timestamp without time zone) AND (IFNULL((CONT_FAIL_DATE)::timestamp without time zone, (CONT_END_DATE)::timestamp without time zone) >= '2023-01-01 00:00 :00'::timestamp without time zone) AND (IS_DEL = '0'::bpchar) AND (((STATUS)::integer = 1) OR ((STATUS)::integer = 2) OR ((STATUS)::integer = 5))) Rows Removed by Filter: 11563 -> Hash (cost=14.10..14.10 rows=72 width=34) (actual time=0.116..0.116 rows=72 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 13kB -> Seq Scan on bms l (cost=0.00..14.10 rows=72 width=34) (actual time=0.006..0.099 rows=72 loops=1) Filter: ((MALL_TYPE)::text = '1'::text) Rows Removed by Filter: 176 -> Index Scan using IDX_ID_49C39C7E on bsggldddict (cost=0.14..0.38 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1912) Index Cond: (ID = t.LAYOUT) Filter: ("LEVEL" = 4) Rows Removed by Filter: 1 -> Materialize (cost=0.00..3.61 rows=55 width=29) (actual time=0.000..0.003 rows=55 loops=1912) -> Seq Scan on bsggldddict bsggldddict_1 (cost=0.00..3.34 rows=55 width=29) (actual time=0.005..0.021 rows=55 loops=1) Filter: ("LEVEL" = 3) Rows Removed by Filter: 52 -> Materialize (cost=0.00..3.42 rows=16 width=29) (actual time=0.000..0.001 rows=16 loops=1912) -> Seq Scan on bsggldddict bsggldddict_2 (cost=0.00..3.34 rows=16 width=29) (actual time=0.004..0.026 rows=16 loops=1) Filter: ("LEVEL" = 2) Rows Removed by Filter: 91 -> Materialize (cost=0.00..3.37 rows=6 width=21) (actual time=0.000..0.000 rows=4 loops=1912) -> Seq Scan on bsggldddict bsggldddict_3 (cost=0.00..3.34 rows=6 width=21) (actual time=0.003..0.018 rows=6 loops=1) Filter: ("LEVEL" = 1) Rows Removed by Filter: 101 SubPlan 2 -> Aggregate (cost=61.82..61.83 rows=1 width=32) (actual time=0.031..0.031 rows=1 loops=1912) InitPlan 1 (returns $1) -> Result (cost=0.29..30.81 rows=1000 width=4) (actual time=0.021..0.023 rows=1 loops=1912) -> ProjectSet (cost=0.29..13.31 rows=1000 width=32) (actual time=0.017..0.018 rows=1 loops=1912) -> Index Scan using idx_bc_1_2_3_4 on bc t1 (cost=0.29..8.30 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Index Scan using IDX_ID_9460CCF1 on bfbfbf r (cost=0.28..30.98 rows=10 width=3) (actual time=0.003..0.003 rows=0 loops=1912) Index Cond: (ID = ANY (ARRAY[$1])) SubPlan 3 -> Limit (cost=31.45..31.45 rows=1 width=11) (actual time=0.041..0.041 rows=1 loops=1912) -> Sort (cost=31.45..31.46 rows=5 width=11) (actual time=0.040..0.040 rows=1 loops=1912) Sort Key: e.CREATED_DATE DESC Sort Method: top-N heapsort Memory: 25kB -> Bitmap Heap Scan on bccdq e (cost=4.47..31.42 rows=5 width=11) (actual time=0.021..0.032 rows=9 loops=1912) Recheck Cond: ((CONT_NO)::text = (t.CONT_NO)::text) Filter: ((RENT_TYPE IS NOT NULL) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 6 Heap Blocks: exact=3014 -> Bitmap Index Scan on idx_cont_feetype_FED3F7C9 (cost=0.00..4.47 rows=7 width=0) (actual time=0.016..0.016 rows=15 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) SubPlan 4 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.020..0.020 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1 (cost=0.42..8.44 rows=1 width=7) (actual time=0.019..0.019 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202301'::text)) SubPlan 5 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202301'::text)) SubPlan 6 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1_2 (cost=0.42..8.44 rows=1 width=7) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202302'::text)) SubPlan 7 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1_3 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202302'::text)) SubPlan 8 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1_4 (cost=0.42..8.44 rows=1 width=7) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202303'::text)) SubPlan 9 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1_5 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202303'::text)) SubPlan 10 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1_6 (cost=0.42..8.44 rows=1 width=7) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202304'::text)) SubPlan 11 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1_7 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202304'::text)) SubPlan 12 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1_8 (cost=0.42..8.44 rows=1 width=7) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202305'::text)) SubPlan 13 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using CONT_NO_82815276 on msamsuo m1_9 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202305'::text)) SubPlan 14 -> Limit (cost=273.59..277.61 rows=1 width=6) (actual time=4.769..4.770 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_1 (cost=273.59..277.61 rows=1 width=6) (actual time=4.766..4.766 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-01'::text)) Filter: (((FEE_TYPE)::text = '01'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 1 Heap Blocks: exact=3634 -> BitmapAnd (cost=273.59..273.59 rows=1 width=0) (actual time=4.757..4.757 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.047..0.047 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..267.77 rows=14313 width=0) (actual time=4.705..4.705 rows=14832 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-01'::text) SubPlan 15 -> Limit (cost=300.49..304.51 rows=1 width=6) (actual time=4.720..4.720 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_2 (cost=300.49..304.51 rows=1 width=6) (actual time=4.716..4.716 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-02'::text)) Filter: (((FEE_TYPE)::text = '01'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 1 Heap Blocks: exact=3624 -> BitmapAnd (cost=300.49..300.49 rows=1 width=0) (actual time=4.709..4.709 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.045..0.045 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..294.67 rows=15766 width=0) (actual time=4.661..4.661 rows=14629 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-02'::text) SubPlan 16 -> Limit (cost=292.46..296.48 rows=1 width=6) (actual time=4.906..4.906 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_3 (cost=292.46..296.48 rows=1 width=6) (actual time=4.902..4.902 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-03'::text)) Filter: (((FEE_TYPE)::text = '01'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 1 Heap Blocks: exact=3597 -> BitmapAnd (cost=292.46..292.46 rows=1 width=0) (actual time=4.896..4.896 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.045..0.045 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..286.64 rows=15228 width=0) (actual time=4.847..4.847 rows=14426 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-03'::text) SubPlan 17 -> Limit (cost=258.73..262.75 rows=1 width=6) (actual time=3.797..3.797 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_4 (cost=258.73..262.75 rows=1 width=6) (actual time=3.793..3.793 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-04'::text)) Filter: (((FEE_TYPE)::text = '01'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 1 Heap Blocks: exact=3545 -> BitmapAnd (cost=258.73..258.73 rows=1 width=0) (actual time=3.787..3.787 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.045..0.045 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..252.91 rows=13398 width=0) (actual time=3.737..3.737 rows=13934 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-04'::text) SubPlan 18 -> Limit (cost=232.64..236.66 rows=1 width=6) (actual time=3.619..3.619 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_5 (cost=232.64..236.66 rows=1 width=6) (actual time=3.616..3.616 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-05'::text)) Filter: (((FEE_TYPE)::text = '01'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 1 Heap Blocks: exact=3487 -> BitmapAnd (cost=232.64..232.64 rows=1 width=0) (actual time=3.610..3.610 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.044..0.044 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..226.82 rows=12053 width=0) (actual time=3.561..3.561 rows=13474 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-05'::text) SubPlan 19 -> Limit (cost=273.59..277.61 rows=1 width=6) (actual time=4.760..4.760 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_6 (cost=273.59..277.61 rows=1 width=6) (actual time=4.756..4.756 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-01'::text)) Filter: (((FEE_TYPE)::text = '1010'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 3 Heap Blocks: exact=6314 -> BitmapAnd (cost=273.59..273.59 rows=1 width=0) (actual time=4.747..4.747 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.044..0.044 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..267.77 rows=14313 width=0) (actual time=4.698..4.698 rows=14832 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-01'::text) SubPlan 20 -> Limit (cost=300.49..304.51 rows=1 width=6) (actual time=4.713..4.714 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_7 (cost=300.49..304.51 rows=1 width=6) (actual time=4.710..4.710 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-02'::text)) Filter: (((FEE_TYPE)::text = '1010'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 3 Heap Blocks: exact=6294 -> BitmapAnd (cost=300.49..300.49 rows=1 width=0) (actual time=4.701..4.701 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.045..0.045 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..294.67 rows=15766 width=0) (actual time=4.653..4.653 rows=14629 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-02'::text) SubPlan 21 -> Limit (cost=292.46..296.48 rows=1 width=6) (actual time=4.897..4.897 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_8 (cost=292.46..296.48 rows=1 width=6) (actual time=4.893..4.893 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-03'::text)) Filter: (((FEE_TYPE)::text = '1010'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 3 Heap Blocks: exact=6238 -> BitmapAnd (cost=292.46..292.46 rows=1 width=0) (actual time=4.885..4.885 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.045..0.045 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..286.64 rows=15228 width=0) (actual time=4.837..4.837 rows=14426 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-03'::text) SubPlan 22 -> Limit (cost=258.73..262.75 rows=1 width=6) (actual time=3.798..3.798 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_9 (cost=258.73..262.75 rows=1 width=6) (actual time=3.794..3.794 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-04'::text)) Filter: (((FEE_TYPE)::text = '1010'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 3 Heap Blocks: exact=6132 -> BitmapAnd (cost=258.73..258.73 rows=1 width=0) (actual time=3.786..3.786 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.044..0.044 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..252.91 rows=13398 width=0) (actual time=3.737..3.737 rows=13934 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-04'::text) SubPlan 23 -> Limit (cost=232.64..236.66 rows=1 width=6) (actual time=3.600..3.601 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_10 (cost=232.64..236.66 rows=1 width=6) (actual time=3.597..3.597 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2023-05'::text)) Filter: (((FEE_TYPE)::text = '1010'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 3 Heap Blocks: exact=6023 -> BitmapAnd (cost=232.64..232.64 rows=1 width=0) (actual time=3.589..3.589 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.045..0.045 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..226.82 rows=12053 width=0) (actual time=3.540..3.540 rows=13474 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2023-05'::text) SubPlan 24 -> Limit (cost=461.46..469.47 rows=1 width=6) (actual time=6.365..6.365 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_11 (cost=461.46..469.47 rows=1 width=6) (actual time=6.362..6.362 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2022-01'::text)) Filter: (((FEE_TYPE)::text = '02'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 1 Heap Blocks: exact=3961 -> BitmapAnd (cost=461.46..461.46 rows=2 width=0) (actual time=6.354..6.354 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.044..0.044 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..455.64 rows=24429 width=0) (actual time=6.305..6.305 rows=24827 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2022-01'::text) SubPlan 25 -> Limit (cost=460.66..468.67 rows=1 width=6) (actual time=6.314..6.314 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_12 (cost=460.66..468.67 rows=1 width=6) (actual time=6.310..6.310 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2022-02'::text)) Filter: (((FEE_TYPE)::text = '02'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 2 Heap Blocks: exact=4007 -> BitmapAnd (cost=460.66..460.66 rows=2 width=0) (actual time=6.303..6.303 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.044..0.044 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..454.84 rows=24322 width=0) (actual time=6.254..6.254 rows=23605 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2022-02'::text) SubPlan 26 -> Limit (cost=507.63..519.63 rows=1 width=6) (actual time=6.402..6.403 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_13 (cost=507.63..519.63 rows=1 width=6) (actual time=6.399..6.399 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2022-03'::text)) Filter: (((FEE_TYPE)::text = '02'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 2 Heap Blocks: exact=4268 -> BitmapAnd (cost=507.63..507.63 rows=3 width=0) (actual time=6.391..6.391 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.044..0.044 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..501.81 rows=26851 width=0) (actual time=6.343..6.343 rows=24746 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2022-03'::text) SubPlan 27 -> Limit (cost=420.51..428.52 rows=1 width=6) (actual time=6.222..6.222 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_14 (cost=420.51..428.52 rows=1 width=6) (actual time=6.218..6.218 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2022-04'::text)) Filter: (((FEE_TYPE)::text = '02'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 2 Heap Blocks: exact=4375 -> BitmapAnd (cost=420.51..420.51 rows=2 width=0) (actual time=6.211..6.211 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.045..0.045 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..414.69 rows=22169 width=0) (actual time=6.162..6.162 rows=24456 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2022-04'::text) SubPlan 28 -> Limit (cost=360.30..368.31 rows=1 width=6) (actual time=5.649..5.649 rows=1 loops=1912) -> Bitmap Heap Scan on FIRCVWZZZZ e_15 (cost=360.30..368.31 rows=1 width=6) (actual time=5.645..5.645 rows=1 loops=1912) Recheck Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FINANCE_PERIOD)::text = '2022-05'::text)) Filter: (((FEE_TYPE)::text = '02'::text) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 2 Heap Blocks: exact=4402 -> BitmapAnd (cost=360.30..360.30 rows=2 width=0) (actual time=5.638..5.638 rows=0 loops=1912) -> Bitmap Index Scan on INX_CONT_NO_668B3A17 (cost=0.00..5.57 rows=152 width=0) (actual time=0.045..0.045 rows=222 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Bitmap Index Scan on INX_FINANCE_PERIOD_C53DF1AB (cost=0.00..354.49 rows=18941 width=0) (actual time=5.589..5.589 rows=19628 loops=1912) Index Cond: ((FINANCE_PERIOD)::text = '2022-05'::text) Planning Time: 6.431 ms Execution Time: 143065.735 ms (283 行记录)
可以看到，基本都是标量子查询产生filter过多导致整体缓慢，最主要的是标量子查询大部分还是 Bitmap Heap Scan ，而不是 Index Scan。
加两条索引再跑一下试试
create index idx_fa_1_2_3_4 on FIRCVWZZZZ (CONT_NO, FEE_TYPE, FINANCE_PERIOD, IS_DEL); create index idx_msam_contno_saleym on msamsuo (CONT_NO, SALE_YM);
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=7747.95..7750.21 rows=21 width=758) (actual time=935.263..937.220 rows=1912 loops=1) -> Sort (cost=7747.95..7748.01 rows=21 width=758) (actual time=935.262..935.390 rows=1912 loops=1) Sort Key: l.MALL_NAME, t.CONT_NO, t.COMPANY_NAME, t.STORE_NOS, ((SubPlan 2)), t.BRAND_NAME, bsggldddict_3.DICT_NAME, bsggldddict_2.DICT_NAME, bsggldddict_1.DICT_NAME, t.LAYOUT_NAME, t.SHARE_SQUARE, t.RENT_SQUARE, t.STRUCTURE_SQUARE, t.CONT_BEGIN_DATE, (IFNULL((t.C ONT_FAIL_DATE)::timestamp without time zone, (t.CONT_END_DATE)::timestamp without time zone)), (CASE t.STATUS WHEN '0'::bpchar THEN '未签约'::text WHEN '1'::bpchar THEN '已审核'::text WHEN '2'::bpchar THEN '已解约'::text WHEN '3'::bpchar THEN '待审核'::text WHEN '5'::bpchar THEN ' 结算'::text ELSE '未知类型'::text END), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6)), ((SubPlan 7)), ((SubPlan 8)), ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11)), ((SubPlan 12)), ((SubPlan 13)), ((SubPlan 14)), ((SubPlan 15)), ((SubPlan 16)), ((SubPlan 17)), ((SubPlan 18)), ((SubPlan 19)), ((SubPlan 20)), ((SubPlan 21)), ((SubPlan 22)), ((SubPlan 23)), ((SubPlan 24)), ((SubPlan 25)), ((SubPlan 26)), ((SubPlan 27)), ((SubPlan 28)) Sort Method: quicksort Memory: 861kB -> Nested Loop Left Join (cost=15.14..7747.49 rows=21 width=758) (actual time=0.830..911.658 rows=1912 loops=1) Join Filter: (bsggldddict_2.PARENT_ID = bsggldddict_3.ID) Rows Removed by Join Filter: 5678 -> Nested Loop Left Join (cost=15.14..1347.57 rows=21 width=188) (actual time=0.198..43.046 rows=1912 loops=1) Join Filter: ((bsggldddict_2.ID = t.LAYOUT) OR (bsggldddict_1.PARENT_ID = bsggldddict_2.ID)) Rows Removed by Join Filter: 28686 -> Nested Loop Left Join (cost=15.14..1338.31 rows=21 width=183) (actual time=0.186..36.326 rows=1912 loops=1) Join Filter: ((bsggldddict_1.ID = t.LAYOUT) OR (bsggldddict.PARENT_ID = bsggldddict_1.ID)) Rows Removed by Join Filter: 103267 -> Nested Loop Left Join (cost=15.14..1314.62 rows=21 width=170) (actual time=0.150..19.731 rows=1912 loops=1) -> Hash Join (cost=15.00..1306.59 rows=21 width=162) (actual time=0.136..13.738 rows=1912 loops=1) Hash Cond: (t.MALL_ID = l.ID) -> Seq Scan on bc t (cost=0.00..1291.40 rows=73 width=144) (actual time=0.016..11.766 rows=4357 loops=1) Filter: (((CONT_BEGIN_DATE)::timestamp without time zone <= '2023-05-31 00:00:00'::timestamp without time zone) AND (IFNULL((CONT_FAIL_DATE)::timestamp without time zone, (CONT_END_DATE)::timestamp without time zone) >= '2023-01-01 00:00 :00'::timestamp without time zone) AND (IS_DEL = '0'::bpchar) AND (((STATUS)::integer = 1) OR ((STATUS)::integer = 2) OR ((STATUS)::integer = 5))) Rows Removed by Filter: 11563 -> Hash (cost=14.10..14.10 rows=72 width=34) (actual time=0.111..0.112 rows=72 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 13kB -> Seq Scan on bms l (cost=0.00..14.10 rows=72 width=34) (actual time=0.006..0.094 rows=72 loops=1) Filter: ((MALL_TYPE)::text = '1'::text) Rows Removed by Filter: 176 -> Index Scan using IDX_ID_49C39C7E on bsggldddict (cost=0.14..0.38 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1912) Index Cond: (ID = t.LAYOUT) Filter: ("LEVEL" = 4) Rows Removed by Filter: 1 -> Materialize (cost=0.00..3.61 rows=55 width=29) (actual time=0.000..0.003 rows=55 loops=1912) -> Seq Scan on bsggldddict bsggldddict_1 (cost=0.00..3.34 rows=55 width=29) (actual time=0.006..0.020 rows=55 loops=1) Filter: ("LEVEL" = 3) Rows Removed by Filter: 52 -> Materialize (cost=0.00..3.42 rows=16 width=29) (actual time=0.000..0.001 rows=16 loops=1912) -> Seq Scan on bsggldddict bsggldddict_2 (cost=0.00..3.34 rows=16 width=29) (actual time=0.004..0.018 rows=16 loops=1) Filter: ("LEVEL" = 2) Rows Removed by Filter: 91 -> Materialize (cost=0.00..3.37 rows=6 width=21) (actual time=0.000..0.000 rows=4 loops=1912) -> Seq Scan on bsggldddict bsggldddict_3 (cost=0.00..3.34 rows=6 width=21) (actual time=0.003..0.016 rows=6 loops=1) Filter: ("LEVEL" = 1) Rows Removed by Filter: 101 SubPlan 2 -> Aggregate (cost=61.82..61.83 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=1912) InitPlan 1 (returns $1) -> Result (cost=0.29..30.81 rows=1000 width=4) (actual time=0.015..0.017 rows=1 loops=1912) -> ProjectSet (cost=0.29..13.31 rows=1000 width=32) (actual time=0.013..0.014 rows=1 loops=1912) -> Index Scan using idx_bc_1_2_3_4 on bc t1 (cost=0.29..8.30 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Index Scan using IDX_ID_9460CCF1 on bfbfbf r (cost=0.28..30.98 rows=10 width=3) (actual time=0.002..0.002 rows=0 loops=1912) Index Cond: (ID = ANY (ARRAY[$1])) SubPlan 3 -> Limit (cost=31.45..31.45 rows=1 width=11) (actual time=0.035..0.035 rows=1 loops=1912) -> Sort (cost=31.45..31.46 rows=5 width=11) (actual time=0.034..0.034 rows=1 loops=1912) Sort Key: e.CREATED_DATE DESC Sort Method: top-N heapsort Memory: 25kB -> Bitmap Heap Scan on bccdq e (cost=4.47..31.42 rows=5 width=11) (actual time=0.018..0.028 rows=9 loops=1912) Recheck Cond: ((CONT_NO)::text = (t.CONT_NO)::text) Filter: ((RENT_TYPE IS NOT NULL) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 6 Heap Blocks: exact=3014 -> Bitmap Index Scan on idx_cont_feetype_FED3F7C9 (cost=0.00..4.47 rows=7 width=0) (actual time=0.015..0.015 rows=15 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) SubPlan 4 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.017..0.017 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1 (cost=0.42..8.44 rows=1 width=7) (actual time=0.016..0.016 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202301'::text)) SubPlan 5 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202301'::text)) SubPlan 6 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1_2 (cost=0.42..8.44 rows=1 width=7) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202302'::text)) SubPlan 7 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1_3 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202302'::text)) SubPlan 8 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1_4 (cost=0.42..8.44 rows=1 width=7) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202303'::text)) SubPlan 9 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1_5 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202303'::text)) SubPlan 10 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1_6 (cost=0.42..8.44 rows=1 width=7) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202304'::text)) SubPlan 11 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1_7 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202304'::text)) SubPlan 12 -> Limit (cost=0.42..8.44 rows=1 width=7) (actual time=0.013..0.013 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1_8 (cost=0.42..8.44 rows=1 width=7) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202305'::text)) SubPlan 13 -> Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1912) -> Index Scan using idx_msam_contno_saleym on msamsuo m1_9 (cost=0.42..8.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((SALE_YM)::text = '202305'::text)) SubPlan 14 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.019..0.019 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_1 (cost=0.43..8.45 rows=1 width=6) (actual time=0.018..0.018 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '01'::text) AND ((FINANCE_PERIOD)::text = '2023-01'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 15 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_2 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '01'::text) AND ((FINANCE_PERIOD)::text = '2023-02'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 16 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_3 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '01'::text) AND ((FINANCE_PERIOD)::text = '2023-03'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 17 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_4 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '01'::text) AND ((FINANCE_PERIOD)::text = '2023-04'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 18 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_5 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '01'::text) AND ((FINANCE_PERIOD)::text = '2023-05'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 19 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.017..0.017 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_6 (cost=0.43..8.45 rows=1 width=6) (actual time=0.017..0.017 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '1010'::text) AND ((FINANCE_PERIOD)::text = '2023-01'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 20 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_7 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '1010'::text) AND ((FINANCE_PERIOD)::text = '2023-02'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 21 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_8 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '1010'::text) AND ((FINANCE_PERIOD)::text = '2023-03'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 22 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_9 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '1010'::text) AND ((FINANCE_PERIOD)::text = '2023-04'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 23 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_10 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '1010'::text) AND ((FINANCE_PERIOD)::text = '2023-05'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 24 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_11 (cost=0.43..8.45 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '02'::text) AND ((FINANCE_PERIOD)::text = '2022-01'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 25 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.016 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_12 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '02'::text) AND ((FINANCE_PERIOD)::text = '2022-02'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 26 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_13 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '02'::text) AND ((FINANCE_PERIOD)::text = '2022-03'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 27 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_14 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '02'::text) AND ((FINANCE_PERIOD)::text = '2022-04'::text) AND ((IS_DEL)::text = '0'::text)) SubPlan 28 -> Limit (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) -> Index Scan using idx_fa_1_2_3_4 on FIRCVWZZZZ e_15 (cost=0.43..8.45 rows=1 width=6) (actual time=0.015..0.015 rows=1 loops=1912) Index Cond: (((CONT_NO)::text = (t.CONT_NO)::text) AND ((FEE_TYPE)::text = '02'::text) AND ((FINANCE_PERIOD)::text = '2022-05'::text) AND ((IS_DEL)::text = '0'::text)) Planning Time: 6.469 ms Execution Time: 937.735 ms (163 行记录)
可以看到在标量子查询处，刚才创建的索引都用上了，SQL执行时间从2分钟降到 937ms 就能执行完成，Index Scan才 是正确的执行计划 ?
最后再附带标量子查询等价改写 left join 一个版本，这个就属于自己玩了，没提供给研发 ，仅供学习使用 ?
WITH m1 AS ( SELECT cont_no, MAX(CASE WHEN sale_ym = '202301' THEN confirm_amount END) AS "2023年1月销售额", MAX(CASE WHEN sale_ym = '202301' THEN confirm_count END) AS "2023年1月销售笔数", MAX(CASE WHEN sale_ym = '202302' THEN confirm_amount END) AS "2023年2月销售额", MAX(CASE WHEN sale_ym = '202302' THEN confirm_count END) AS "2023年2月销售笔数", MAX(CASE WHEN sale_ym = '202303' THEN confirm_amount END) AS "2023年3月销售额", MAX(CASE WHEN sale_ym = '202303' THEN confirm_count END) AS "2023年3月销售笔数", MAX(CASE WHEN sale_ym = '202304' THEN confirm_amount END) AS "2023年4月销售额", MAX(CASE WHEN sale_ym = '202304' THEN confirm_count END) AS "2023年4月销售笔数", MAX(CASE WHEN sale_ym = '202305' THEN confirm_amount END) AS "2023年5月销售额", MAX(CASE WHEN sale_ym = '202305' THEN confirm_count END) AS "2023年5月销售笔数" FROM msamsuo WHERE sale_ym BETWEEN '202301' AND '202305' GROUP BY cont_no ),fr AS ( SELECT cont_no, MAX(CASE WHEN finance_period = '2023-01' AND fee_type = '01' THEN rece_amount END) AS "2023年1月固定租金", MAX(CASE WHEN finance_period = '2023-02' AND fee_type = '01' THEN rece_amount END) AS "2023年2月固定租金", MAX(CASE WHEN finance_period = '2023-03' AND fee_type = '01' THEN rece_amount END) AS "2023年3月固定租金", MAX(CASE WHEN finance_period = '2023-04' AND fee_type = '01' THEN rece_amount END) AS "2023年4月固定租金", MAX(CASE WHEN finance_period = '2023-05' AND fee_type = '01' THEN rece_amount END) AS "2023年5月固定租金", MAX(CASE WHEN finance_period = '2023-01' AND fee_type = '1010' THEN rece_amount END) AS "2023年1月提成租金", MAX(CASE WHEN finance_period = '2023-02' AND fee_type = '1010' THEN rece_amount END) AS "2023年2月提成租金", MAX(CASE WHEN finance_period = '2023-03' AND fee_type = '1010' THEN rece_amount END) AS "2023年3月提成租金", MAX(CASE WHEN finance_period = '2023-04' AND fee_type = '1010' THEN rece_amount END) AS "2023年4月提成租金", MAX(CASE WHEN finance_period = '2023-05' AND fee_type = '1010' THEN rece_amount END) AS "2023年5月提成租金", MAX(CASE WHEN finance_period = '2023-01' AND fee_type = '02' THEN rece_amount END) AS "2023年1月物业管理费", MAX(CASE WHEN finance_period = '2023-02' AND fee_type = '02' THEN rece_amount END) AS "2023年2月物业管理费", MAX(CASE WHEN finance_period = '2023-03' AND fee_type = '02' THEN rece_amount END) AS "2023年3月物业管理费", MAX(CASE WHEN finance_period = '2023-04' AND fee_type = '02' THEN rece_amount END) AS "2023年4月物业管理费", MAX(CASE WHEN finance_period = '2023-05' AND fee_type = '02' THEN rece_amount END) AS "2023年5月物业管理费" FROM FIRCVWZZZZ WHERE finance_period BETWEEN '2023-01' AND '2023-05' AND is_del = '0' GROUP BY cont_no ) SELECT DISTINCT l.MALL_NAME "项目", t.CONT_NO "合同编号", t.COMPANY_NAME "租户", t.STORE_NOS "铺位号", (SELECT STRING_AGG(r.FLOOR_NAME, ',') FROM bfbfbf r WHERE r.id = ANY (ARRAY( SELECT CAST(SUBSTRING(REGEXP_SPLIT_TO_TABLE(t1.store_ids, ','), '\d+') AS INTEGER) FROM bc t1 WHERE t1.CONT_NO = t.CONT_NO))) AS "楼层", t.BRAND_NAME "品牌", l1.DICT_NAME "一级业态", l2.DICT_NAME "二级业态", l3.DICT_NAME "三级业态", t.LAYOUT_NAME "签约业态", t.SHARE_SQUARE "套内面积", t.RENT_SQUARE "计租面积", t.STRUCTURE_SQUARE "建筑面积", t.CONT_BEGIN_DATE A, IFNULL(CONT_FAIL_DATE, CONT_END_DATE) B, CASE t."STATUS" WHEN '0' THEN '未签约' WHEN '1' THEN '已审核' WHEN '2' THEN '已解约' WHEN '3' THEN '待审核' WHEN '5' THEN '已结算' ELSE '未知类型' END "状态", (SELECT RENT_TYPE FROM bccdq e WHERE e.CONT_NO = t.CONT_NO AND e.IS_DEL = '0' AND e.RENT_TYPE IS NOT NULL ORDER BY e.CREATED_DATE DESC LIMIT 1) AS "计租方式", m1."2023年1月销售额" , m1."2023年1月销售笔数" , m1."2023年2月销售额" , m1."2023年2月销售笔数" , m1."2023年3月销售额" , m1."2023年3月销售笔数" , m1."2023年4月销售额" , m1."2023年4月销售笔数" , m1."2023年5月销售额" , m1."2023年5月销售笔数" , fr."2023年1月固定租金" , fr."2023年2月固定租金" , fr."2023年3月固定租金" , fr."2023年4月固定租金" , fr."2023年5月固定租金" , fr."2023年1月提成租金" , fr."2023年2月提成租金" , fr."2023年3月提成租金" , fr."2023年4月提成租金" , fr."2023年5月提成租金" , fr."2023年1月物业管理费" , fr."2023年2月物业管理费" , fr."2023年3月物业管理费" , fr."2023年4月物业管理费" , fr."2023年5月物业管理费" FROM bc t LEFT JOIN bms l ON l.id = t.MALL_ID LEFT JOIN (SELECT * FROM bsggldddict WHERE "LEVEL" = 4) l4 ON l4.ID = t.LAYOUT LEFT JOIN (SELECT * FROM bsggldddict WHERE "LEVEL" = 3) l3 ON (l3.ID = t.LAYOUT OR l4.PARENT_ID = l3.ID) LEFT JOIN (SELECT * FROM bsggldddict WHERE "LEVEL" = 2) l2 ON (l2.ID = t.LAYOUT OR l3.PARENT_ID = l2.ID) LEFT JOIN (SELECT * FROM bsggldddict WHERE "LEVEL" = 1) l1 ON l2.PARENT_ID = l1.ID LEFT JOIN m1 ON (m1.CONT_NO = t.CONT_NO) LEFT JOIN fr ON (fr.cont_no = t.cont_no) /* LEFT JOIN (SELECT cont_no, rent_type, ROW_NUMBER() OVER (PARTITION BY cont_no ORDER BY created_date DESC) AS rn FROM bccdq WHERE is_del = '0' AND rent_type IS NOT NULL) e ON (e.CONT_NO = t.CONT_NO) */ WHERE l.MALL_TYPE = '1' AND t.IS_DEL = '0' AND t.STATUS IN (1, 2, 5) AND t.CONT_BEGIN_DATE <= '2023-05-31' AND IFNULL(CONT_FAIL_DATE, CONT_END_DATE) >= '2023-01-01';
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- Unique (cost=96438.36..96440.62 rows=21 width=1058) (actual time=484.161..486.031 rows=1912 loops=1) -> Sort (cost=96438.36..96438.41 rows=21 width=1058) (actual time=484.160..484.290 rows=1912 loops=1) Sort Key: l.MALL_NAME, t.CONT_NO, t.COMPANY_NAME, t.STORE_NOS, ((SubPlan 2)), t.BRAND_NAME, bsggldddict_3.DICT_NAME, bsggldddict_2.DICT_NAME, bsggldddict_1.DICT_NAME, t.LAYOUT_NAME, t.SHARE_SQUARE, t.RENT_SQUARE, t.STRUCTURE_SQUARE, t.CONT_BEGIN_DATE, (IFNULL((t.C ONT_FAIL_DATE)::timestamp without time zone, (t.CONT_END_DATE)::timestamp without time zone)), (CASE t.STATUS WHEN '0'::bpchar THEN '未签约'::text WHEN '1'::bpchar THEN '已审核'::text WHEN '2'::bpchar THEN '已解约'::text WHEN '3'::bpchar THEN '待审核'::text WHEN '5'::bpchar THEN ' 结算'::text ELSE '未知类型'::text END), ((SubPlan 3)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202301'::text) THEN msamsuo.CONFIRM_AMOUNT ELSE NULL::numeric END)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202301'::text) THEN mng_sale_a mount_month.CONFIRM_COUNT ELSE NULL::numeric END)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202302'::text) THEN msamsuo.CONFIRM_AMOUNT ELSE NULL::numeric END)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202302'::text) THEN mng_sale_amou nt_month.CONFIRM_COUNT ELSE NULL::numeric END)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202303'::text) THEN msamsuo.CONFIRM_AMOUNT ELSE NULL::numeric END)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202303'::text) THEN mng_sale_amount_ month.CONFIRM_COUNT ELSE NULL::numeric END)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202304'::text) THEN msamsuo.CONFIRM_AMOUNT ELSE NULL::numeric END)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202304'::text) THEN mng_sale_amount_mon th.CONFIRM_COUNT ELSE NULL::numeric END)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202305'::text) THEN msamsuo.CONFIRM_AMOUNT ELSE NULL::numeric END)), (max(CASE WHEN ((msamsuo.SALE_YM)::text = '202305'::text) THEN msamsuo. CONFIRM_COUNT ELSE NULL::numeric END)), fr."2023年1月固定租金", fr."2023年2月固定租金", fr."2023年3月固定租金", fr."2023年4月固定租金", fr."2023年5月固定租金", fr."2023年1月提成租金", fr."2023年2月提成租金", fr."2023年3月提成租金", fr."2023年4月提成租金", fr."2023年5月提成租金", fr ."2023年1月物业管理费", fr."2023年2月物业管理费", fr."2023年3月物业管理费", fr."2023年4月物业管理费", fr."2023年5月物业管理费" Sort Method: quicksort Memory: 896kB -> Nested Loop Left Join (cost=94174.57..96437.90 rows=21 width=1058) (actual time=324.580..459.882 rows=1912 loops=1) Join Filter: ((bsggldddict_2.ID = t.LAYOUT) OR (bsggldddict_1.PARENT_ID = bsggldddict_2.ID)) Rows Removed by Join Filter: 28686 -> Nested Loop Left Join (cost=94171.16..94465.97 rows=21 width=983) (actual time=324.375..351.578 rows=1912 loops=1) Join Filter: ((bsggldddict_1.ID = t.LAYOUT) OR (bsggldddict.PARENT_ID = bsggldddict_1.ID)) Rows Removed by Join Filter: 103267 -> Hash Left Join (cost=94171.16..94442.28 rows=21 width=970) (actual time=324.350..335.203 rows=1912 loops=1) Hash Cond: (t.LAYOUT = bsggldddict.ID) -> Hash Right Join (cost=94167.45..94438.51 rows=21 width=962) (actual time=324.303..334.204 rows=1912 loops=1) Hash Cond: ((msamsuo.CONT_NO)::text = (t.CONT_NO)::text) -> HashAggregate (cost=19272.76..19386.83 rows=11407 width=331) (actual time=78.407..84.658 rows=5707 loops=1) Group Key: msamsuo.CONT_NO -> Bitmap Heap Scan on msamsuo (cost=575.04..17866.18 rows=26792 width=29) (actual time=24.886..36.601 rows=27189 loops=1) Recheck Cond: (((SALE_YM)::text >= '202301'::text) AND ((SALE_YM)::text <= '202305'::text)) Heap Blocks: exact=5919 -> Bitmap Index Scan on INX_SALE_YM_3DCD12EF (cost=0.00..568.34 rows=26792 width=0) (actual time=24.068..24.069 rows=27189 loops=1) Index Cond: (((SALE_YM)::text >= '202301'::text) AND ((SALE_YM)::text <= '202305'::text)) -> Hash (cost=74894.42..74894.42 rows=21 width=642) (actual time=245.872..245.876 rows=1912 loops=1) Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 508kB -> Hash Left Join (cost=73602.77..74894.42 rows=21 width=642) (actual time=232.789..243.568 rows=1912 loops=1) Hash Cond: ((t.CONT_NO)::text = (fr.cont_no)::text) -> Hash Join (cost=15.00..1306.59 rows=21 width=162) (actual time=0.131..9.620 rows=1912 loops=1) Hash Cond: (t.MALL_ID = l.ID) -> Seq Scan on bc t (cost=0.00..1291.40 rows=73 width=144) (actual time=0.014..8.737 rows=4357 loops=1) Filter: (((CONT_BEGIN_DATE)::timestamp without time zone <= '2023-05-31 00:00:00'::timestamp without time zone) AND (IFNULL((CONT_FAIL_DATE)::timestamp without time zone, (CONT_END_DATE)::timestamp without time zone) >= '2023 -01-01 00:00:00'::timestamp without time zone) AND (IS_DEL = '0'::bpchar) AND (((STATUS)::integer = 1) OR ((STATUS)::integer = 2) OR ((STATUS)::integer = 5))) Rows Removed by Filter: 11563 -> Hash (cost=14.10..14.10 rows=72 width=34) (actual time=0.109..0.110 rows=72 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 13kB -> Seq Scan on bms l (cost=0.00..14.10 rows=72 width=34) (actual time=0.005..0.092 rows=72 loops=1) Filter: ((MALL_TYPE)::text = '1'::text) Rows Removed by Filter: 176 -> Hash (cost=73455.92..73455.92 rows=10548 width=491) (actual time=232.634..232.636 rows=4893 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 678kB -> Subquery Scan on fr (cost=73244.96..73455.92 rows=10548 width=491) (actual time=221.100..230.052 rows=4893 loops=1) -> HashAggregate (cost=73244.96..73350.44 rows=10548 width=491) (actual time=221.099..229.259 rows=4893 loops=1) Group Key: FIRCVWZZZZ.CONT_NO -> Bitmap Heap Scan on FIRCVWZZZZ (cost=2683.78..67477.83 rows=50149 width=27) (actual time=69.354..92.470 rows=51955 loops=1) Recheck Cond: (((FINANCE_PERIOD)::text >= '2023-01'::text) AND ((FINANCE_PERIOD)::text <= '2023-05'::text) AND ((IS_DEL)::text = '0'::text)) Heap Blocks: exact=12083 -> Bitmap Index Scan on INX_FINANCE_PERIOD_RECE_NO_989A97EA (cost=0.00..2671.24 rows=50149 width=0) (actual time=67.535..67.535 rows=51955 loops=1) Index Cond: (((FINANCE_PERIOD)::text >= '2023-01'::text) AND ((FINANCE_PERIOD)::text <= '2023-05'::text) AND ((IS_DEL)::text = '0'::text)) -> Hash (cost=3.34..3.34 rows=30 width=16) (actual time=0.034..0.034 rows=30 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on bsggldddict (cost=0.00..3.34 rows=30 width=16) (actual time=0.022..0.028 rows=30 loops=1) Filter: ("LEVEL" = 4) Rows Removed by Filter: 77 -> Materialize (cost=0.00..3.61 rows=55 width=29) (actual time=0.000..0.003 rows=55 loops=1912) -> Seq Scan on bsggldddict bsggldddict_1 (cost=0.00..3.34 rows=55 width=29) (actual time=0.006..0.020 rows=55 loops=1) Filter: ("LEVEL" = 3) Rows Removed by Filter: 52 -> Materialize (cost=3.41..6.87 rows=16 width=34) (actual time=0.000..0.001 rows=16 loops=1912) -> Hash Left Join (cost=3.41..6.79 rows=16 width=34) (actual time=0.039..0.055 rows=16 loops=1) Hash Cond: (bsggldddict_2.PARENT_ID = bsggldddict_3.ID) -> Seq Scan on bsggldddict bsggldddict_2 (cost=0.00..3.34 rows=16 width=29) (actual time=0.004..0.015 rows=16 loops=1) Filter: ("LEVEL" = 2) Rows Removed by Filter: 91 -> Hash (cost=3.34..3.34 rows=6 width=21) (actual time=0.020..0.020 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on bsggldddict bsggldddict_3 (cost=0.00..3.34 rows=6 width=21) (actual time=0.004..0.014 rows=6 loops=1) Filter: ("LEVEL" = 1) Rows Removed by Filter: 101 SubPlan 2 -> Aggregate (cost=61.82..61.83 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=1912) InitPlan 1 (returns $1) -> Result (cost=0.29..30.81 rows=1000 width=4) (actual time=0.014..0.015 rows=1 loops=1912) -> ProjectSet (cost=0.29..13.31 rows=1000 width=32) (actual time=0.012..0.013 rows=1 loops=1912) -> Index Scan using idx_bc_1_2_3_4 on bc t1 (cost=0.29..8.30 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) -> Index Scan using IDX_ID_9460CCF1 on bfbfbf r (cost=0.28..30.98 rows=10 width=3) (actual time=0.002..0.002 rows=0 loops=1912) Index Cond: (ID = ANY (ARRAY[$1])) SubPlan 3 -> Limit (cost=31.45..31.45 rows=1 width=11) (actual time=0.032..0.032 rows=1 loops=1912) -> Sort (cost=31.45..31.46 rows=5 width=11) (actual time=0.032..0.032 rows=1 loops=1912) Sort Key: e.CREATED_DATE DESC Sort Method: top-N heapsort Memory: 25kB -> Bitmap Heap Scan on bccdq e (cost=4.47..31.42 rows=5 width=11) (actual time=0.018..0.027 rows=9 loops=1912) Recheck Cond: ((CONT_NO)::text = (t.CONT_NO)::text) Filter: ((RENT_TYPE IS NOT NULL) AND ((IS_DEL)::text = '0'::text)) Rows Removed by Filter: 6 Heap Blocks: exact=3014 -> Bitmap Index Scan on idx_cont_feetype_FED3F7C9 (cost=0.00..4.47 rows=7 width=0) (actual time=0.014..0.014 rows=15 loops=1912) Index Cond: ((CONT_NO)::text = (t.CONT_NO)::text) Planning Time: 4.134 ms Execution Time: 486.659 ms (88 行记录)
最后可以看到 486 ms 就能出结果，比刚才加索引优化也快了一半。
总结：作为PG的开发，对SQL的熟练度要求还是要蛮高的。