The SUBPLAN SCAN operator is used to show which view the optimizer accesses data from.
When a query accesses a view, the execution plan contains a SUBPLAN SCAN operator. The SUBPLAN SCAN operator is similar to the TABLE SCAN operator, but it does not read data from the base table. Instead, it reads the output data of its child node.
In the following example, operator 1 in query Q1 is generated for the view query, and operator 0, the SUBPLAN SCAN, reads from operator 1 and outputs the result.
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 of the execution plan for query Q1 shows the output information of the SUBPLAN SCAN operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. For example, v.c1 > 0 in filter([v.c1 > 0]). |
| access | The names of the columns that the operator needs to read from the child node. |
When a query accesses a view and certain conditions are met, view merge rewriting can be performed, and the execution plan does not contain a SUBPLAN SCAN operator. As shown in the following example, query Q2 has fewer filter conditions than query Q1, so no SUBPLAN SCAN operator needs to be assigned.
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)