STL_NESTLOOP
Analyzes nested-loop join execution steps for queries.
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. |
| tbl | integer | Table ID. |
| checksum | integer | This information is for internal use only. |
Sample Queries
Because the following query neglects to join the CATEGORY table, it produces a partial Cartesian product, which is not recommended. It is shown here to illustrate a nested loop.
select count(event.eventname), event.eventname, category.catname, date.caldate from event, category, date where event.dateid = date.dateid group by event.eventname, category.catname, date.caldate;
The following query shows the results from the previous query in the STL_NESTLOOP table.
select query, slice, segment as seg, step, datediff(msec, starttime, endtime) as duration, tasknum, rows, tbl from stl_nestloop where query = pg_last_query_id();
query | slice | seg | step | duration | tasknum | rows | tbl -------+-------+-----+------+----------+---------+-------+----- 6028 | 0 | 4 | 5 | 41 | 22 | 24277 | 240 6028 | 1 | 4 | 5 | 26 | 23 | 24189 | 240 6028 | 3 | 4 | 5 | 25 | 23 | 24376 | 240 6028 | 2 | 4 | 5 | 54 | 22 | 23936 | 240
