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

STL_SCAN

Analyzes table scan steps for queries. The step number for rows in this table is always 0 because a scan is the first step in a segment.

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.
slice integer Number that identifies the slice where the query was running.
segment integer Number that identifies the query segment.
step integer Query step that executed.
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.
tasknum integer Number of the query task process that was assigned to execute the step.
rows bigint Total number of rows that were processed.
bytes bigint Size, in bytes, of all the output rows for the step.
fetches bigint This information is for internal use only.
type integer ID of the scan type. For a list of valid values, see the following table.
tbl integer Table ID.
is_rrscan character(1) If true (t), indicates that range-restricted scan was used on the step.
is_delayed_scan character(1) This information is for internal use only.
rows_pre_filter bigint For scans of permanent tables, the total number of rows emitted before filtering rows marked for deletion (ghost rows) and before applying user-defined query filters.
rows_pre_user_filter bigint For scans of permanent tables, the number of rows processed after filtering rows marked for deletion (ghost rows) but before applying user-defined query filters.
perm_table_name character(136) For scans of permanent tables, the name of the table scanned.

Scan Types

Type ID Description
1 Data from the network.
2 Permanent user tables in compressed shared memory.
3 Transient row-wise tables.
21 Load files from Amazon S3.
22 Load tables from Amazon DynamoDB.
23 Load data from a remote SSH connection.
24 Load data from remote cluster (sorted region). This is used for resizing.
25 Load data from remote cluster(unsorted region). This is used for resizing.

Usage Notes

Ideally rows should be relatively close to rows_pre_filter. A large difference between rows and rows_pre_filter is an indication that the execution engine is scanning rows that are later discarded, which is inefficient. The difference between rows_pre_filter and rows_pre_user_filter is the number of ghost rows in the scan. Run a VACUUM to remove rows marked for deletion. The difference between rows and rows_pre_user_filter is the number of rows filtered by the query. If a lot of rows are discarded by the user filter, review your choice of sort column or, if this is due to a large unsorted region, run a vacuum.

Sample Queries

The following example shows that rows_pre_filter is larger than rows_pre_user_filter because the table has deleted rows that have not been vacuumed (ghost rows).

Copy
SELECT slice, segment,step,rows, rows_pre_filter, rows_pre_user_filter from stl_scan where query = pg_last_query_id(); query | slice | segment | step | rows | rows_pre_filter | rows_pre_user_filter -------+--------+---------+------+-------+-----------------+---------------------- 42915 | 0 | 0 | 0 | 43159 | 86318 | 43159 42915 | 0 | 1 | 0 | 1 | 0 | 0 42915 | 1 | 0 | 0 | 43091 | 86182 | 43091 42915 | 1 | 1 | 0 | 1 | 0 | 0 42915 | 2 | 0 | 0 | 42778 | 85556 | 42778 42915 | 2 | 1 | 0 | 1 | 0 | 0 42915 | 3 | 0 | 0 | 43428 | 86856 | 43428 42915 | 3 | 1 | 0 | 1 | 0 | 0 42915 | 10000 | 2 | 0 | 4 | 0 | 0 (9 rows)