Optimizing Redshift Queries with Query Plans

The Query Optimization Workflow

When a query runs slowly, your goal is to understand why. Is the database doing unnecessary work? Is it moving large amounts of data across the network? The key to answering these questions is the query plan, which is Redshift's step-by-step recipe for executing your SQL.

Our approach will be two-fold:

1. Leveraging Automated Tooling: First, we'll explore automated features like Redshift Advisor and Workload Management (WLM) that proactively manage performance.
2. Manual Deep Dive with EXPLAIN: Then, we'll learn to manually generate and interpret a query plan to identify specific bottlenecks like inefficient joins and costly data movements.

1. Automated Performance Management

Before diving into manual analysis, it's essential to use the tools AWS provides. Often, they can identify and even fix common performance issues for you.

Redshift Advisor & Auto Table Optimization

Redshift Advisor is like having an automated DBA on your team. It analyzes your cluster's workload and provides specific, actionable recommendations for improving performance and reducing costs. These recommendations often point to suboptimal table design, such as missing sort keys or inefficient distribution styles.

Taking this a step further, Redshift now offers Auto Table Optimization, which can automatically apply some of these recommendations for you.

Workload Management (WLM) and Query Monitoring Rules (QMR)

Sometimes, a query is slow not because it's written badly, but because it's competing for resources with other queries. Workload Management (WLM) allows you to define queues to prioritize workloads. For example, you can ensure that critical dashboard queries get more resources than a large, ad-hoc analytical query. Automatic WLM simplifies this by managing memory and concurrency for you.

To protect the cluster from rogue queries, you can set up Query Monitoring Rules (QMR). These rules can automatically terminate or deprioritize queries that exceed certain thresholds, like running for too long or scanning too much data.

The following video from AWS re:Invent provides a great overview of these features.

2. Deep Dive: The EXPLAIN Command

When automated tools aren't enough, you need to roll up your sleeves and analyze the query plan yourself. You can generate this plan by prefixing your query with the EXPLAIN command.

EXPLAIN doesn't run the query; it just shows you the plan the optimizer has chosen. This plan details the sequence of operations, the join algorithms, and the data movement required to get the final result.

Understanding Parallel Execution

Redshift is a Massively Parallel Processing (MPP) system. A query is broken down into segments and streams that run in parallel across the cluster's compute nodes. Understanding this is key to reading a plan.

How to Read a Query Plan

A query plan can look intimidating, but the approach is straightforward:

• Read from the inside out: The most indented operations happen first.
• Read from the bottom up: The flow of execution generally moves from the bottom of the plan to the top.
• Look for the highest cost: The plan includes cost estimates for each step. Start your analysis by focusing on the most expensive operations, as they offer the greatest potential for improvement.

This article provides a practical guide to reading and understanding the structure of a query plan.

3. Deconstructing the Plan: Common Bottlenecks and Fixes

Now let's break down the most critical parts of the query plan, what they mean, and how to address the problems they might reveal. We'll use another resource to provide more narrative explanations.

Based on these resources, here are the three main areas to investigate in a query plan:

A. Join Operations

Joins are often the most expensive part of a query. The optimizer's choice of join algorithm has a massive impact on performance.

• Nested Loop Join:

  • What it means: This is a brute-force join, comparing every row from the outer table to every row of the inner table. It's the least efficient join type, with a complexity that can be quadratic.
  • Why it happens: Often occurs when there is no join condition (a CROSS JOIN) or when using inequality conditions (<, >).
  • What to do: Avoid cross joins on large tables if at all possible. This is a major red flag in a query plan.

• Hash Join:

  • What it means: The optimizer builds an in-memory hash table on the smaller (inner) table and then scans the larger (outer) table to find matches. This is much more efficient than a nested loop.
  • Why it happens: This is the most common join type when the tables are not perfectly optimized for joining.
  • What to do: This is generally acceptable. However, ensure statistics are up-to-date with ANALYZE so the optimizer correctly identifies the smaller table to build the hash table.

• Merge Join:

  • What it means: This is the fastest join type. It requires both tables to be pre-sorted on the join keys. The database can then read both tables in sorted order and simply "merge" them together.
  • Why it happens: This occurs when the join columns are also the SORTKEY and DISTKEY for the tables.
  • What to do: This is the ideal state. If your most critical and frequent join is a Hash Join, consider changing your table DDL to use the join columns as distribution and sort keys to enable a Merge Join.

B. Data Distribution (Network Movement)

In an MPP database, network cost is a silent killer. Moving data between compute nodes is extremely expensive. The query plan tells you exactly what data movement is happening.

• DS_DIST_NONE:

  • What it means: No data movement is needed. The joining rows are already on the same compute nodes (collocated).
  • What to do: This is the best-case scenario. It happens when tables are joined on their DISTKEY.

• DS_BCAST_INNER (Broadcast):

  • What it means: The entire inner table is copied and sent to every compute node.
  • Why it happens: The optimizer does this when one table is small.
  • What to do: This is efficient if the inner table is small (e.g., a small dimension table). If you see this on a large table, it's a major performance problem. The fix is to change the small table's distribution style to DISTSTYLE ALL, which permanently stores a copy on every node, avoiding the broadcast during query execution.

• DS_DIST_BOTH (Redistribution):

  • What it means: This is the worst-case scenario. Neither table is distributed on the join key, so both tables must be shuffled across the network to collocate the joining rows.
  • What to do: If you see this on a frequent, expensive join, you should change the table DDL. Set the DISTSTYLE to KEY and use the join column as the DISTKEY for both tables.

C. Sort Operations

• Sort:
  • What it means: The database has to perform an explicit, costly sort operation.
  • Why it happens: This is required for ORDER BY clauses, some GROUP BY operations, and to prepare for a Merge Join if the data isn't already sorted.
  • What to do: If a Sort is consistently happening on a column used in a WHERE clause or a JOIN, consider adding that column as a SORTKEY to the table. This allows the query optimizer to take advantage of the pre-sorted data and potentially skip the sort step. Pre-sorting also dramatically improves scan performance by allowing Redshift to use its zone maps to skip large blocks of data that don't contain relevant values.

Conclusion

In this lesson, you've learned a systematic process for troubleshooting and optimizing slow-running queries in Amazon Redshift. This is a powerful skill that allows you to directly impact the performance and efficiency of your data warehouse.

Key Takeaways:

• Always start with automated tools like Redshift Advisor and ensure WLM is configured to manage your workload.
• For manual tuning, use the EXPLAIN command to get the query plan.
• Read plans from the bottom up, focusing on the highest-cost operations first.
• Analyze join types: aim for Merge Join, be okay with Hash Join, and be very concerned about Nested Loop.
• Analyze data distribution: aim for DS_DIST_NONE, avoid broadcasting large tables (DS_BCAST_INNER), and fix DS_DIST_BOTH immediately by setting a common DISTKEY.
• Look for expensive Sort operations and eliminate them by defining an appropriate SORTKEY on your table.

Next Up:
We have now completed our module on data warehousing with Amazon Redshift. We've covered cluster design, loading data, optimizing performance, and securing the environment. In the next module, we'll shift our focus to the data lake itself. Our first lesson will be on how to optimize Athena queries using partitioning, bucketing, and compression, applying some similar principles of performance tuning to a serverless, interactive query environment.