有如下一个SQL查询：
SELECT DISTINCT u.* FROM tsys_user u, tsys_user_right ur LEFT JOIN tsys_menu m ON m.trans_code = ur.trans_code LEFT JOIN tsys_trans tt ON m.menu_code = tt.trans_code AND tt.model_code != '-1' WHERE u.user_id = ur.user_id AND ( u.approval_status IS NULL OR u.approval_status != '1' ) AND ( ur.right_enable IS NULL OR ur.right_enable IN ( '', '1' ) ) AND m.tree_idx LIKE concat( '','#bizroot#TCMP#', '%' ) union SELECT distinct u.* FROM (select distinct m.tree_idx,rr.role_code,rr.right_flag,m.menu_code,rr.trans_code,rr.sub_trans_code from tsys_role_right rr LEFT JOIN tsys_menu m ON m.menu_code = rr.trans_code LEFT JOIN tsys_trans tt ON m.menu_code = tt.trans_code AND tt.model_code != '-1' where m.tree_idx LIKE concat( '','#bizroot#TCMP#', '%' )) as rr, tsys_user u, tsys_role r, tsys_role_user ru WHERE u.user_id = ru.user_code AND rr.role_code = ru.role_code AND rr.right_flag = ru.right_flag AND rr.role_code = r.role_code AND ( u.approval_status IS NULL OR u.approval_status != '1' ) AND ( r.approval_status IS NULL OR r.approval_status != '1' ) AND r.role_status = '1' -- AND rr.tree_idx LIKE concat( '','#bizroot#scheduler_ui#', '%' ) AND NOT EXISTS ( SELECT 'X' FROM tsys_user_right ur WHERE ur.trans_code = rr.trans_code AND ur.sub_trans_code = rr.sub_trans_code AND ur.right_flag = rr.right_flag AND ur.right_enable = '0' AND ur.user_id = u.user_id );
explain执行计划如下：
QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ HashAggregate (cost=6333.49..6385.82 rows=5233 width=11792) | Group Key: u.user_id, u.user_name, u.user_pwd, u.org_id, u.user_type, u.user_status, u.lock_status, u.create_date, u.modify_date, u.pass_modify_date, u.mobile, u.email, u.ext_flag, u.remark, u.ext_field_1, u.ext_field_2, u.ext_field_3, u.ext_field_4, u.| -> Append (cost=2251.18..5535.46 rows=5233 width=11792) | -> HashAggregate (cost=2251.18..2296.72 rows=4554 width=174) | Group Key: u.user_id, u.user_name, u.user_pwd, u.org_id, u.user_type, u.user_status, u.lock_status, u.create_date, u.modify_date, u.pass_modify_date, u.mobile, u.email, u.ext_flag, u.remark, u.ext_field_1, u.ext_field_2, u.ext_field_3, u.ext| -> Hash Join (cost=353.81..1465.50 rows=5152 width=174) | Hash Cond: ((ur.user_id)::text = (u.user_id)::text) | -> Hash Join (cost=30.95..1129.11 rows=5152 width=5) | Hash Cond: ((ur.trans_code)::text = (m.trans_code)::text) | -> Seq Scan on tsys_user_right ur (cost=0.00..904.24 rows=28480 width=26) | Filter: ((right_enable IS NULL) OR ((right_enable)::text = ANY ('{"",1}'::text[]))) | -> Hash (cost=29.30..29.30 rows=132 width=44) | -> Seq Scan on tsys_menu m (cost=0.00..29.30 rows=132 width=44) | Filter: ((tree_idx)::text ~~ concat('', '#bizroot#TCMP#', '%')) | -> Hash (cost=265.93..265.93 rows=4554 width=174) | -> Seq Scan on tsys_user u (cost=0.00..265.93 rows=4554 width=174) | Filter: ((approval_status IS NULL) OR ((approval_status)::text <> '1'::text)) | -> Unique (cost=3055.00..3160.24 rows=679 width=174) | -> Sort (cost=3055.00..3056.70 rows=679 width=174) | Sort Key: u_1.user_id, u_1.user_name, u_1.user_pwd, u_1.org_id, u_1.user_type, u_1.user_status, u_1.lock_status, u_1.create_date, u_1.modify_date, u_1.pass_modify_date, u_1.mobile, u_1.email, u_1.ext_flag, u_1.remark, u_1.ext_field_1, | -> Nested Loop Anti Join (cost=1806.14..3023.06 rows=679 width=174) | Join Filter: ((ur_1.user_id)::text = (u_1.user_id)::text) | -> Nested Loop (cost=1805.85..2176.48 rows=683 width=216) | -> Merge Join (cost=1805.57..1917.32 rows=683 width=47) | Merge Cond: (((rr.right_flag)::text = (ru.right_flag)::text) AND ((rr.role_code)::text = (ru.role_code)::text)) | -> Sort (cost=836.65..842.51 rows=2345 width=55) | Sort Key: rr.right_flag, rr.role_code | -> Hash Join (cost=645.96..705.38 rows=2345 width=55) | Hash Cond: ((rr.role_code)::text = (r.role_code)::text) | -> HashAggregate (cost=637.04..663.24 rows=2620 width=130) | Group Key: m_1.tree_idx, rr.role_code, rr.right_flag, m_1.menu_code, rr.trans_code, rr.sub_trans_code | -> Hash Join (cost=30.95..597.74 rows=2620 width=130) | Hash Cond: ((rr.trans_code)::text = (m_1.menu_code)::text) | -> Seq Scan on tsys_role_right rr (cost=0.00..513.63 rows=20163 width=48) | -> Hash (cost=29.30..29.30 rows=132 width=82) | -> Seq Scan on tsys_menu m_1 (cost=0.00..29.30 rows=132 width=82) | Filter: ((tree_idx)::text ~~ concat('', '#bizroot#TCMP#', '%')) | -> Hash (cost=6.69..6.69 rows=179 width=7) | -> Seq Scan on tsys_role r (cost=0.00..6.69 rows=179 width=7) | Filter: (((approval_status IS NULL) OR ((approval_status)::text <> '1'::text)) AND ((role_status)::text = '1'::text)) | -> Sort (cost=968.92..998.03 rows=11645 width=13) | Sort Key: ru.right_flag, ru.role_code | -> Seq Scan on tsys_role_user ru (cost=0.00..182.45 rows=11645 width=13) | -> Index Scan using tsys_user_pkey on tsys_user u_1 (cost=0.28..0.38 rows=1 width=174) | Index Cond: ((user_id)::text = (ru.user_code)::text) | Filter: ((approval_status IS NULL) OR ((approval_status)::text <> '1'::text)) | -> Index Scan using idx_subcode_transcode on tsys_user_right ur_1 (cost=0.29..1.23 rows=1 width=58) | Index Cond: (((sub_trans_code)::text = (rr.sub_trans_code)::text) AND ((trans_code)::text = (rr.trans_code)::text)) | Filter: (((right_enable)::text = '0'::text) AND ((right_flag)::text = (rr.right_flag)::text)) |
还是挺复杂的，具有性能优化的典型意义了。
该语句要执行25秒左右，业务希望在1秒内。
先来看下explain (analyze,verbose,buffers,settings)的情况：
QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ HashAggregate (cost=2965.55..2974.38 rows=883 width=11792) (actual time=26462.092..26467.773 rows=3027 loops=1) | Output: u.user_id, u.user_name, u.user_pwd, u.org_id, u.user_type, u.user_status, u.lock_status, u.create_date, u.modify_date, u.pass_modify_date, u.mobile, u.email, u.ext_flag, u.remark, u.ext_field_1, u.ext_field_2, u.ext_field_3, u.ext_field_4, u.use| Group Key: u.user_id, u.user_name, u.user_pwd, u.org_id, u.user_type, u.user_status, u.lock_status, u.create_date, u.modify_date, u.pass_modify_date, u.mobile, u.email, u.ext_flag, u.remark, u.ext_field_1, u.ext_field_2, u.ext_field_3, u.ext_field_4, u.| Batches: 1 Memory Usage: 1185kB | Buffers: shared hit=1501006 | -> Append (cost=1390.36..2830.90 rows=883 width=11792) (actual time=5.465..26439.559 rows=3028 loops=1) | Buffers: shared hit=1501006 | -> HashAggregate (cost=1390.36..1398.17 rows=781 width=174) (actual time=5.463..5.489 rows=10 loops=1) | Output: u.user_id, u.user_name, u.user_pwd, u.org_id, u.user_type, u.user_status, u.lock_status, u.create_date, u.modify_date, u.pass_modify_date, u.mobile, u.email, u.ext_flag, u.remark, u.ext_field_1, u.ext_field_2, u.ext_field_3, u.ext_fi| Group Key: u.user_id, u.user_name, u.user_pwd, u.org_id, u.user_type, u.user_status, u.lock_status, u.create_date, u.modify_date, u.pass_modify_date, u.mobile, u.email, u.ext_flag, u.remark, u.ext_field_1, u.ext_field_2, u.ext_field_3, u.ext| Batches: 1 Memory Usage: 97kB | Buffers: shared hit=815 | -> Nested Loop (cost=0.69..1271.26 rows=781 width=174) (actual time=0.362..3.320 rows=235 loops=1) | Output: u.user_id, u.user_name, u.user_pwd, u.org_id, u.user_type, u.user_status, u.lock_status, u.create_date, u.modify_date, u.pass_modify_date, u.mobile, u.email, u.ext_flag, u.remark, u.ext_field_1, u.ext_field_2, u.ext_field_3, u.| Inner Unique: true | Buffers: shared hit=815 | -> Nested Loop (cost=0.41..1010.44 rows=781 width=5) (actual time=0.344..1.265 rows=235 loops=1) | Output: ur.user_id | Buffers: shared hit=110 | -> Seq Scan on public.tsys_menu m (cost=0.00..29.30 rows=20 width=44) (actual time=0.308..0.667 rows=21 loops=1) | Output: m.menu_code, m.kind_code, m.trans_code, m.sub_trans_code, m.menu_name, m.menu_arg, m.menu_icon, m.menu_url, m.window_type, m.window_model, m.tip, m.hot_key, m.parent_code, m.order_no, m.open_flag, m.tree_idx, m.rema| Filter: ((m.tree_idx)::text ~~ concat('', '#bizroot#scheduler_ui#', '%')) | Rows Removed by Filter: 466 | Buffers: shared hit=22 | -> Index Scan using tsys_user_right_pkey on public.tsys_user_right ur (cost=0.41..48.67 rows=39 width=26) (actual time=0.014..0.025 rows=11 loops=21) | Output: ur.trans_code, ur.sub_trans_code, ur.user_id, ur.create_by, ur.create_date, ur.begin_date, ur.end_date, ur.right_flag, ur.right_enable, ur.module_type, ur.action_type, ur.tenant_uuid, ur.tenant_id, ur.kind_code | Index Cond: ((ur.trans_code)::text = (m.trans_code)::text) | Filter: ((ur.right_enable IS NULL) OR ((ur.right_enable)::text = ANY ('{"",1}'::text[]))) | Buffers: shared hit=88 | -> Index Scan using tsys_user_pkey on public.tsys_user u (cost=0.28..0.33 rows=1 width=174) (actual time=0.007..0.007 rows=1 loops=235) | Output: u.user_id, u.user_name, u.user_pwd, u.org_id, u.user_type, u.user_status, u.lock_status, u.create_date, u.modify_date, u.pass_modify_date, u.mobile, u.email, u.ext_flag, u.remark, u.ext_field_1, u.ext_field_2, u.ext_field| Index Cond: ((u.user_id)::text = (ur.user_id)::text) | Filter: ((u.approval_status IS NULL) OR ((u.approval_status)::text <> '1'::text)) | Buffers: shared hit=705 | -> Unique (cost=1403.67..1419.48 rows=102 width=174) (actual time=25277.599..26433.641 rows=3018 loops=1) | Output: u_1.user_id, u_1.user_name, u_1.user_pwd, u_1.org_id, u_1.user_type, u_1.user_status, u_1.lock_status, u_1.create_date, u_1.modify_date, u_1.pass_modify_date, u_1.mobile, u_1.email, u_1.ext_flag, u_1.remark, u_1.ext_field_1, u_1.ext_| Buffers: shared hit=1500191 | -> Sort (cost=1403.67..1403.92 rows=102 width=174) (actual time=25277.594..25312.647 rows=271080 loops=1) | Output: u_1.user_id, u_1.user_name, u_1.user_pwd, u_1.org_id, u_1.user_type, u_1.user_status, u_1.lock_status, u_1.create_date, u_1.modify_date, u_1.pass_modify_date, u_1.mobile, u_1.email, u_1.ext_flag, u_1.remark, u_1.ext_field_1, u_| Sort Key: u_1.user_id, u_1.user_name, u_1.user_pwd, u_1.org_id, u_1.user_type, u_1.user_status, u_1.lock_status, u_1.create_date, u_1.modify_date, u_1.pass_modify_date, u_1.mobile, u_1.email, u_1.ext_flag, u_1.remark, u_1.ext_field_1, | Sort Method: quicksort Memory: 84491kB | Buffers: shared hit=1500191 | -> Nested Loop Anti Join (cost=168.33..1400.26 rows=102 width=174) (actual time=2.006..6410.412 rows=271080 loops=1) | Output: u_1.user_id, u_1.user_name, u_1.user_pwd, u_1.org_id, u_1.user_type, u_1.user_status, u_1.lock_status, u_1.create_date, u_1.modify_date, u_1.pass_modify_date, u_1.mobile, u_1.email, u_1.ext_flag, u_1.remark, u_1.ext_field| Join Filter: ((ur_1.user_id)::text = (u_1.user_id)::text) | Buffers: shared hit=1500191 | -> Nested Loop (cost=168.04..981.77 rows=103 width=216) (actual time=1.982..2942.376 rows=271080 loops=1) | Output: rr.right_flag, rr.trans_code, rr.sub_trans_code, u_1.user_id, u_1.user_name, u_1.user_pwd, u_1.org_id, u_1.user_type, u_1.user_status, u_1.lock_status, u_1.create_date, u_1.modify_date, u_1.pass_modify_date, u_1.mob| Inner Unique: true | Buffers: shared hit=813455 | -> Merge Join (cost=167.76..942.69 rows=103 width=47) (actual time=1.963..388.239 rows=271080 loops=1) | Output: rr.right_flag, rr.trans_code, rr.sub_trans_code, ru.user_code | Merge Cond: ((ru.right_flag)::text = (rr.right_flag)::text) | Join Filter: ((rr.role_code)::text = (ru.role_code)::text) | Rows Removed by Join Filter: 252945 | Buffers: shared hit=215 | -> Index Scan using indx_biz_roleuser_flag on public.tsys_role_user ru (cost=0.29..384.38 rows=11645 width=13) (actual time=0.083..7.860 rows=11645 loops=1) | Output: ru.user_code, ru.role_code, ru.right_flag, ru.status | Buffers: shared hit=124 | -> Sort (cost=167.47..168.36 rows=355 width=55) (actual time=1.474..63.049 rows=520948 loops=1) | Output: rr.role_code, rr.right_flag, rr.trans_code, rr.sub_trans_code, r.role_code | Sort Key: rr.right_flag | Sort Method: quicksort Memory: 38kB | Buffers: shared hit=91 | -> Hash Join (cost=143.43..152.44 rows=355 width=55) (actual time=1.315..1.421 rows=90 loops=1) | Output: rr.role_code, rr.right_flag, rr.trans_code, rr.sub_trans_code, r.role_code | Inner Unique: true | Hash Cond: ((rr.role_code)::text = (r.role_code)::text) | Buffers: shared hit=91 | -> HashAggregate (cost=134.51..138.48 rows=397 width=130) (actual time=1.081..1.128 rows=90 loops=1) | Output: m_1.tree_idx, rr.role_code, rr.right_flag, m_1.menu_code, rr.trans_code, rr.sub_trans_code | Group Key: m_1.tree_idx, rr.role_code, rr.right_flag, m_1.menu_code, rr.trans_code, rr.sub_trans_code | Batches: 1 Memory Usage: 61kB | Buffers: shared hit=87 | -> Nested Loop (cost=0.41..128.56 rows=397 width=130) (actual time=0.313..0.936 rows=90 loops=1) | Output: m_1.tree_idx, rr.role_code, rr.right_flag, m_1.menu_code, rr.trans_code, rr.sub_trans_code | Buffers: shared hit=87 | -> Seq Scan on public.tsys_menu m_1 (cost=0.00..29.30 rows=20 width=82) (actual time=0.284..0.621 rows=21 loops=1) | Output: m_1.menu_code, m_1.kind_code, m_1.trans_code, m_1.sub_trans_code, m_1.menu_name, m_1.menu_arg, m_1.menu_icon, m_1.menu_url, m_1.window_type, m_1.window_model, m_1.tip, m_1.hot_key| Filter: ((m_1.tree_idx)::text ~~ concat('', '#bizroot#scheduler_ui#', '%')) | Rows Removed by Filter: 466 | Buffers: shared hit=22 | -> Index Only Scan using tsys_role_right_pkey on public.tsys_role_right rr (cost=0.41..4.76 rows=20 width=48) (actual time=0.011..0.013 rows=4 loops=21) | Output: rr.trans_code, rr.sub_trans_code, rr.role_code, rr.begin_date, rr.end_date, rr.right_flag | Index Cond: (rr.trans_code = (m_1.menu_code)::text) | Heap Fetches: 0 | Buffers: shared hit=65 | -> Hash (cost=6.69..6.69 rows=179 width=7) (actual time=0.222..0.224 rows=179 loops=1) | Output: r.role_code | Buckets: 1024 Batches: 1 Memory Usage: 15kB | Buffers: shared hit=4 | -> Seq Scan on public.tsys_role r (cost=0.00..6.69 rows=179 width=7) (actual time=0.011..0.130 rows=179 loops=1) | Output: r.role_code | Filter: (((r.approval_status IS NULL) OR ((r.approval_status)::text <> '1'::text)) AND ((r.role_status)::text = '1'::text)) | Buffers: shared hit=4 | -> Index Scan using tsys_user_pkey on public.tsys_user u_1 (cost=0.28..0.38 rows=1 width=174) (actual time=0.008..0.008 rows=1 loops=271080) | Output: u_1.user_id, u_1.user_name, u_1.user_pwd, u_1.org_id, u_1.user_type, u_1.user_status, u_1.lock_status, u_1.create_date, u_1.modify_date, u_1.pass_modify_date, u_1.mobile, u_1.email, u_1.ext_flag, u_1.remark, u| Index Cond: ((u_1.user_id)::text = (ru.user_code)::text) | Filter: ((u_1.approval_status IS NULL) OR ((u_1.approval_status)::text <> '1'::text)) | Buffers: shared hit=813240 | -> Index Scan using idx_subcode_transcode on public.tsys_user_right ur_1 (cost=0.29..4.05 rows=1 width=58) (actual time=0.011..0.011 rows=0 loops=271080) | Output: ur_1.trans_code, ur_1.sub_trans_code, ur_1.user_id, ur_1.create_by, ur_1.create_date, ur_1.begin_date, ur_1.end_date, ur_1.right_flag, ur_1.right_enable, ur_1.module_type, ur_1.action_type, ur_1.tenant_uuid, ur_1.te| Index Cond: (((ur_1.sub_trans_code)::text = (rr.sub_trans_code)::text) AND ((ur_1.trans_code)::text = (rr.trans_code)::text)) | Filter: (((ur_1.right_enable)::text = '0'::text) AND ((ur_1.right_flag)::text = (rr.right_flag)::text)) | Rows Removed by Filter: 5 | Buffers: shared hit=686736 | Settings: effective_cache_size = '128GB', enable_partitionwise_aggregate = 'on', enable_partitionwise_join = 'on', jit_above_cost = '1e+06', jit_inline_above_cost = '50000', jit_optimize_above_cost = '50000', max_parallel_workers_per_gather = '4', min_par| Planning: | Buffers: shared hit=108 | Planning Time: 7.923 ms | Execution Time: 26509.953 ms |
首先就该语句来说，work_mem、shared_buffers、effective_cache_size已足够大，所以不存在物理I/O瓶颈。应用不好拆分SQL语句修改，重点在执行计划本身的优化上了（不要说SQL这么复杂不合理，我们做自研数据库LightDB开发，客户原来用oracle，所以我们的目标就是跟oracle一样性能高效，尽可能支持各种优化器策略和算法）。
SQL优化在原生pg中就只有3个策略：1、优化器特性开关；2、SQL语句利用某些语义限制（如left join、distinct&order by子查询）重写；3、加减索引。或者借用pg_hint_plan，因为pg_hint_plan支持通过Set(k v)优化器提示修改GUC参数，所以可以认为覆盖了1（LightDB默认集成了pg_hint_plan，无需额外安装，直接可用）。
下面我们分析上述执行计划：
1、针对tsys_user_right表idx_subcode_transcode索引的anti join，走了nl，扫描次数太多，所以这个不应该走嵌套循环。可以通过set enable_nestloop=off;禁用，或/*+ Set(enable_nestloop off)*/。
2、merge join一般也不建议使用，所以也先禁用。
3、排序占用了太多的时间，但是返回的是user，所以可以先返回user_id，这样不用整个结果集排序，只要id即可，肯定更快。
explain (analyze,buffers) with rr as materialized ( select distinct m.tree_idx, rr.role_code, rr.right_flag, m.menu_code, rr.trans_code, rr.sub_trans_code from tsys_role_right rr left join tsys_menu m on m.menu_code = rr.trans_code left join tsys_trans tt on m.menu_code = tt.trans_code and tt.model_code != '-1' where m.tree_idx like concat( '', '#bizroot#BIZFRAME#', '%' )) select * from tsys_user tu where tu.user_id in ( select u.user_id from tsys_user u, tsys_user_right ur left join tsys_menu m on m.trans_code = ur.trans_code left join tsys_trans tt on m.menu_code = tt.trans_code and tt.model_code != '-1' where u.user_id = ur.user_id and ( u.approval_status is null or u.approval_status != '1' ) and ( ur.right_enable is null or ur.right_enable in ( '', '1' ) ) and m.tree_idx like concat( '', '#bizroot#BIZFRAME#', '%' ) union select u.user_id from rr, tsys_user u, tsys_role r, tsys_role_user ru where u.user_id = ru.user_code and rr.role_code = ru.role_code and rr.right_flag = ru.right_flag and rr.role_code = r.role_code and ( u.approval_status is null or u.approval_status != '1' ) and ( r.approval_status is null or r.approval_status != '1' ) and r.role_status = '1' -- AND rr.tree_idx LIKE concat( '','#bizroot#scheduler_ui#', '%' ) and not exists ( select 'X' from tsys_user_right ur where ur.trans_code = rr.trans_code and ur.sub_trans_code = rr.sub_trans_code and ur.right_flag = rr.right_flag and ur.right_enable = '0' and ur.user_id = u.user_id ));
QUERY PLAN | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=4616.35..4882.86 rows=883 width=174) (actual time=202.367..204.116 rows=3027 loops=1) | Hash Cond: ((tu.user_id)::text = (u.user_id)::text) | Buffers: shared hit=2083 | CTE rr | -> HashAggregate (cost=602.29..606.26 rows=397 width=130) (actual time=2.576..2.598 rows=90 loops=1) | Group Key: m_1.tree_idx, rr_1.role_code, rr_1.right_flag, m_1.menu_code, rr_1.trans_code, rr_1.sub_trans_code | Batches: 1 Memory Usage: 61kB | Buffers: shared hit=334 | -> Hash Join (cost=29.55..596.34 rows=397 width=130) (actual time=1.550..2.538 rows=90 loops=1) | Hash Cond: ((rr_1.trans_code)::text = (m_1.menu_code)::text) | Buffers: shared hit=334 | -> Seq Scan on tsys_role_right rr_1 (cost=0.00..513.63 rows=20163 width=48) (actual time=0.002..0.869 rows=20163 loops=1) | Buffers: shared hit=312 | -> Hash (cost=29.30..29.30 rows=20 width=82) (actual time=0.150..0.151 rows=21 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 11kB | Buffers: shared hit=22 | -> Seq Scan on tsys_menu m_1 (cost=0.00..29.30 rows=20 width=82) (actual time=0.068..0.146 rows=21 loops=1) | Filter: ((tree_idx)::text ~~ concat('', '#bizroot#scheduler_ui#', '%')) | Rows Removed by Filter: 466 | Buffers: shared hit=22 | -> Seq Scan on tsys_user tu (cost=0.00..254.54 rows=4554 width=174) (actual time=0.039..0.242 rows=4554 loops=1) | Buffers: shared hit=209 | -> Hash (cost=3999.06..3999.06 rows=883 width=82) (actual time=202.319..202.327 rows=3027 loops=1) | Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 145kB | Buffers: shared hit=1874 | -> HashAggregate (cost=3981.40..3990.23 rows=883 width=82) (actual time=201.774..201.994 rows=3027 loops=1) | Group Key: u.user_id | Batches: 1 Memory Usage: 369kB | Buffers: shared hit=1874 | -> Append (cost=352.41..3979.19 rows=883 width=82) (actual time=4.622..176.174 rows=271315 loops=1) | Buffers: shared hit=1874 | -> Hash Join (cost=352.41..1408.90 rows=781 width=5) (actual time=4.621..7.484 rows=235 loops=1) | Hash Cond: ((ur.user_id)::text = (u.user_id)::text) | Buffers: shared hit=746 | -> Hash Join (cost=29.55..1084.00 rows=781 width=5) (actual time=3.130..5.959 rows=235 loops=1) | Hash Cond: ((ur.trans_code)::text = (m.trans_code)::text) | Buffers: shared hit=537 | -> Seq Scan on tsys_user_right ur (cost=0.00..904.24 rows=28480 width=26) (actual time=0.005..4.247 rows=28470 loops=1) | Filter: ((right_enable IS NULL) OR ((right_enable)::text = ANY ('{"",1}'::text[]))) | Rows Removed by Filter: 2669 | Buffers: shared hit=515 | -> Hash (cost=29.30..29.30 rows=20 width=44) (actual time=0.159..0.159 rows=21 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 10kB | Buffers: shared hit=22 | -> Seq Scan on tsys_menu m (cost=0.00..29.30 rows=20 width=44) (actual time=0.073..0.153 rows=21 loops=1) | Filter: ((tree_idx)::text ~~ concat('', '#bizroot#scheduler_ui#', '%')) | Rows Removed by Filter: 466 | Buffers: shared hit=22 | -> Hash (cost=265.93..265.93 rows=4554 width=5) (actual time=1.475..1.476 rows=4554 loops=1) | Buckets: 8192 Batches: 1 Memory Usage: 231kB | Buffers: shared hit=209 | -> Seq Scan on tsys_user u (cost=0.00..265.93 rows=4554 width=5) (actual time=0.031..1.099 rows=4554 loops=1) | Filter: ((approval_status IS NULL) OR ((approval_status)::text <> '1'::text)) | Buffers: shared hit=209 | -> Hash Anti Join (cost=2270.29..2557.04 rows=102 width=5) (actual time=87.087..156.701 rows=271080 loops=1) | Hash Cond: (((rr.trans_code)::text = (ur_1.trans_code)::text) AND ((rr.sub_trans_code)::text = (ur_1.sub_trans_code)::text) AND ((rr.right_flag)::text = (ur_1.right_flag)::text) AND ((u_1.user_id)::text = (ur_1.user_id)::text))| Buffers: shared hit=1128 | -> Hash Join (cost=1314.29..1598.33 rows=103 width=875) (actual time=83.114..113.969 rows=271080 loops=1) | Hash Cond: ((u_1.user_id)::text = (ru.user_code)::text) | Buffers: shared hit=613 | -> Seq Scan on tsys_user u_1 (cost=0.00..265.93 rows=4554 width=5) (actual time=0.025..1.282 rows=4554 loops=1) | Filter: ((approval_status IS NULL) OR ((approval_status)::text <> '1'::text)) | Buffers: shared hit=209 | -> Hash (cost=1313.01..1313.01 rows=103 width=875) (actual time=83.081..83.084 rows=271080 loops=1) | Buckets: 524288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 30277kB | Buffers: shared hit=404 | -> Hash Join (cost=23.25..1313.01 rows=103 width=875) (actual time=2.730..31.974 rows=271080 loops=1) | Hash Cond: (((ru.role_code)::text = (rr.role_code)::text) AND ((ru.right_flag)::text = (rr.right_flag)::text)) | Buffers: shared hit=404 | -> Seq Scan on tsys_role_user ru (cost=0.00..182.45 rows=11645 width=13) (actual time=0.002..0.527 rows=11645 loops=1) | Buffers: shared hit=66 | -> Hash (cost=17.93..17.93 rows=355 width=1023) (actual time=2.718..2.720 rows=90 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 18kB | Buffers: shared hit=338 | -> Hash Join (cost=8.92..17.93 rows=355 width=1023) (actual time=2.639..2.704 rows=90 loops=1) | Hash Cond: ((rr.role_code)::text = (r.role_code)::text) | Buffers: shared hit=338 | -> CTE Scan on rr (cost=0.00..7.94 rows=397 width=1016) (actual time=2.578..2.624 rows=90 loops=1) | Buffers: shared hit=334 | -> Hash (cost=6.69..6.69 rows=179 width=7) (actual time=0.056..0.057 rows=179 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 15kB | Buffers: shared hit=4 | -> Seq Scan on tsys_role r (cost=0.00..6.69 rows=179 width=7) (actual time=0.005..0.040 rows=179 loops=1) | Filter: (((approval_status IS NULL) OR ((approval_status)::text <> '1'::text)) AND ((role_status)::text = '1'::text)) | Buffers: shared hit=4 | -> Hash (cost=904.24..904.24 rows=2588 width=58) (actual time=3.945..3.946 rows=2596 loops=1) | Buckets: 4096 Batches: 1 Memory Usage: 258kB | Buffers: shared hit=515 | -> Seq Scan on tsys_user_right ur_1 (cost=0.00..904.24 rows=2588 width=58) (actual time=0.038..3.372 rows=2596 loops=1) | Filter: ((right_enable)::text = '0'::text) | Rows Removed by Filter: 28543 | Buffers: shared hit=515 | Planning Time: 1.028 ms | Execution Time: 204.836 ms |
调整之后，针对scheduler_ui的查询已经很快了，300毫秒。针对另外一个条件XAFB则为1.7秒。但是到了默认的frame，还要10秒。如下：
QUERY PLAN | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=16299.80..16528.72 rows=10117 width=174) (actual time=9944.104..9947.221 rows=4369 loops=1) | Hash Cond: ((u.user_id)::text = (tu.user_id)::text) | Buffers: shared hit=2083 | CTE rr | -> HashAggregate (cost=667.12..712.57 rows=4545 width=130) (actual time=7.432..9.842 rows=9476 loops=1) | Group Key: m_1.tree_idx, rr_1.role_code, rr_1.right_flag, m_1.menu_code, rr_1.trans_code, rr_1.sub_trans_code | Batches: 1 Memory Usage: 2449kB | Buffers: shared hit=334 | -> Hash Join (cost=32.17..598.95 rows=4545 width=130) (actual time=0.203..3.934 rows=9476 loops=1) | Hash Cond: ((rr_1.trans_code)::text = (m_1.menu_code)::text) | Buffers: shared hit=334 | -> Seq Scan on tsys_role_right rr_1 (cost=0.00..513.63 rows=20163 width=48) (actual time=0.003..0.928 rows=20163 loops=1) | Buffers: shared hit=312 | -> Hash (cost=29.30..29.30 rows=229 width=82) (actual time=0.176..0.177 rows=227 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 32kB | Buffers: shared hit=22 | -> Seq Scan on tsys_menu m_1 (cost=0.00..29.30 rows=229 width=82) (actual time=0.008..0.143 rows=227 loops=1) | Filter: ((tree_idx)::text ~~ concat('', '#bizroot#BIZFRAME#', '%')) | Rows Removed by Filter: 260 | Buffers: shared hit=22 | -> HashAggregate (cost=15275.77..15376.94 rows=10117 width=82) (actual time=9943.152..9943.637 rows=4369 loops=1) | Group Key: u.user_id | Batches: 1 Memory Usage: 657kB | Buffers: shared hit=1874 | -> Append (cost=355.02..15250.47 rows=10117 width=82) (actual time=1.745..8185.464 rows=19452542 loops=1) | Buffers: shared hit=1874 | -> Hash Join (cost=355.02..1514.53 rows=8939 width=5) (actual time=1.743..8.721 rows=5199 loops=1) | Hash Cond: ((ur.user_id)::text = (u.user_id)::text) | Buffers: shared hit=746 | -> Hash Join (cost=32.17..1168.20 rows=8939 width=5) (actual time=0.251..6.371 rows=5199 loops=1) | Hash Cond: ((ur.trans_code)::text = (m.trans_code)::text) | Buffers: shared hit=537 | -> Seq Scan on tsys_user_right ur (cost=0.00..904.24 rows=28480 width=26) (actual time=0.006..4.213 rows=28470 loops=1) | Filter: ((right_enable IS NULL) OR ((right_enable)::text = ANY ('{"",1}'::text[]))) | Rows Removed by Filter: 2669 | Buffers: shared hit=515 | -> Hash (cost=29.30..29.30 rows=229 width=44) (actual time=0.207..0.208 rows=227 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 24kB | Buffers: shared hit=22 | -> Seq Scan on tsys_menu m (cost=0.00..29.30 rows=229 width=44) (actual time=0.008..0.167 rows=227 loops=1) | Filter: ((tree_idx)::text ~~ concat('', '#bizroot#BIZFRAME#', '%')) | Rows Removed by Filter: 260 | Buffers: shared hit=22 | -> Hash (cost=265.93..265.93 rows=4554 width=5) (actual time=1.456..1.457 rows=4554 loops=1) | Buckets: 8192 Batches: 1 Memory Usage: 231kB | Buffers: shared hit=209 | -> Seq Scan on tsys_user u (cost=0.00..265.93 rows=4554 width=5) (actual time=0.030..1.039 rows=4554 loops=1) | Filter: ((approval_status IS NULL) OR ((approval_status)::text <> '1'::text)) | Buffers: shared hit=209 | -> Hash Anti Join (cost=1451.88..13584.19 rows=1178 width=5) (actual time=21.894..7401.208 rows=19447343 loops=1) | Hash Cond: (((rr.trans_code)::text = (ur_1.trans_code)::text) AND ((rr.sub_trans_code)::text = (ur_1.sub_trans_code)::text) AND ((rr.right_flag)::text = (ur_1.right_flag)::text) AND ((u_1.user_id)::text = (ur_1.user_id)::text))| Buffers: shared hit=1128 | -> Hash Join (cost=495.88..12600.29 rows=1184 width=875) (actual time=17.965..4679.762 rows=19447423 loops=1) | Hash Cond: ((ru.user_code)::text = (u_1.user_id)::text) | Buffers: shared hit=613 | -> Hash Join (cost=173.03..12274.33 rows=1184 width=875) (actual time=16.476..2244.767 rows=19447511 loops=1) | Hash Cond: (((ru.role_code)::text = (rr.role_code)::text) AND ((ru.right_flag)::text = (rr.right_flag)::text)) | Buffers: shared hit=404 | -> Seq Scan on tsys_role_user ru (cost=0.00..182.45 rows=11645 width=13) (actual time=0.004..0.791 rows=11645 loops=1) | Buffers: shared hit=66 | -> Hash (cost=112.01..112.01 rows=4068 width=1023) (actual time=16.456..16.459 rows=9472 loops=1) | Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 856kB | Buffers: shared hit=338 | -> Hash Join (cost=8.92..112.01 rows=4068 width=1023) (actual time=7.498..14.520 rows=9472 loops=1) | Hash Cond: ((rr.role_code)::text = (r.role_code)::text) | Buffers: shared hit=338 | -> CTE Scan on rr (cost=0.00..90.90 rows=4545 width=1016) (actual time=7.434..12.918 rows=9476 loops=1) | Buffers: shared hit=334 | -> Hash (cost=6.69..6.69 rows=179 width=7) (actual time=0.056..0.057 rows=179 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 15kB | Buffers: shared hit=4 | -> Seq Scan on tsys_role r (cost=0.00..6.69 rows=179 width=7) (actual time=0.007..0.040 rows=179 loops=1) | Filter: (((approval_status IS NULL) OR ((approval_status)::text <> '1'::text)) AND ((role_status)::text = '1'::text)) | Buffers: shared hit=4 | -> Hash (cost=265.93..265.93 rows=4554 width=5) (actual time=1.452..1.452 rows=4554 loops=1) | Buckets: 8192 Batches: 1 Memory Usage: 231kB | Buffers: shared hit=209 | -> Seq Scan on tsys_user u_1 (cost=0.00..265.93 rows=4554 width=5) (actual time=0.019..0.972 rows=4554 loops=1) | Filter: ((approval_status IS NULL) OR ((approval_status)::text <> '1'::text)) | Buffers: shared hit=209 | -> Hash (cost=904.24..904.24 rows=2588 width=58) (actual time=3.921..3.922 rows=2596 loops=1) | Buckets: 4096 Batches: 1 Memory Usage: 258kB | Buffers: shared hit=515 | -> Seq Scan on tsys_user_right ur_1 (cost=0.00..904.24 rows=2588 width=58) (actual time=0.026..3.316 rows=2596 loops=1) | Filter: ((right_enable)::text = '0'::text) | Rows Removed by Filter: 28543 | Buffers: shared hit=515 | -> Hash (cost=254.54..254.54 rows=4554 width=174) (actual time=0.933..0.934 rows=4554 loops=1) | Buckets: 8192 Batches: 1 Memory Usage: 971kB | Buffers: shared hit=209 | -> Seq Scan on tsys_user tu (cost=0.00..254.54 rows=4554 width=174) (actual time=0.040..0.309 rows=4554 loops=1) | Buffers: shared hit=209 | Planning Time: 0.996 ms | Execution Time: 9947.867 ms |
hash join的这一步导致慢的根源，因为它的结果集大了，导致后面要么要建立哈希表、要么要扫描一遍，所以结果就会非常大。因此性能线性下降，后面每一次关联hash都要2秒2秒的增加，即使结果集很大，走哈希也不一定是个正确的路子。通过让再次优化，可以实现6.8秒。如下：
explain (analyze) with rr as materialized ( select distinct m.tree_idx, rr.role_code, rr.right_flag, m.menu_code, rr.trans_code, rr.sub_trans_code from tsys_role_right rr left join tsys_menu m on m.menu_code = rr.trans_code left join tsys_trans tt on m.menu_code = tt.trans_code and tt.model_code != '-1' where m.tree_idx like concat( '', '#bizroot#BIZFRAME#', '%' )), xxx as materialized ( select distinct ru.role_code , ru.right_flag, u.user_id from tsys_role r join tsys_role_user ru on r.role_code = ru.role_code and ( r.approval_status is null or r.approval_status != '1' ) and r.role_status = '1' join tsys_user u on u.user_id = ru.user_code and ( u.approval_status is null or u.approval_status != '1' )) select * from tsys_user tu where tu.user_id in ( select u.user_id from tsys_user u, tsys_user_right ur left join tsys_menu m on m.trans_code = ur.trans_code left join tsys_trans tt on m.menu_code = tt.trans_code and tt.model_code != '-1' where u.user_id = ur.user_id and ( u.approval_status is null or u.approval_status != '1' ) and ( ur.right_enable is null or ur.right_enable in ( '', '1' ) ) and m.tree_idx like concat( '', '#bizroot#BIZFRAME#', '%' ) union select distinct xxx.user_id from xxx join rr on rr.role_code = xxx.role_code and rr.right_flag = xxx.right_flag where -- AND rr.tree_idx LIKE concat( '','#bizroot#scheduler_ui#', '%' ) -- and not exists ( select 'X' from tsys_user_right ur where ur.trans_code = rr.trans_code and ur.sub_trans_code = rr.sub_trans_code and ur.right_flag = rr.right_flag and ur.right_enable = '0' and ur.user_id = xxx.user_id ))
GEQO也不智能。
如果一下子无头绪，可以看一下相同的语句在oracle和mysql中的表现，如果它们两个很快，可以参考它们的执行计划，下面是mysql的，即使是BIZFRAME，也只要1秒钟。
{ "query_block": { "union_result": { "table_name": "<union1,2>", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "temporary_table": { "table": { "table_name": "m", "access_type": "ALL", "possible_keys": ["FK_MENU_SOURCE"], "rows": 488, "filtered": 100, "attached_condition": "m.tree_idx like '#bizroot#BIZFRAME#%' and m.trans_code is not null" }, "table": { "table_name": "ur", "access_type": "ref", "possible_keys": ["PRIMARY", "FK_RIGHT_USER", "idx_user_id"], "key": "PRIMARY", "key_length": "602", "used_key_parts": ["trans_code"], "ref": ["bizframetcmp.m.trans_code"], "rows": 25, "filtered": 100, "attached_condition": "ur.right_enable is null or ur.right_enable in ('','1')" }, "table": { "table_name": "u", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "98", "used_key_parts": ["user_id"], "ref": ["bizframetcmp.ur.user_id"], "rows": 1, "filtered": 100, "attached_condition": "u.approval_status is null or u.approval_status <> '1'" } } } }, { "query_block": { "select_id": 2, "operation": "UNION", "temporary_table": { "table": { "table_name": "u", "access_type": "ALL", "possible_keys": ["PRIMARY"], "rows": 4674, "filtered": 100, "attached_condition": "u.approval_status is null or u.approval_status <> '1'" }, "table": { "table_name": "ru", "access_type": "ref", "possible_keys": [ "PRIMARY", "FK_ROLEUSER_ROLE", "INDX_BIZ_ROLEUSER_FLAG", "INDX_BIZ_ROLEUSER_RU" ], "key": "PRIMARY", "key_length": "98", "used_key_parts": ["user_code"], "ref": ["bizframetcmp.u.user_id"], "rows": 1, "filtered": 100, "using_index": true, "distinct": true }, "table": { "table_name": "r", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "194", "used_key_parts": ["role_code"], "ref": ["bizframetcmp.ru.role_code"], "rows": 1, "filtered": 100, "attached_condition": "(r.approval_status is null or r.approval_status <> '1') and r.role_status = '1'", "distinct": true }, "table": { "table_name": "<derived3>", "access_type": "ref", "possible_keys": ["key1"], "key": "key1", "key_length": "220", "used_key_parts": ["role_code", "right_flag"], "ref": ["bizframetcmp.ru.role_code", "bizframetcmp.ru.right_flag"], "rows": 19, "filtered": 100, "attached_condition": "rr.right_flag = ru.right_flag and !<in_optimizer>((rr.trans_code,rr.sub_trans_code,rr.right_flag,u.user_id),<exists>(subquery#4))", "distinct": true, "materialized": { "query_block": { "select_id": 3, "temporary_table": { "table": { "table_name": "m", "access_type": "ALL", "possible_keys": ["PRIMARY", "INDX_BIZ_MENU_CODE"], "rows": 488, "filtered": 100, "attached_condition": "m.tree_idx like '#bizroot#BIZFRAME#%'" }, "table": { "table_name": "rr", "access_type": "ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "602", "used_key_parts": ["trans_code"], "ref": ["bizframetcmp.m.menu_code"], "rows": 7, "filtered": 100, "using_index": true } } } } }, "subqueries": [ { "query_block": { "select_id": 4, "table": { "table_name": "ur", "access_type": "index_subquery", "possible_keys": [ "PRIMARY", "FK_RIGHT_USER", "idx_user_id", "idx_subcode_transcode" ], "key": "PRIMARY", "key_length": "1302", "used_key_parts": ["trans_code", "sub_trans_code", "user_id"], "ref": ["func", "func", "func"], "rows": 1, "filtered": 100 } } } ] } } } ] } } }