EXPLAIN operators for Amazon Redshift query plans

This section briefly describes the operators that you see most often in the EXPLAIN output.

Sequential scan

The sequential scan operator (Seq Scan) indicates a table scan. Seq Scan scans each column in the table sequentially from beginning to end and evaluates query constraints (in the WHERE clause) for every row.

Join operators

Amazon Redshift selects join operators based on the physical design of the tables being joined, the location of the data required for the join, and the specific requirements of the query itself.

Nested loop

A nested loop is used mainly for cross-joins. Cross-joins are joins without a join condition that result in the Cartesian product of two tables. Nested loops are typically run as nested loop joins, which are the slowest of the possible join types. If a nested loop is present, you could see a nested loop alert event in the STL_ALERT_EVENT_LOG view. You can run the following query to identify the queries that have nested loops:

select q.query, 
       trim(q.querytxt) as sql_query, 
       q.starttime 
from stl_query q
join stl_alert_event_log l
    on l.query = q.query
    and l.event like 'Nested Loop Join in the query plan%' 
order by q.starttime desc;

Hash join and hash

Hash join and hash operators are typically faster than a nested loop join. These operators are used for inner joins and left and right outer joins. You can use hash join and hash operators when joining tables where the join columns are not both distribution keys and sort keys. The hash operator creates the hash table for the inner table in the join. The hash join operator reads the outer table, hashes the joining column, and finds matches in the inner hash table.

Merge join

The merge join operator is typically the fastest join and is used for inner joins and outer joins. The merge join isn't used for full joins. You can use a merge join when joining tables where the join columns are both distribution keys and sort keys, and when less than 20 percent of the joining tables are unsorted. The merge join operator reads two sorted tables in order and finds the matching rows. To view the percent of unsorted rows, query the SVV_TABLE_INFO system table.

Aggregate operators

The query plan uses the following operators in queries that involve aggregate functions and GROUP BY operations:

• Aggregate – Operator for scalar aggregate functions such as AVG and SUM

• HashAggregate – Operator for unsorted grouped aggregate functions

• GroupAggregate – Operator for sorted grouped aggregate functions

Sort operators

The query plan uses the following operators when queries have to sort or merge result sets:

• Sort – Evaluates the ORDER BY clause and other sort operations, such as sorts required by UNION queries and joins, SELECT DISTINCT queries, and window functions

• Merge – Produces final sorted results according to intermediate sorted results that derive from parallel operations

UNION, INTERSECT, and EXCEPT operators

The query plan uses the following operators for queries that involve set operations with UNION, INTERSECT, and EXCEPT:

• Subquery – Used to run UNION queries

• Hash Intersect Distinct – Used to run INTERSECT queries

• SetOp Except – Used to run EXCEPT (or MINUS) queries

Other operators

The following operators also appear frequently in EXPLAIN output for routine queries:

• Unique – Removes duplicates for SELECT DISTINCT queries and UNION queries

• Limit – Processes the LIMIT clause

• Window – Runs window functions

• Result – Runs scalar functions that don't involve any table access

• Subplan – Used for certain subqueries

• Network – Sends intermediate results to the leader node for further processing

• Materialize – Saves rows for input to nested loop joins and some merge joins