The SUBPLAN SCAN operator shows the view from which data is accessed by the optimizer.
If FROM TABLE in a query is a view, a SUBPLAN SCAN operator is assigned in the execution plan. The SUBPLAN SCAN operator is similar to the TABLE SCAN operator, except that SUBPLAN SCAN does not read data from the base table. Instead, it reads the output data of subnodes.
In the following example, Operator 1 in query Q1 is generated by the query in the view, and SUBPLAN SCAN, which is Operator 0, reads Operator 1 and generates the output data.
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(1,1);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(2,2);
Query OK, 1 rows affected
obclient> CREATE VIEW v AS SELECT * FROM t1 LIMIT 5;
Query OK, 1 rows affected
Q1:
obclient> EXPLAIN SELECT * FROM V WHERE c1 > 0;
Query Plan:
=====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-------------------------------------
|0 |SUBPLAN SCAN|v |1 |37 |
|1 | TABLE SCAN |t1 |2 |37 |
=====================================
Outputs & filters:
-------------------------------------
0 - output([v.c1], [v.c2]), filter([v.c1 > 0]),
access([v.c1], [v.c2])
1 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0),
limit(5), offset(nil)
In the preceding example, the Outputs & filters section for query Q1 shows in detail the output information of the SUBPLAN SCAN operator.
| Field | Description |
|---|---|
| output | The output expressions of the operator. |
| filter | The filter conditions of the operator. For example, v.c 1> 0 in filter([v.c 1> 0]) is a filter condition. |
| access | The name of the column to be used. The operator reads it from the subnode. |
If FROM TABLE is a view, and view merge rewriting can be performed at the specified conditions, the SUBPLAN SCAN operator does not appear in the execution plan. In comparison with query Q1, the filter condition is not specified for query Q2 in the following example. The SUBPLAN SCAN operator is not required.
Q2:
obclient> EXPLAIN SELECT * FROM v;
Query Plan:
===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |2 |37 |
===================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0),
limit(5), offset(nil)