Optimize your analytical workloads utilizing the automated question rewrite function of Amazon Redshift materialized views

Amazon Redshift materialized views allow you to considerably enhance efficiency of advanced queries which are often run as a part of your extract, load, and rework (ELT), enterprise intelligence (BI), or dashboarding purposes. Materialized views precompute and retailer the end result units of the SQL question within the view definition. Materialized views pace up information entry, as a result of the question doesn’t must rerun the computation every time the question runs, which additionally reduces the useful resource consumption.

Amazon Redshift has the power to routinely rewrite your SQL queries that don’t explicitly reference present materialized views to make use of an present materialized view if it can enhance efficiency. This function is effective and, in some circumstances, the one possibility for efficiency optimization. Take into account packaged ISV apps and even simply studies— customers usually don’t have entry to the SQL to optimize. In some circumstances, even when they do have entry, the code or script is so previous that no one is conversant in it and also you don’t know what regressions even a small change may introduce.

On this put up, we describe how the automated question rewrite function works and a few eventualities the place you would make the most of this function. For details about the materialized view function itself, confer with Pace up your ELT and BI queries with Amazon Redshift materialized views and Creating materialized views in Amazon Redshift.

All examples on this put up are run on an 8 node ra3.4xlarge cluster with the 3 TB TPC-DS cloud benchmark dataset.

Let’s have a look at three completely different eventualities the place the automated question rewrite function may assist: optimizing joins between two massive tables, optimizing joins for tables which have a number of be a part of paths, and optimizing desk scans.

Optimize joins between two massive tables

There are lots of conditions the place you will have two massive tables which are joined often. On this case, making a materialized view that joins these two tables may assist enhance the efficiency of these queries. Materialized views precompute the be a part of and retailer the outcomes so subsequent runs solely must retrieve the saved outcomes; no must run the costly JOINs every time. With computerized question rewrite, not one of the end-user queries must be modified to confer with the materialized view. When creating the clarify plan for the question, Amazon Redshift replaces the be a part of between the 2 tables with the materialized view.

By default, the automated question rewrite makes use of a materialized view provided that it’s updated and displays all adjustments from its base tables. Because of this the question isn’t rewritten to make use of the materialized view if the bottom tables have newer updates that aren’t but mirrored within the materialized view.

For instance, think about the next SQL question. The question joins two tables: store_sales (8,639,936,081 rows) and buyer (30,000,000 rows):

SELECT 
cust.c_customer_id 
FROM store_sales gross sales
INNER JOIN buyer cust
ON gross sales.ss_customer_sk = cust.c_customer_sk
GROUP BY cust.c_customer_id;

The question runs in 545,520 milliseconds; the next is the clarify plan for the question:

XN HashAggregate  (price=9602679386653.98..9602679386653.98 rows=29705556 width=20)
  ->  XN Hash Be part of DS_BCAST_INNER  (price=375000.00..9602659714194.54 rows=7868983773 width=20)
        Hash Cond: (""outer"".ss_customer_sk = ""inside"".c_customer_sk)
        ->  XN Seq Scan on store_sales gross sales  (price=0.00..86399365.12 rows=8245454518 width=4)
              Filter: (ss_customer_sk IS NOT NULL)
        ->  XN Hash  (price=300000.00..300000.00 rows=30000000 width=24)
              ->  XN Seq Scan on buyer cust  (price=0.00..300000.00 rows=30000000 width=24)

Let’s create a materialized view that pre-computes the be a part of between the store_sales and buyer tables utilizing the next SQL assertion:

CREATE MATERIALIZED VIEW cust_store_sales
AS 
SELECT         
  cust.c_customer_id
, cust.c_first_name
, cust.c_last_name
, gross sales.ss_item_sk
, gross sales.ss_quantity
, cust.c_current_addr_sk
FROM  store_sales gross sales
INNER JOIN buyer cust
ON gross sales.ss_customer_sk = cust.c_customer_sk;

Let’s now rerun the unique question:

SELECT 
cust.c_customer_id 
FROM store_sales gross sales
INNER JOIN buyer cust
ON gross sales.ss_customer_sk = cust.c_customer_sk
GROUP BY cust.c_customer_sk;

The question runs a lot quicker (46,493 milliseconds). That is due to the automated question rewrite function, which has rewritten the previous question to make use of the newly created materialized view as a substitute of becoming a member of each tables. The clarify plan for this question reveals this variation:

XN HashAggregate  (price=103138905.60..103138905.60 rows=29705556 width=20)
  ->  XN Seq Scan on mv_tbl__cust_store_sales__0 derived_table1  (price=0.00..82511124.48 rows=8251112448 width=20)

The unique question run additionally consumed 1,263 CPU seconds and skim 45,013 blocks of knowledge, whereas the question that ran after the creation of the materialized view solely consumed 898 CPU seconds and skim 29,256 blocks. That could be a discount of 29% in CPU consumption and 35% in blocks learn.

The optimizer may rewrite the next question to make use of the beforehand created materialized view, which incorporates the extra be a part of to the customer_address desk:

SELECT
cust.c_customer_id
,addr.ca_state
FROM store_sales gross sales
INNER JOIN buyer cust
ON gross sales.ss_customer_sk = cust.c_customer_sk
INNER JOIN customer_address addr
ON cust.c_current_addr_sk = addr.ca_address_sk
GROUP BY cust.c_customer_id, addr.ca_state;
     
      XN HashAggregate  (price=30242919089.37..30242919089.37 rows=1544688912 width=26)
         ->  XN Hash Be part of DS_BCAST_INNER  (price=542661.20..30201663527.13 rows=8251112448 width=26)
        Hash Cond: ("outer".c_current_addr_sk = "inside".ca_address_sk)
        ->  XN Seq Scan on mv_tbl__cust_store_sales_1__0 derived_table1  (price=0.00..82511124.48 rows=8251112448 width=24)
        ->  XN Hash  (price=150000.00..150000.00 rows=15000000 width=10)
              ->  XN Seq Scan on customer_address addr  (price=0.00..150000.00 rows=15000000 width=10)

