SVCS_EXPLAIN
Displays the EXPLAIN plan for a query that has been submitted for execution.
Note
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the tables with the prefix STL except that the STL tables provide information only for queries run on the main cluster.
SVCS_EXPLAIN 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. |
| nodeid | integer | Plan node identifier, where a node maps to one or more steps in the execution of the query. |
| parentid | integer | Plan node identifier for a parent node. A parent node has some number of child nodes. For example, a merge join is the parent of the scans on the joined tables. |
| plannode | character(400) | The node text from the EXPLAIN output. Plan nodes
that refer to execution on compute nodes are prefixed with
XN in the EXPLAIN output. |
| info | character(400) | Qualifier and filter information for the plan node. For example, join conditions and WHERE clause restrictions are included in this column. |
Sample queries
Consider the following EXPLAIN output for an aggregate join query:
explain select avg(datediff(day, listtime, saletime)) as avgwait from sales, listing where sales.listid = listing.listid; QUERY PLAN ------------------------------------------------------------------------------ XN Aggregate (cost=6350.30..6350.31 rows=1 width=16) -> XN Hash Join DS_DIST_NONE (cost=47.08..6340.89 rows=3766 width=16) Hash Cond: ("outer".listid = "inner".listid) -> XN Seq Scan on listing (cost=0.00..1924.97 rows=192497 width=12) -> XN Hash (cost=37.66..37.66 rows=3766 width=12) -> XN Seq Scan on sales (cost=0.00..37.66 rows=3766 width=12) (6 rows)
If you run this query and its query ID is 10, you can use the SVCS_EXPLAIN table to see the same kind of information that the EXPLAIN command returns:
select query,nodeid,parentid,substring(plannode from 1 for 30), substring(info from 1 for 20) from svcs_explain where query=10 order by 1,2; query| nodeid |parentid| substring | substring -----+--------+--------+--------------------------------+------------------- 10 | 1 | 0 |XN Aggregate (cost=6717.61..6 | 10 | 2 | 1 | -> XN Merge Join DS_DIST_NO| Merge Cond:("outer" 10 | 3 | 2 | -> XN Seq Scan on lis | 10 | 4 | 2 | -> XN Seq Scan on sal | (4 rows)
Consider the following query:
select event.eventid, sum(pricepaid) from event, sales where event.eventid=sales.eventid group by event.eventid order by 2 desc; eventid | sum --------+---------- 289 | 51846.00 7895 | 51049.00 1602 | 50301.00 851 | 49956.00 7315 | 49823.00 ...
If this query's ID is 15, the following system table query returns the plan nodes that were performed. In this case, the order of the nodes is reversed to show the actual order of execution:
select query,nodeid,parentid,substring(plannode from 1 for 56) from svcs_explain where query=15 order by 1, 2 desc; query|nodeid|parentid| substring -----+------+--------+-------------------------------------------------------- 15 | 8 | 7 | -> XN Seq Scan on eve 15 | 7 | 5 | -> XN Hash(cost=87.98..87.9 15 | 6 | 5 | -> XN Seq Scan on sales(cos 15 | 5 | 4 | -> XN Hash Join DS_DIST_OUTER(cos 15 | 4 | 3 | -> XN HashAggregate(cost=862286577.07.. 15 | 3 | 2 | -> XN Sort(cost=1000862287175.47..10008622871 15 | 2 | 1 | -> XN Network(cost=1000862287175.47..1000862287197. 15 | 1 | 0 |XN Merge(cost=1000862287175.47..1000862287197.46 rows=87 (8 rows)
The following query retrieves the query IDs for any query plans that contain a window function:
select query, trim(plannode) from svcs_explain where plannode like '%Window%'; query| btrim -----+------------------------------------------------------------------------ 26 | -> XN Window(cost=1000985348268.57..1000985351256.98 rows=170 width=33) 27 | -> XN Window(cost=1000985348268.57..1000985351256.98 rows=170 width=33) (2 rows)