Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# Query analysis and improvement
Retrieving information from an Amazon Redshift data warehouse involves running complex queries on extremely large amounts of data, which can take a long time to process. To make sure that queries process as quickly as possible, there are a number of tools you can use to identify potential performance issues.
**Topics**
+ [Query analysis workflow](c-query-analysis-process.md)
+ [Reviewing query alerts](c-reviewing-query-alerts.md)
+ [Analyzing the query plan](c-analyzing-the-query-plan.md)
+ [Analyzing the query summary](c-analyzing-the-query-summary.md)
+ [Query performance improvement](query-performance-improvement-opportunities.md)
+ [Diagnostic queries for query tuning](diagnostic-queries-for-query-tuning.md)
# Query analysis workflow
If a query is taking longer than expected, use the following steps to identify and correct issues that might be negatively affecting the query’s performance. If you aren’t sure what queries in your system might benefit from performance tuning, start by running the diagnostic query in [Identifying queries that are top candidates for tuning](identify-queries-that-are-top-candidates-for-tuning.md).
1. Make sure that your tables are designed according to best practices. For more information, see [Amazon Redshift best practices for designing tables](c_designing-tables-best-practices.md).
1. See if you can delete or archive any unneeded data in your tables. For example, suppose your queries always target the last 6 months’ worth of data but you have the last 18 months’ worth in your tables. In this case, you can delete or archive the older data to reduce the number of records that must be scanned and distributed.
1. Run the [VACUUM](r_VACUUM_command.md) command on the tables in the query to reclaim space and re-sort rows. Running VACUUM helps if the unsorted region is large and the query uses the sort key in a join or in the predicate.
1. Run the [ANALYZE](r_ANALYZE.md) command on the tables in the query to make sure that statistics are up to date. Running ANALYZE helps if any of the tables in the query have recently changed a lot in size. If running a full ANALYZE command will take too long, run ANALYZE on a single column to reduce processing time. This approach still updates the table size statistics; table size is a significant factor in query planning.
1. Make sure that your query has been run once for each type of client (based on what type of connection protocol the client uses) so that the query is compiled and cached. This approach speeds up subsequent runs of the query. For more information, see [Factors affecting query performance](c-query-performance.md).
1. Check the [STL\$1ALERT\$1EVENT\$1LOG](r_STL_ALERT_EVENT_LOG.md) table to identify and correct possible issues with your query. For more information, see [Reviewing query alerts](c-reviewing-query-alerts.md).
1. Run the [EXPLAIN](r_EXPLAIN.md) command to get the query plan and use it to optimize the query. For more information, see [Analyzing the query plan](c-analyzing-the-query-plan.md).
1. Use the [SVL\$1QUERY\$1SUMMARY](r_SVL_QUERY_SUMMARY.md) and [SVL\$1QUERY\$1REPORT](r_SVL_QUERY_REPORT.md) views to get summary information and use it to optimize the query. For more information, see [Analyzing the query summary](c-analyzing-the-query-summary.md).
Sometimes a query that should run quickly is forced to wait until another, longer-running query finishes. In that case, you might have nothing to improve in the query itself, but you can improve overall system performance by creating and using query queues for different types of queries. To get an idea of queue wait time for your queries, see [Reviewing queue wait times for queries](review-queue-wait-times-for-queries.md). For more information about configuring query queues, see [Workload management](cm-c-implementing-workload-management.md).
# Reviewing query alerts
To use the [STL\$1ALERT\$1EVENT\$1LOG](r_STL_ALERT_EVENT_LOG.md) system table to identify and correct potential performance issues with your query, follow these steps:
1. Run the following to determine your query ID:
```
select query, elapsed, substring
from svl_qlog
order by query
desc limit 5;
```
Examine the truncated query text in the `substring` field to determine which `query` value to select. If you have run the query more than once, use the `query` value from the row with the lower `elapsed` value. That is the row for the compiled version. If you have been running many queries, you can raise the value used by the LIMIT clause used to make sure that your query is included.
1. Select rows from STL\$1ALERT\$1EVENT\$1LOG for your query:
```
Select * from stl_alert_event_log where query = MyQueryID;
```
![\[A sample query result from STL_ALERT_EVENT_LOG.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/stl_alert_event_log_results.png)
1. Evaluate the results for your query. Use the following table to locate potential solutions for any issues that you have identified.
**Note**
Not all queries have rows in STL\$1ALERT\$1EVENT\$1LOG, only those with identified issues.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c-reviewing-query-alerts.html)
# Analyzing the query plan
Run the [EXPLAIN](r_EXPLAIN.md) command to get a query plan.
Before analyzing the query plan, you should be familiar with how to read it. If you are unfamiliar with reading a query plan, we recommend that you read [Creating and interpreting a query plan](c-the-query-plan.md) before proceeding.
To analyze the data provided by the query plan, follow these steps:
1. Identify the steps with the highest cost. Concentrate on optimizing those when proceeding through the remaining steps.
1. Look at the join types:
+ **Nested Loop**: Such joins usually occur because a join condition was omitted. For recommended solutions, see [Nested loop](query-performance-improvement-opportunities.md#nested-loop).
+ **Hash and Hash Join**: Hash joins are used when joining tables where the join columns are not distribution keys and also not sort keys. For recommended solutions, see [Hash join](query-performance-improvement-opportunities.md#hash-join).
+ **Merge Join**: No change is needed.
1. Notice which table is used for the inner join, and which for the outer join. The query engine generally chooses the smaller table for the inner join, and the larger table for the outer join. If such a choice doesn't occur, your statistics are likely out of date. For recommended solutions, see [Table statistics missing or out of date](query-performance-improvement-opportunities.md#table-statistics-missing-or-out-of-date).
1. See if there are any high-cost sort operations. If there are, see [Unsorted or missorted rows](query-performance-improvement-opportunities.md#unsorted-or-mis-sorted-rows) for recommended solutions.
1. Look for the following broadcast operators where there are high-cost operations:
+ **DS\$1BCAST\$1INNER**: Indicates that the table is broadcast to all the compute nodes. This is fine for a small table, but not ideal for a larger table.
+ **DS\$1DIST\$1ALL\$1INNER**: Indicates that all of the workload is on a single slice.
+ **DS\$1DIST\$1BOTH**: Indicates heavy redistribution.
For recommended solutions for these situations, see [Suboptimal data distribution](query-performance-improvement-opportunities.md#suboptimal-data-distribution).
# Analyzing the query summary
To get execution steps and statistics in more detail than in the query plan that [EXPLAIN](r_EXPLAIN.md) produces, use the [SVL\$1QUERY\$1SUMMARY](r_SVL_QUERY_SUMMARY.md) and [SVL\$1QUERY\$1REPORT](r_SVL_QUERY_REPORT.md) system views.
SVL\$1QUERY\$1SUMMARY provides query statistics by stream. You can use the information it provides to identify issues with expensive steps, long-running steps, and steps that write to disk.
The SVL\$1QUERY\$1REPORT system view enables you to see information similar to that for SVL\$1QUERY\$1SUMMARY, only by compute node slice rather than by stream. You can use the slice-level information for detecting uneven data distribution across the cluster (also known as data distribution skew), which forces some nodes to do more work than others and impairs query performance.
**Topics**
+ [Using the SVL\$1QUERY\$1SUMMARY view](using-SVL-Query-Summary.md)
+ [Using the SVL\$1QUERY\$1REPORT view](using-SVL-Query-Report.md)
+ [Mapping the query plan to the query summary](query-plan-summary-map.md)
# Using the SVL\$1QUERY\$1SUMMARY view
To analyze query summary information by stream using [SVL\$1QUERY\$1SUMMARY](r_SVL_QUERY_SUMMARY.md), do the following:
1. Run the following query to determine your query ID:
```
select query, elapsed, substring
from svl_qlog
order by query
desc limit 5;
```
Examine the truncated query text in the `substring` field to determine which `query` value represents your query. If you have run the query more than once, use the `query` value from the row with the lower `elapsed` value. That is the row for the compiled version. If you have been running many queries, you can raise the value used by the LIMIT clause used to make sure that your query is included.
1. Select rows from SVL\$1QUERY\$1SUMMARY for your query. Order the results by stream, segment, and step:
```
select * from svl_query_summary where query = MyQueryID order by stm, seg, step;
```
The following is an example result.
![\[A sample result for rows in SVL_QUERY_SUMMARY matching a given query.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/svl_query_summary_results.png)
1. Map the steps to the operations in the query plan using the information in [Mapping the query plan to the query summary](query-plan-summary-map.md). They should have approximately the same values for rows and bytes (rows \$1 width from the query plan). If they don’t, see [Table statistics missing or out of date](query-performance-improvement-opportunities.md#table-statistics-missing-or-out-of-date) for recommended solutions.
1. See if the `is_diskbased` field has a value of `t` (true) for any step. Hashes, aggregates, and sorts are the operators that are likely to write data to disk if the system doesn't have enough memory allocated for query processing.
If `is_diskbased` is true, see [Insufficient memory allocated to the query](query-performance-improvement-opportunities.md#insufficient-memory-allocated-to-the-query) for recommended solutions.
1. Review the `label` field values and see if there is an AGG-DIST-AGG sequence anywhere in the steps. Its presence indicates two-step aggregation, which is expensive. To fix this, change the GROUP BY clause to use the distribution key (the first key, if there are multiple ones).
1. Review the `maxtime` value for each segment (it is the same across all steps in the segment). Identify the segment with the highest `maxtime` value and review the steps in this segment for the following operators.
**Note**
A high `maxtime` value doesn't necessarily indicate a problem with the segment. Despite a high value, the segment might not have taken a long time to process. All segments in a stream start getting timed in unison. However, some downstream segments might not be able to run until they get data from upstream ones. This effect might make them seem to have taken a long time because their `maxtime` value includes both their waiting time and their processing time.
+ **BCAST or DIST**: In these cases, the high `maxtime` value might be the result of redistributing a large number of rows. For recommended solutions, see [Suboptimal data distribution](query-performance-improvement-opportunities.md#suboptimal-data-distribution).
+ **HJOIN (hash join)**: If the step in question has a very high value in the `rows` field compared to the `rows` value in the final RETURN step in the query, see [Hash join](query-performance-improvement-opportunities.md#hash-join) for recommended solutions.
+ **SCAN/SORT**: Look for a SCAN, SORT, SCAN, MERGE sequence of steps just before a join step. This pattern indicates that unsorted data is being scanned, sorted, and then merged with the sorted area of the table.
See if the rows value for the SCAN step has a very high value compared to the rows value in the final RETURN step in the query. This pattern indicates that the execution engine is scanning rows that are later discarded, which is inefficient. For recommended solutions, see [Insufficiently restrictive predicate](query-performance-improvement-opportunities.md#insufficiently-restrictive-predicate).
If the `maxtime` value for the SCAN step is high, see [Suboptimal WHERE clause](query-performance-improvement-opportunities.md#suboptimal-WHERE-clause) for recommended solutions.
If the `rows` value for the SORT step is not zero, see [Unsorted or missorted rows](query-performance-improvement-opportunities.md#unsorted-or-mis-sorted-rows) for recommended solutions.
1. Review the `rows` and `bytes` values for the 5–10 steps that precede the final RETURN step to get an idea of the amount of data that is returned to the client. This process can be a bit of an art.
For example, in the following sample query summary, the third PROJECT step provides a `rows` value, but not a `bytes` value. By looking through the preceding steps for one with the same `rows` value, you find the SCAN step that provides both rows and bytes information.
The following is a sample result.
![\[A row in the query summary results that is a SCAN step with both rows and bytes information.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/rows_and_bytes.png)
If you are returning an unusually large volume of data, see [Very large result set](query-performance-improvement-opportunities.md#very-large-result-set) for recommended solutions.
1. See if the `bytes` value is high relative to the `rows` value for any step, in comparison to other steps. This pattern can indicate that you are selecting a lot of columns. For recommended solutions, see [Large SELECT list](query-performance-improvement-opportunities.md#large-SELECT-list).
# Using the SVL\$1QUERY\$1REPORT view
To analyze query summary information by slice using [SVL\$1QUERY\$1REPORT](r_SVL_QUERY_REPORT.md), do the following:
1. Run the following to determine your query ID:
```
select query, elapsed, substring
from svl_qlog
order by query
desc limit 5;
```
Examine the truncated query text in the `substring` field to determine which `query` value represents your query. If you have run the query more than once, use the `query` value from the row with the lower `elapsed` value. That is the row for the compiled version. If you have been running many queries, you can raise the value used by the LIMIT clause used to make sure that your query is included.
1. Select rows from SVL\$1QUERY\$1REPORT for your query. Order the results by segment, step, elapsed\$1time, and rows:
```
select * from svl_query_report where query = MyQueryID order by segment, step, elapsed_time, rows;
```
1. For each step, check to see that all slices are processing approximately the same number of rows:
![\[A list of data slices used to run a query. Each slice processes approximately the same number of rows.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/SVL_QUERY_REPORT_rows.png)
Also check to see that all slices are taking approximately the same amount of time:
![\[A list of data slices used to run a query. Each slice takes approximately the same amount of time..\]](http://docs.aws.amazon.com/redshift/latest/dg/images/SVL_QUERY_REPORT_elapsed_time.png)
Large discrepancies in these values can indicate data distribution skew due to a suboptimal distribution style for this particular query. For recommended solutions, see [Suboptimal data distribution](query-performance-improvement-opportunities.md#suboptimal-data-distribution).
# Mapping the query plan to the query summary
When analyzing the query summary, you can get further details by mapping the operations from the query plan to the steps (identified by the label field values) in the query summary. The following table maps query plan operations to query summary steps.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/query-plan-summary-map.html)
# Query performance improvement
Following are some common issues that affect Amazon Redshift query performance, with instructions on ways to diagnose and resolve them.
**Topics**
+ [Table statistics missing or out of date](#table-statistics-missing-or-out-of-date)
+ [Nested loop](#nested-loop)
+ [Hash join](#hash-join)
+ [Ghost rows or uncommitted rows](#ghost-rows-or-uncommitted-rows)
+ [Unsorted or missorted rows](#unsorted-or-mis-sorted-rows)
+ [Suboptimal data distribution](#suboptimal-data-distribution)
+ [Insufficient memory allocated to the query](#insufficient-memory-allocated-to-the-query)
+ [Suboptimal WHERE clause](#suboptimal-WHERE-clause)
+ [Insufficiently restrictive predicate](#insufficiently-restrictive-predicate)
+ [Very large result set](#very-large-result-set)
+ [Large SELECT list](#large-SELECT-list)
## Table statistics missing or out of date
If table statistics are missing or out of date, you might see the following:
+ A warning message in EXPLAIN command results.
+ A missing statistics alert event in STL\$1ALERT\$1EVENT\$1LOG. For more information, see [Reviewing query alerts](c-reviewing-query-alerts.md).
To fix this issue, run [ANALYZE](r_ANALYZE.md).
## Nested loop
If a nested loop is present, you might see a nested loop alert event in STL\$1ALERT\$1EVENT\$1LOG. You can also identify this type of event by running the query at [Identifying queries with nested loops](identify-queries-with-nested-loops.md). For more information, see [Reviewing query alerts](c-reviewing-query-alerts.md).
To fix this, review your query for cross-joins and remove them if possible. Cross-joins are joins without a join condition that result in the Cartesian product of two tables. They are typically run as nested loop joins, which are the slowest of the possible join types.
## Hash join
If a hash join is present, you might see the following:
+ Hash and hash join operations in the query plan. For more information, see [Analyzing the query plan](c-analyzing-the-query-plan.md).
+ An HJOIN step in the segment with the highest maxtime value in SVL\$1QUERY\$1SUMMARY. For more information, see [Using the SVL\$1QUERY\$1SUMMARY view](using-SVL-Query-Summary.md).
To fix this issue, you can take a couple of approaches:
+ Rewrite the query to use a merge join if possible. You can do this by specifying join columns that are both distribution keys and sort keys.
+ If the HJOIN step in SVL\$1QUERY\$1SUMMARY has a very high value in the rows field compared to the rows value in the final RETURN step in the query, check whether you can rewrite the query to join on a unique column. When a query does not join on a unique column, such as a primary key, that increases the number of rows involved in the join.
## Ghost rows or uncommitted rows
If ghost rows or uncommitted rows are present, you might see an alert event in STL\$1ALERT\$1EVENT\$1LOG that indicates excessive ghost rows. For more information, see [Reviewing query alerts](c-reviewing-query-alerts.md).
To fix this issue, you can take a couple of approaches:
+ Check the **Loads** tab of your Amazon Redshift console for active load operations on any of the query tables. If you see active load operations, wait for those to complete before taking action.
+ If there are no active load operations, run [VACUUM](r_VACUUM_command.md) on the query tables to remove deleted rows.
## Unsorted or missorted rows
If unsorted or missorted rows are present, you might see a very selective filter alert event in STL\$1ALERT\$1EVENT\$1LOG. For more information, see [Reviewing query alerts](c-reviewing-query-alerts.md).
You can also check to see if any of the tables in your query have large unsorted areas by running the query in [Identifying tables with data skew or unsorted rows](identify-tables-with-data-skew-or-unsorted-rows.md).
To fix this issue, you can take a couple of approaches:
+ Run [VACUUM](r_VACUUM_command.md) on the query tables to re-sort the rows.
+ Review the sort keys on the query tables to see if any improvements can be made. Remember to weigh the performance of this query against the performance of other important queries and the system overall before making any changes. For more information, see [Sort keys](t_Sorting_data.md).
## Suboptimal data distribution
If data distribution is suboptimal, you might see the following:
+ A serial execution, large broadcast, or large distribution alert event appears in STL\$1ALERT\$1EVENT\$1LOG. For more information, see [Reviewing query alerts](c-reviewing-query-alerts.md).
+ Slices are not processing approximately the same number of rows for a given step. For more information, see [Using the SVL\$1QUERY\$1REPORT view](using-SVL-Query-Report.md).
+ Slices are not taking approximately the same amount of time for a given step. For more information, see [Using the SVL\$1QUERY\$1REPORT view](using-SVL-Query-Report.md).
If none of the preceding is true, you can also see if any of the tables in your query have data skew by running the query in [Identifying tables with data skew or unsorted rows](identify-tables-with-data-skew-or-unsorted-rows.md).
To fix this issue, review the distribution styles for the tables in the query and see if any improvements can be made. Remember to weigh the performance of this query against the performance of other important queries and the system overall before making any changes. For more information, see [Data distribution for query optimization](t_Distributing_data.md).
## Insufficient memory allocated to the query
If insufficient memory is allocated to your query, you might see a step in SVL\$1QUERY\$1SUMMARY that has an `is_diskbased` value of true. For more information, see [Using the SVL\$1QUERY\$1SUMMARY view](using-SVL-Query-Summary.md).
To fix this issue, allocate more memory to the query by temporarily increasing the number of query slots it uses. Workload Management (WLM) reserves slots in a query queue equivalent to the concurrency level set for the queue. For example, a queue with a concurrency level of 5 has 5 slots. Memory assigned to the queue is allocated equally to each slot. Assigning several slots to one query gives that query access to the memory for all of those slots. For more information on how to temporarily increase the slots for a query, see [wlm\$1query\$1slot\$1count](r_wlm_query_slot_count.md).
## Suboptimal WHERE clause
If your WHERE clause causes excessive table scans, you might see a SCAN step in the segment with the highest `maxtime` value in SVL\$1QUERY\$1SUMMARY. For more information, see [Using the SVL\$1QUERY\$1SUMMARY view](using-SVL-Query-Summary.md).
To fix this issue, add a WHERE clause to the query based on the primary sort column of the largest table. This approach helps minimize scanning time. For more information, see [Amazon Redshift best practices for designing tables](c_designing-tables-best-practices.md).
## Insufficiently restrictive predicate
If your query has an insufficiently restrictive predicate, you might see a SCAN step in the segment with the highest `maxtime` value in SVL\$1QUERY\$1SUMMARY that has a very high `rows` value compared to the `rows` value in the final RETURN step in the query. For more information, see [Using the SVL\$1QUERY\$1SUMMARY view](using-SVL-Query-Summary.md).
To fix this issue, try adding a predicate to the query or making the existing predicate more restrictive to narrow the output.
## Very large result set
If your query returns a very large result set, consider rewriting the query to use [UNLOAD](r_UNLOAD.md) to write the results to Amazon S3. This approach improves the performance of the RETURN step by taking advantage of parallel processing. For more information on checking for a very large result set, see [Using the SVL\$1QUERY\$1SUMMARY view](using-SVL-Query-Summary.md).
## Large SELECT list
If your query has an unusually large SELECT list, you might see a `bytes` value that is high relative to the `rows` value for any step (in comparison to other steps) in SVL\$1QUERY\$1SUMMARY. This high `bytes` value can be an indicator that you are selecting a lot of columns. For more information, see [Using the SVL\$1QUERY\$1SUMMARY view](using-SVL-Query-Summary.md).
To fix this issue, review the columns you are selecting and see if any can be removed.
# Diagnostic queries for query tuning
Use the following queries to identify issues with queries or underlying tables that can affect query performance. We recommend using these queries with the query tuning processes discussed in [Query analysis and improvement](c-query-tuning.md).
**Note**
These queries are for Amazon Redshift provisioned clusters. These queries are not for use with Redshift Serverless workgroups.
**Topics**
+ [Identifying queries that are top candidates for tuning](identify-queries-that-are-top-candidates-for-tuning.md)
+ [Identifying tables with data skew or unsorted rows](identify-tables-with-data-skew-or-unsorted-rows.md)
+ [Identifying queries with nested loops](identify-queries-with-nested-loops.md)
+ [Reviewing queue wait times for queries](review-queue-wait-times-for-queries.md)
+ [Reviewing query alerts by table](review-query-alerts-by-table.md)
+ [Identifying tables with missing statistics](identify-tables-with-missing-statistics.md)
# Identifying queries that are top candidates for tuning
The following query identifies the top 50 most time-consuming statements that have been run in the last 7 days. You can use the results to identify queries that are taking unusually long. You can also identify queries that are run frequently (those that appear more than once in the result set). These queries are frequently good candidates for tuning to improve system performance.
This query also provides a count of the alert events associated with each query identified. These alerts provide details that you can use to improve the query’s performance. For more information, see [Reviewing query alerts](c-reviewing-query-alerts.md).
```
select trim(database) as db, count(query) as n_qry,
max(substring (qrytext,1,80)) as qrytext,
min(run_minutes) as "min" ,
max(run_minutes) as "max",
avg(run_minutes) as "avg", sum(run_minutes) as total,
max(query) as max_query_id,
max(starttime)::date as last_run,
sum(alerts) as alerts, aborted
from (select userid, label, stl_query.query,
trim(database) as database,
trim(querytxt) as qrytext,
md5(trim(querytxt)) as qry_md5,
starttime, endtime,
(datediff(seconds, starttime,endtime)::numeric(12,2))/60 as run_minutes,
alrt.num_events as alerts, aborted
from stl_query
left outer join
(select query, 1 as num_events from stl_alert_event_log group by query ) as alrt
on alrt.query = stl_query.query
where userid <> 1 and starttime >= dateadd(day, -7, current_date))
group by database, label, qry_md5, aborted
order by total desc limit 50;
```
# Identifying tables with data skew or unsorted rows
The following query identifies tables that have uneven data distribution (data skew) or a high percentage of unsorted rows.
A low `skew` value indicates that table data is properly distributed. If a table has a `skew` value of 4.00 or higher, consider modifying its data distribution style. For more information, see [Suboptimal data distribution](query-performance-improvement-opportunities.md#suboptimal-data-distribution).
If a table has a `pct_unsorted` value greater than 20 percent, consider running the [VACUUM](r_VACUUM_command.md) command. For more information, see [Unsorted or missorted rows](query-performance-improvement-opportunities.md#unsorted-or-mis-sorted-rows).
Also review the `mbytes` and `pct_of_total` values for each table. These columns identify the size of the table and what percentage of raw disk space the table consumes. The raw disk space includes space that is reserved by Amazon Redshift for internal use, so it is larger than the nominal disk capacity, which is the amount of disk space available to the user. Use this information to verify that you have free disk space equal to at least 2.5 times the size of your largest table. Having this space available enables the system to write intermediate results to disk when processing complex queries.
```
select trim(pgn.nspname) as schema,
trim(a.name) as table, id as tableid,
decode(pgc.reldiststyle,0, 'even',1,det.distkey ,8,'all') as distkey, dist_ratio.ratio::decimal(10,4) as skew,
det.head_sort as "sortkey",
det.n_sortkeys as "#sks", b.mbytes,
decode(b.mbytes,0,0,((b.mbytes/part.total::decimal)*100)::decimal(5,2)) as pct_of_total,
decode(det.max_enc,0,'n','y') as enc, a.rows,
decode( det.n_sortkeys, 0, null, a.unsorted_rows ) as unsorted_rows ,
decode( det.n_sortkeys, 0, null, decode( a.rows,0,0, (a.unsorted_rows::decimal(32)/a.rows)*100) )::decimal(5,2) as pct_unsorted
from (select db_id, id, name, sum(rows) as rows,
sum(rows)-sum(sorted_rows) as unsorted_rows
from stv_tbl_perm a
group by db_id, id, name) as a
join pg_class as pgc on pgc.oid = a.id
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
left outer join (select tbl, count(*) as mbytes
from stv_blocklist group by tbl) b on a.id=b.tbl
inner join (select attrelid,
min(case attisdistkey when 't' then attname else null end) as "distkey",
min(case attsortkeyord when 1 then attname else null end ) as head_sort ,
max(attsortkeyord) as n_sortkeys,
max(attencodingtype) as max_enc
from pg_attribute group by 1) as det
on det.attrelid = a.id
inner join ( select tbl, max(mbytes)::decimal(32)/min(mbytes) as ratio
from (select tbl, trim(name) as name, slice, count(*) as mbytes
from svv_diskusage group by tbl, name, slice )
group by tbl, name ) as dist_ratio on a.id = dist_ratio.tbl
join ( select sum(capacity) as total
from stv_partitions where part_begin=0 ) as part on 1=1
where mbytes is not null
order by mbytes desc;
```
# Identifying queries with nested loops
The following query identifies queries that have had alert events logged for nested loops. For information on how to fix the nested loop condition, see [Nested loop](query-performance-improvement-opportunities.md#nested-loop).
```
select query, trim(querytxt) as SQL, starttime
from stl_query
where query in (
select distinct query
from stl_alert_event_log
where event like 'Nested Loop Join in the query plan%')
order by starttime desc;
```
# Reviewing queue wait times for queries
The following query shows how long recent queries waited for an open slot in a query queue before running. If you see a trend of high wait times, you might want to modify your query queue configuration for better throughput. For more information, see [Implementing manual WLM](cm-c-defining-query-queues.md).
```
select trim(database) as DB , w.query,
substring(q.querytxt, 1, 100) as querytxt, w.queue_start_time,
w.service_class as class, w.slot_count as slots,
w.total_queue_time/1000000 as queue_seconds,
w.total_exec_time/1000000 exec_seconds, (w.total_queue_time+w.total_Exec_time)/1000000 as total_seconds
from stl_wlm_query w
left join stl_query q on q.query = w.query and q.userid = w.userid
where w.queue_start_Time >= dateadd(day, -7, current_Date)
and w.total_queue_Time > 0 and w.userid >1
and q.starttime >= dateadd(day, -7, current_Date)
order by w.total_queue_time desc, w.queue_start_time desc limit 35;
```
# Reviewing query alerts by table
The following query identifies tables that have had alert events logged for them, and also identifies what type of alerts are most frequently raised.
If the `minutes` value for a row with an identified table is high, check that table to see if it needs routine maintenance, such as having [ANALYZE](r_ANALYZE.md) or [VACUUM](r_VACUUM_command.md) run against it.
If the `count` value is high for a row but the `table` value is null, run a query against STL\$1ALERT\$1EVENT\$1LOG for the associated `event` value to investigate why that alert is getting raised so often.
```
select trim(s.perm_table_name) as table,
(sum(abs(datediff(seconds, s.starttime, s.endtime)))/60)::numeric(24,0) as minutes, trim(split_part(l.event,':',1)) as event, trim(l.solution) as solution,
max(l.query) as sample_query, count(*)
from stl_alert_event_log as l
left join stl_scan as s on s.query = l.query and s.slice = l.slice
and s.segment = l.segment and s.step = l.step
where l.event_time >= dateadd(day, -7, current_Date)
group by 1,3,4
order by 2 desc,6 desc;
```
# Identifying tables with missing statistics
The following query provides a count of the queries that you are running against tables that are missing statistics. If this query returns any rows, look at the `plannode` value to determine the affected table, and then run [ANALYZE](r_ANALYZE.md) on it.
```
select substring(trim(plannode),1,100) as plannode, count(*)
from stl_explain
where plannode like '%missing statistics%'
group by plannode
order by 2 desc;
```