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

STL_QUERY

Returns execution information about a database query.

Note

The STL_QUERY and STL_QUERYTEXT tables only contain information about queries, not other utility and DDL commands. For a listing and information on all statements executed by Amazon Redshift, you can also query the STL_DDLTEXT and STL_UTILITYTEXT tables. For a complete listing of all statements executed by Amazon Redshift, you can query the SVL_STATEMENTTEXT view.

To manage disk space, the STL log tables only retain approximately two to five days of log history, depending on log usage and available disk space. If you want to retain the log data, you will need to periodically copy it to other tables or unload it to Amazon S3.

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.
label character(15) Either the name of the file used to run the query or a label defined with a SET QUERY_GROUP command. If the query is not file-based or the QUERY_GROUP parameter is not set, this field value is default.
xid bigint Transaction ID.
pid integer Process ID. Normally, all of the queries in a session are run in the same process, so this value usually remains constant if you run a series of queries in the same session. Following certain internal events, Amazon Redshift might restart an active session and assign a new PID. For more information, see STL_RESTARTED_SESSIONS.
database character(32) The name of the database the user was connected to when the query was issued.
querytxt character(4000) Actual query text for the query.
starttime timestamp Time in UTC that the query started executing, with 6 digits of precision for fractional seconds. For example: 2009-06-12 11:29:19.131358.
endtime timestamp Time in UTC that the query finished executing, with 6 digits of precision for fractional seconds. For example: 2009-06-12 11:29:19.131358.
aborted integer If a query was aborted by the system or canceled by the user, this column contains 1. If the query ran to completion (including returning results to the client), this column contains 0. If a client disconnects before receiving the results, the query will be marked as canceled (1), even if it completed successfully in the backend.
insert_pristine integer Whether write queries are/were able to run while the current query is/was running. 1 = no write queries allowed. 0 = write queries allowed. This column is intended for use in debugging.

Sample Queries

The following query lists the five most recent queries.

select query, trim(querytxt) as sqlquery from stl_query order by query desc limit 5; query | sqlquery ------+-------------------------------------------------- 129 | select query, trim(querytxt) from stl_query order by query; 128 | select node from stv_disk_read_speeds; 127 | select system_status from stv_gui_status 126 | select * from systable_topology order by slice 125 | load global dict registry (5 rows)

The following query returns the time elapsed in descending order for queries that ran on February 15, 2013.

select query, datediff(seconds, starttime, endtime), trim(querytxt) as sqlquery from stl_query where starttime >= '2013-02-15 00:00' and endtime < '2013-02-15 23:59' order by date_diff desc; query | date_diff | sqlquery -------+-----------+------------------------------------------- 55 | 119 | padb_fetch_sample: select count(*) from category 121 | 9 | select * from svl_query_summary; 181 | 6 | select * from svl_query_summary where query in(179,178); 172 | 5 | select * from svl_query_summary where query=148; ... (189 rows)