Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

STL_EXPLAIN

Displays the EXPLAIN plan for a query that has been submitted for execution.

This table is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of Data in System Tables and Views.

Table Columns

Column Name Data Type Description
userid integer ID of the user who generated the entry.
query integer Query ID. The query column can be used to join other system tables and views.
nodeid integer Plan node identifier, where a node maps to one or more steps in the execution of the query.
parentid integer Plan node identifier for a parent node. A parent node has some number of child nodes. For example, a merge join is the parent of the scans on the joined tables.
plannode character(400) The node text from the EXPLAIN output. Plan nodes that refer to execution on compute nodes are prefixed with XN in the EXPLAIN output.
info character(400) Qualifier and filter information for the plan node. For example, join conditions and WHERE clause restrictions are included in this column.

Sample Queries

Consider the following EXPLAIN output for an aggregate join query:

Copy
explain select avg(datediff(day, listtime, saletime)) as avgwait from sales, listing where sales.listid = listing.listid; QUERY PLAN ------------------------------------------------------------------------------ XN Aggregate (cost=6350.30..6350.31 rows=1 width=16) -> XN Hash Join DS_DIST_NONE (cost=47.08..6340.89 rows=3766 width=16) Hash Cond: ("outer".listid = "inner".listid) -> XN Seq Scan on listing (cost=0.00..1924.97 rows=192497 width=12) -> XN Hash (cost=37.66..37.66 rows=3766 width=12) -> XN Seq Scan on sales (cost=0.00..37.66 rows=3766 width=12) (6 rows)

If you run this query and its query ID is 10, you can use the STL_EXPLAIN table to see the same kind of information that the EXPLAIN command returns:

Copy
select query,nodeid,parentid,substring(plannode from 1 for 30), substring(info from 1 for 20) from stl_explain where query=10 order by 1,2; query| nodeid |parentid| substring | substring -----+--------+--------+--------------------------------+------------------- 10 | 1 | 0 |XN Aggregate (cost=6717.61..6 | 10 | 2 | 1 | -> XN Merge Join DS_DIST_NO| Merge Cond:("outer" 10 | 3 | 2 | -> XN Seq Scan on lis | 10 | 4 | 2 | -> XN Seq Scan on sal | (4 rows)

Consider the following query:

Copy
select event.eventid, sum(pricepaid) from event, sales where event.eventid=sales.eventid group by event.eventid order by 2 desc; eventid | sum --------+---------- 289 | 51846.00 7895 | 51049.00 1602 | 50301.00 851 | 49956.00 7315 | 49823.00 ...
If this query's ID is 15, the following system table query returns the plan nodes that were executed. In this case, the order of the nodes is reversed to show the actual order of execution:
Copy
select query,nodeid,parentid,substring(plannode from 1 for 56) from stl_explain where query=15 order by 1, 2 desc; query|nodeid|parentid| substring -----+------+--------+-------------------------------------------------------- 15 | 8 | 7 | -> XN Seq Scan on eve 15 | 7 | 5 | -> XN Hash(cost=87.98..87.9 15 | 6 | 5 | -> XN Seq Scan on sales(cos 15 | 5 | 4 | -> XN Hash Join DS_DIST_OUTER(cos 15 | 4 | 3 | -> XN HashAggregate(cost=862286577.07.. 15 | 3 | 2 | -> XN Sort(cost=1000862287175.47..10008622871 15 | 2 | 1 | -> XN Network(cost=1000862287175.47..1000862287197. 15 | 1 | 0 |XN Merge(cost=1000862287175.47..1000862287197.46 rows=87 (8 rows)

The following query retrieves the query IDs for any query plans that contain a window function:

Copy
select query, trim(plannode) from stl_explain where plannode like '%Window%'; query| btrim -----+------------------------------------------------------------------------ 26 | -> XN Window(cost=1000985348268.57..1000985351256.98 rows=170 width=33) 27 | -> XN Window(cost=1000985348268.57..1000985351256.98 rows=170 width=33) (2 rows)