How to interpret SYS_QUERY_HISTORY results

Question

I am trying to troubleshoot query performance issues and am seeing the following results out of SYS_QUERY_HISTORY.

| query_id | elapsed_time | execution_time | compile_time | planning_time | compute_type |
| --- | --- | --- | --- | --- | --- |
| 12345 | 6231177 | 6019462 | 5850622 | 176937 | primary |
| 12346 | 6158004 | 5998149 | 5584236 | 132701 | primary |
| 12347 | 5068902 | 5046901 | 5026107 | 5952 | primary |
| 12348 | 547836 | 450070 | 252367 | 76902 | primary |
| 12349 | 368474 | 291247 | 2303 | 58302 | primary |

Are the elapsed time values inclusive of execution_time / compile_time? And in the case of compile_time being so high what steps can actually be taken?

The [documentation for SYS_QUERY_HISTORY](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_HISTORY.html) mostly enumerates the columns of the table, but doesn't give any guidance on how to interpret the values.

Answer

Hi willayd,

The elapsed time is the difference between the end_time and the start_time of the request. It includes the execution, queuing, compilation and planning times.

* **Execution**: the steps in the query plan are active and being executed. If this phase takes a lot of time, then this means that the queries should be reviewed for optimization, optimization can be at the physical data model level (such as distribution and sort keys) or at the query level (rewriting the query by analyzing the [query plan](https://docs.aws.amazon.com/redshift/latest/dg/c-the-query-plan.html) first and then [improving its performance](https://docs.aws.amazon.com/redshift/latest/dg/c-query-tuning.html)). Here are the [best practices ](https://docs.aws.amazon.com/redshift/latest/dg/c_designing-tables-best-practices.html) for designing tables. 
* **Queuing**: the query is on-hold or in other words on the waiting list, waiting for an available slot so it can get executed. This is so often used as an indicator for high number of concurrent queries. This column can be reduced by reviewing the defined [workload management](https://docs.aws.amazon.com/redshift/latest/dg/c_workload_mngmt_classification.html) and/or enabling the[ concurrency scaling](https://docs.aws.amazon.com/redshift/latest/dg/concurrency-scaling.html) feature.
* **Compilation**: the time spent on compiling the execution plan. Usually the first execution includes the overhead of compiling the code and as a result the future runs of the same query will be faster, often even with different parameters ([Query planning and execution workflow](https://docs.aws.amazon.com/redshift/latest/dg/c-query-planning.html) and [compiled code](https://docs.aws.amazon.com/redshift/latest/dg/c_challenges_achieving_high_performance_queries.html#compiled-code)).
* **Planning**: the time spent on query planning such as query plan rewrite for a faster execution and better performance.

Regards,

Ziad

query_id	elapsed_time	execution_time	compile_time	planning_time	compute_type
12345	6231177	6019462	5850622	176937	primary
12346	6158004	5998149	5584236	132701	primary
12347	5068902	5046901	5026107	5952	primary
12348	547836	450070	252367	76902	primary
12349	368474	291247	2303	58302	primary

How to interpret SYS_QUERY_HISTORY results

Relevant content