为性能创建Redshift视图

Question

我正面临创建视图红移与数据从胶水(S3)，我的情况是:

我有来自S3的三个表数据(数百万条记录)用于glue:

表

表B
表C

我有一个查询:

(group by day table A) join (group by day table B) join (group by day table C)

所以，我有3个选项:

• 选项1:

CREATE MATERIALIZED VIEW VVV AS
(group by day table A) join (group by day table B) join (group by day table C)

then query from VVV

• 选项2:

CREATE MATERIALIZED VIEW V1 AS
(group by day table A)
CREATE MATERIALIZED VIEW V2 AS
(group by day table B)
CREATE MATERIALIZED VIEW V3 AS
(group by day table C)

Then query from V1 join V2 join V3

• 选项3:

CREATE MATERIALIZED VIEW V1 AS
(table A)
CREATE MATERIALIZED VIEW V2 AS
(table B)
CREATE MATERIALIZED VIEW V3 AS
(table C)

Then query from (group by day V1) join (group by day V3) join (group by day V3)

所以我不知道什么是最好的性能

Answer 1

物化视图的价值在于以可能过时的值为代价，预先计算开销较大的查询的结果。为了最大化这个值，我怀疑作为物化视图的一部分做最多的工作将是最好的——选项1。

只要记住定期刷新物化视图，如果你每次使用都刷新你的视图，那么你不应该使用物化视图。这些是用于一次计算和多次使用结果