有如下一个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秒的增加，即使结果集很大，走哈希也不一定是个正确的路子。如果一下子无头绪，可以看一下相同的语句在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
}
}
}
]
}
}
}
]
}
}
}
