Amazon Redshift Compound Sort Key Query Optimization

Choose a data store.

Answer Correct answer: B, C — Filter on the leading columns of the compound sort key, as predicate order in the WHERE clause does not affect optimization.

A company stores employee data in Amazon Resdshift. A table names Employee uses columns named Region ID, Department ID, and Role ID as a compound sort key. Which queries will MOST increase the speed of query by using a compound sort key of the table? (Choose two.)

Select *from Employee where Region ID=’North America’;
Select *from Employee where Region ID=’North America’ and Department ID=20; Correct Answer
Select *from Employee where Department ID=20 and Region ID=’North America’; Correct Answer
Select *from Employee where Role ID=50;
Select *from Employee where Region ID=’North America’ and Role ID=50;

Community Votes

35%

32%

35% of anonymous learners picked answer BE. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

Compound sort keys are tested on leading column prefixes; the order of predicates in the WHERE clause does not impact the execution plan.

A compound sort key in Amazon Redshift improves query performance when filters are applied on the leading columns of the key. This page establishes that predicate order in the WHERE clause does not affect sort key utilization, making queries filtering on the first two sort key columns the most optimized.

Choosing A and B, assuming the predicate order must strictly match the sort key definition, or choosing B and E, incorrectly believing skipping the second column still fully utilizes the third.

Community Discussion (10 comments)

teo2157 👍 7 Selected: AB

To maximize the speed of queries using a compound sort key in Amazon Redshift, you should structure your queries to take advantage of the order of the columns in the sort key. The most efficient queries will filter or join on the columns in the same order as the sort key. Saying that, the most efficient queries would be: SELECT FROM Employee WHERE Region_ID = 'region1' AND Department_ID = 'dept1' AND Role_ID = 'role1'; SELECT FROM Employee WHERE Region_ID = 'region1' AND Department_ID = 'dept1'; SELECT * FROM Employee WHERE Region_ID = 'region1';

antun3ra 👍 6 Selected: BE

To maximize the speed of queries by using the compound sort key (Region ID, Department ID, and Role ID) in the Employee table in Amazon Redshift, the queries should align with the order of the columns in the sort key.

minhhnh 👍 2 Selected: BC

The filter order in the query is irrelevant to the performance because the sort key itself determines the storage order. So the execution plan is the same

HagarTheHorrible 👍 1 Selected: AB

E is not optimal bc of skipping of the second column.

altonh 👍 2 Selected: BC

The execution plan of these 2 queries should be the same.

RockyLeon 👍 2 Selected: BC

sort key works best with the first column in the sort key and continuing in sequential order

michele_scar 👍 2 Selected: AB

The order is the key to speed up queries

Parandhaman_Margan 👍 4

Answer:AB A:This query filters by Region ID, which is the first column in the compound sort key. Queries filtering on the leading sort key column(s) will benefit from optimized performance because the data can be quickly located. B: This query filters by both Region ID (the first column) and Department ID (the second column) in the sort key. This further narrows down the search space, leading to even faster query performance.

tucobbad 👍 4 Selected: BC

I would vote for B and C. I've tested with a compound sort key (3 columns) and even inverting predicate order the explain plan was the same.

Shanmahi 👍 5 Selected: BE

Based on the order of the compound sort key columns.

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

Options B and C both filter on the first two columns of the compound sort key (Region ID and Department ID). Amazon Redshift compound sort keys are most effective when queries filter on leading columns, and the logical order of predicates in the WHERE clause does not affect the optimizer's ability to leverage the sort key. Therefore, both B and C equally and most significantly increase query speed by utilizing the longest possible sort key prefix available in the options.

Why the Other Options Are Wrong

Option A only filters on the first column, providing less performance improvement than utilizing the first two columns. Option E filters on the first and third columns; because it skips the second column in the sort key, the third column cannot be used for zone map pruning, making it less effective than B or C. Option D filters only on the third column, completely skipping the leading columns, so it derives no benefit from the compound sort key.

Community Comment Notes

Several users correctly pointed out that the predicate order in the SQL query is irrelevant to performance, noting that "the filter order in the query is irrelevant" because the sort key determines storage order. One user confirmed this with testing, stating "even inverting predicate order the explain plan was the same." Others mistakenly focused strictly on the written order of the sort key, leading them to incorrectly include Option A or E.

Official Reference

https://docs.aws.amazon.com/redshift/latest/dg/t_Sorting_data.html

Exam Strategy

When evaluating Redshift compound sort key effectiveness, identify queries that filter on the longest unbroken prefix of the sort key columns. Remember that the order of conditions in the WHERE clause does not matter; only the presence of the leading columns dictates sort key utilization.

Related Analysis

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide