为了更快的定位您的问题，请提供以下信息，谢谢
【业务影响】
【StarRocks版本】例如：2.5.3
【集群规模】例如：3fe（4C）+3be（16C）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】例如：社区群1-峻，谢谢
使用异步物化视图，30分钟更新一次，平常查询很快，但是在某个时间（大概是异步执行后1-2分钟内，后台正在做同步更新？）查下特别慢，比视图还要慢。这样直接拉低了这个SQL的下限。请问一下关于这点有什么优化呢？
请问您是直接查询的sql还是查询的物化视图名称，视图的数据量大概是多大。请提供下脱敏的视图的创建语句
直接查询物化视图名称再做一些关联，主表2000W左右，其余表几百万以下
CREATE MATERIALIZED VIEW webull_rt_db_gl.dim_user_register_info_us_mv
DISTRIBUTED BY HASH(gl_region, user_id)
REFRESH ASYNC START(‘2023-07-11 12:40:00’) EVERY (interval
10 MINUTE) AS
SELECT
t1.gl_region AS gl_region,
t1.user_id AS user_id,
coalesce(t1.final_channel, ‘null’) AS final_channel,
t1.register_time AS register_time,
t1.register_address AS register_address,
t1.register_os AS register_os,
t1.register_version AS register_version,
t1.register_account_status AS register_account_status,
t1.register_platform AS register_platform,
t2.code AS code,
t2.primary_channel AS channel_1,
t2.secondary_channel AS channel_2,
t2.third_channel AS channel_3,
t2.fourth_channel AS channel_4,
t2.region AS region
FROM
(
SELECT
u.gl_region AS gl_region,
u.user_id AS user_id,
if(
(i.invite_channel IS NULL)
OR (i.invite_channel = ‘’),
if(
(u.client_channel IS NULL)
OR (u.client_channel = ‘’),
a.app_channel,
u.client_channel
),
if(
(i.invite_code_source IS NOT NULL)
AND (
i.invite_code_source IN (‘invite_bd’, ‘invite_default’)
),
if(
i.invite_code_source = ‘invite_bd’,
i.invite_channel,
concat(
‘normal_invite_’,
coalesce(u.client_channel, ‘’)
)
),
if(
i.source LIKE ‘wb_kol_%’,
i.invite_channel,
concat(
‘normal_invite_’,
coalesce(u.client_channel, ‘’)
)
)
)
) AS final_channel,
u.create_time AS register_time,
u.register_address AS register_address,
u.os AS register_os,
u.ver AS register_version,
u.status AS register_account_status,
u.platform AS register_platform
FROM
webull_rt_db_gl.ods_wlu_user_us AS u
LEFT OUTER JOIN webull_rt_db_gl.dim_user_invite_channel_us AS i ON u.user_id = i.invited_user_id
LEFT OUTER JOIN (
SELECT
a.user_id AS user_id,
a.app_channel AS app_channel
FROM
(
SELECT
af.user_id AS user_id,
af.app_channel AS app_channel,
row_number() OVER (
PARTITION BY af.user_id
ORDER BY
af.sort_field ASC
) AS rn
FROM
webull_rt_db_gl.dim_user_appsflyer_channel_us_view as af
) a
WHERE
a.rn = 1
) a ON u.user_id = a.user_id
) t1
LEFT OUTER JOIN webull_rt_db_gl.dim_t_promotion_channels AS t2 ON (
(t1.final_channel = t2.code)
AND (t2.status = 1) AND (t2.parent_level = 4)
);
请您提供下查询物化视图慢时的profile我们分析下
这个sql耗时1s+是属于慢的么？您选的是耗时长的吗？
1s+还是可以接受的，还不是耗时长的，感觉做异步物化视图更新时查询就很慢了
很慢时您获取个profile·发下
你的profile里是dwd_execute_order_us_view这个视图，和你发的物化视图的创建语句不一致
这个是视图，视图里面包含了上面的物化视图
create view webull_rt_db_gl.dwd_execute_order_us_view(
id,
fin_account_id,
transact_time,
strike_amount,
strike_qty,
ticker_type,
is_multi_leg,
create_time,
modify_time,
user_id,
broker_id,
broker_account_id,
supaccount_id,
supaccount_type,
channel_1,
remark,
continent
) comment ‘’ as (
select
t1.id,
t1.fin_account_id,
t1.transact_time,
t1.strike_amount,
t1.strike_qty,
t1.ticker_type,
t1.is_multi_leg,
t1.create_time,
t1.modify_time,
t2.user_id,
t2.broker_id,
t2.broker_account_id,
t2.supaccount_id,
t2.supaccount_type,
t3.channel_1,
t5.remark,
t5.continent
from
webull_rt_db_gl.ods_execute_order_us t1
left join
webull_rt_db_gl.dim_user_boaccount_info_mv t2 on t1.fin_account_id = t2.fin_account_id
left join
webull_rt_db_gl.dim_user_register_info_us_mv t3 on t2.user_id = t3.user_id
left join
webull_rt_db_gl.dim_wlt_register_info_us t4 on t2.user_id = t4.user_id and t2.broker_id = t4.broker_id
left join
webull_rt_db_gl.dim_region t5 on t4.nationality = t5.open_region
)