Looker Studio在BigQuery中的查询性能优化问询

我准备了约1.8GB的样本数据，存储在三个独立表中，随后创建了包含700万条记录的物化视图关联这三张表。基于该物化视图，我在Looker Studio中制作了一份报表，无缓存状态下初始加载数据耗时约18-20秒。为测试最坏场景，我在报表中添加了一个展示所有维度/列的表格。

Looker Studio为生成该表格发出的查询如下：

SELECT t0.address, t0.agent_name, t0.amount, t0.customer_gender, t0.customer_id, t0.customer_name, t0.food_item, t0.phone_number, DATETIME_TRUNC(SAFE_CAST(t0.date AS DATETIME), SECOND) AS t0_qt_gjznucnwed, t0.rating, t0.zipCode FROM `project_id.my_dataset.data_mat_view` AS t0 GROUP BY t0.address, t0.agent_name, t0.amount, t0.customer_gender, t0.customer_id, t0.customer_name, t0.food_item, t0.phone_number, t0_qt_gjznucnwed, t0.rating, t0.zipCode ORDER BY t0.food_item DESC LIMIT 2000001;

目前我已尝试对数据及物化视图做分区和聚类，也启用了3GB容量的BI-engine，但报表性能没有明显提升。

我想明确：

• 这种场景下能否将报表加载时间控制在5秒以内？
• 若可以，具体该怎么实现？
• 若不行，这属于预期表现吗？当数据量达到3TB时，性能会维持现状吗？

补充说明 #

关联三张表创建物化视图的查询语句如下：

SELECT
t2.food_item as food_item,
t2.amount as amount,
t1.address as address,
t3.agent_name as agent_name ,
t1.gender as customer_gender,
t1.customer_id as customer_id,
t1.customer_name as customer_name,
t1.date as date,
t3.phone_number as phone_number,
t1.zip_code as zipCode,
t3.rating as rating
FROM
`project_id.my_dataset.customers_data` as t1
JOIN
`project_id.my_dataset.orders_data` as t2
ON
t1.customer_id = t2.customer_id
JOIN
`project_id.my_dataset.agents_data` as t3
ON
t2.order_id = t3.order_id

内容的提问来源于stack exchange，提问作者Ankit Atrey