Why can't I match query IDs across system tables in Amazon Redshift?

Short description

You can use the query_id column as the query ID for SYS views including SYS_QUERY_HISTORY. For STL/SVL tables and views including STL_QUERY, you can use the query column for the query ID. The query column identifies the rewritten child queries that Amazon Redshift generates when it processes the original query. For more information, see Improving query identifier tracking using the SYS monitoring views.

Resolution

Grant access to system tables

To match query IDs across system tables in Amazon Redshift, the user must be either a superuser or have the necessary privileges. For information about how to grant access to a specific user, see Visibility of data in system tables and views.

To grant access to a specific user and assign a system-defined role, run the following command:

GRANT ROLE sys:monitor TO database_user;

Note: Replace database_user with your database user.

Correlate query IDs

Amazon Redshift might not provide exact matches between the query_id column and the query column. However, you can search for potential matches between SYS and STL/SVL tables.

Use the query_id from SYS to get the query ID in STL/SVL in the following query:

SELECT query_id, query   FROM sys_query_history a, stl_query b 
  WHERE a.query_id = <query_id> 
    AND a.transaction_id = b.xid
    AND b.starttime BETWEEN a.start_time AND a.end_time
    AND b.endtime BETWEEN a.start_time AND a.end_time;

Note: Queries that run only on the leader node are present in SYS_QUERY_HISTORY, but STL_QUERY excludes these queries.

Use the query from STL/SVL to get query id in SYS in the following query:

 SELECT query_id, query   FROM sys_query_history a, stl_query b 
  WHERE b.query = <query> 
    AND a.transaction_id = b.xid
    AND b.starttime BETWEEN a.start_time AND a.end_time
    AND b.endtime BETWEEN a.start_time AND a.end_time;

Find query history for identical SQL statements

The output of these queries can help when you compare different query runs. To find identical queries that ran at different times in SYS_QUERY_HISTORY, use the following queries to retrieve the user_query_hash value and its corresponding entries:

 SELECT user_query_hash  FROM sys_query_history
  WHERE query_id = <query_id>;

 SELECT *
  FROM sys_query_history 
  WHERE user_query_hash = '<user_query_hash>';

Related information

Query planning and execution workflow