Optimize joins for tables which have a number of be a part of paths

For big tables on Amazon Redshift, the best distribution model could be ‘KEY’, with the distribution key being the column that’s used most often within the JOIN clause. There are conditions the place some massive tables have a number of be a part of paths. 50% of the queries could use a specific column to hitch to the desk, and the opposite 50% of the queries could use a distinct column to hitch to the desk. Each sorts of queries are vital and have stringent efficiency necessities. On this case, you would decide one column because the distribution key for the desk after which create a materialized view with the second column because the distribution key. That is attainable as a result of materialized views can have their very own distribution and type keys.

Right here’s an instance for example how this works.

The web_sales desk (2,159,968,881 rows) has the distribution key ws_order_number. This helps optimize a majority of the queries (70% of the joins to this desk use ws_order_number because the be a part of column). The remaining 30% use the column ws_bill_customer_sk to hitch to the desk, as proven within the following SQL assertion. This question took 12,790 milliseconds to run.

SELECT 
  c_customer_id
, c_email_address 
FROM web_sales ws
INNER JOIN buyer cs
ON ws.ws_bill_customer_sk=cs.c_customer_sk;

We will create the materialized view to assist enhance the efficiency of the remaining 30% of the queries. Notice the DISTKEY key phrase within the following code. We’ve got outlined a brand new distribution key for the materialized view (ws_bill_customer_sk):

CREATE MATERIALIZED VIEW web_sales_cust_dist
DISTKEY (ws_bill_customer_sk)
AS
SELECT * FROM web_sales;

Rerunning the next question returns rows a lot quicker than earlier than (7,715 milliseconds vs. 12,790 milliseconds):

SELECT 
  c_customer_id
, c_email_address 
FROM web_sales ws
INNER JOIN buyer cs
ON ws.ws_bill_customer_sk=cs.c_customer_sk;

Once more, the clarify plan of the question has modified; it now references the materialized view despite the fact that the SQL assertion doesn’t explicitly reference the materialized view:

XN Hash Be part of DS_DIST_NONE  (price=375000.00..696964927.69 rows=2159968768 width=74)
  Hash Cond: (""outer"".ws_bill_customer_sk = ""inside"".c_customer_sk)
  ->  XN Seq Scan on mv_tbl__web_sales_cust_dist__0 derived_table1  (price=0.00..21599687.68 rows=2159968768 width=4)
  ->  XN Hash  (price=300000.00..300000.00 rows=30000000 width=78)
        ->  XN Seq Scan on buyer cs  (price=0.00..300000.00 rows=30000000 width=78)

Optimize desk scans

Desk scans on Amazon Redshift are made environment friendly by means of using type keys. Kind keys decide the order by which the columns are saved within the information blocks. Choosing a column that seems often in your filtering situations as a kind key can enhance question efficiency considerably.

Compound type keys with a number of columns may be outlined in your desk in case a number of columns are good candidates for type keys. However in some conditions the place two or extra excessive cardinality columns are type key candidates, the compound type key could not present satisfactory efficiency. In these circumstances, a materialized view might be created with a distinct type key to take care of that information in an alternate sorted order to assist cater to a subset of the queries.

Within the following instance question, the web_sales desk makes use of the column ws_sold_date_sk for the type key, as a result of that is the column that’s used generally for filtering rows. A smaller set of queries use ws_sales_price for filtering rows. Provided that each ws_sold_date_sk and ws_sales_price are excessive cardinality columns with a number of distinctive values, a compound type key with each columns might not be performant for all question patterns.

SELECT *
FROM web_sales 
WHERE ws_sales_price BETWEEN 50 AND 100;

Let’s create the next materialized view and see the way it will help enhance the efficiency of the previous question:

CREATE MATERIALIZED VIEW web_sales_sort_on_price
SORTKEY (ws_sales_price)
AS
SELECT * FROM web_sales;

Operating the next question returns rows a lot quicker (5 milliseconds vs. 3,548 milliseconds) as a result of the automated question rewrite is utilizing the materialized view:

SELECT *
FROM web_sales 
WHERE ws_sales_price BETWEEN 50 AND 100;

The next is the brand new clarify plan:

XN Seq Scan on mv_tbl__web_sales_cust_dist__0 derived_table1  (price=0.00..32399531.52 rows=10799844 width=260)
  Filter: ((ws_sales_price <= 100.00) AND (ws_sales_price >= 50.00))

Conclusion

Materialized views on Amazon Redshift generally is a highly effective optimization software if used appropriately. With computerized question rewrite, you’ll be able to optimize queries with none impression to end-users or their queries. This lets you create materialized views after the appliance has gone dwell. Some prospects plan this as a part of their performance-optimization technique when constructing new apps. The actual worth is which you could optimize queries and workloads without having to switch the supply code or scripts, and you may profit even with a partial match.

Concerning the Authors

Harshida Patel is a Specialist Sr. Options Architect, Analytics with AWS.

Jeetesh Srivastva is a Sr. Supervisor, Specialist Options Architect at AWS. He makes a speciality of Amazon Redshift and works with prospects to implement scalable options utilizing Amazon Redshift and different AWS Analytic companies. He has labored to ship on-premises and cloud-based analytic options for purchasers in banking and finance and hospitality trade verticals.

Sain Das is an Analytics Specialist Options Architect at AWS and helps prospects construct scalable cloud options that assist flip information into actionable insights.

Somdeb Bhattacharjee is an Enterprise Options Architect at AWS.