Redshift MV vs Table

Question

suppose I have a fact table named my_table.  Now I create a MV for it without any filter nor aggregation. 
like this:
```
create materialized view public.my_mv diststyle key
distkey (distkey_column)
sortkey
(
      sort_key_column1,
      sort_key_column2 ...
) auto refresh yes
as 
select * from my_table
```
the MV has the same distkey style and sortKeys.
do we get any benefits from using the MV instead of base table?

Accepted Answer

the scenario you mentioned, where the materialized view (MV) has the same distribution key, sort keys, and no additional filters or aggregations, you would not gain any significant benefits by using the MV instead of the base table.

Materialized views are useful in cases where you want to:

* Precompute complex or time-consuming aggregations: Since the MV stores the pre-computed results, queries using the MV will be faster.
* Optimize the data for specific query patterns: You can choose different distribution keys or sort keys to optimize the MV for certain query patterns, improving performance.
* Reduce the load on the base table: If you have a highly concurrent system with many queries, using an MV can help offload some of the load from the base table, improving overall performance.

However, in your example, since the MV and the base table have identical distribution keys, sort keys, and no additional filters or aggregations, the MV is essentially just a redundant copy of the base table. Querying either the base table or the MV would yield a similar performance.

In this scenario, it would be more beneficial to simply use the base table and avoid the overhead of managing and refreshing the materialized view.

Redshift MV vs Table

Relevant content