Query analysis workflow - Amazon Redshift

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.

  1. Make sure that your tables are designed according to best practices. For more information, see Amazon Redshift best practices for designing tables.

  2. 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.

  3. Run the VACUUM 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.

  4. Run the ANALYZE 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.

  5. 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.

  6. Check the STL_ALERT_EVENT_LOG table to identify and correct possible issues with your query. For more information, see Reviewing query alerts.

  7. Run the EXPLAIN command to get the query plan and use it to optimize the query. For more information, see Analyzing the query plan.

  8. Use the SVL_QUERY_SUMMARY and SVL_QUERY_REPORT views to get summary information and use it to optimize the query. For more information, see Analyzing the query summary.

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. For more information about configuring query queues, see Implementing workload management.