Initplan

Description

Initplan is a kind of GaussDB subplan. In GaussDB, subquery plans can be classified into correlated subplans and non-correlated subplans. A correlated subplan is a row that a subquery depends on an external query and cannot be executed independently of the foreign query. A non-correlated subplan is the opposite. In GaussDB, a subplan or initplan is a part that can be independently executed compared with the entire plan and is generally generated by a subplan that cannot be promoted. Subplans are generated based on correlated subplans, and initplans are generated based on non-correlated subplans. Subplan runs during the execution of the main query and is re-executed on each row of the main query. Initplan runs before the execution of the main query and the result is one-time. They are calculated at the beginning of the query and then cached for reuse during the entire query execution, therefore, initplan is more efficient.

Typical Scenarios

The SELECT subplan does not depend on foreign queries and cannot be pulled up.

Examples

Example: The SELECT subplan does not depend on foreign queries and cannot be pulled up.

-- Prepare data.
gaussdb=# CREATE TABLE init_table (id int, grade int, time int);
CREATE TABLE 
gaussdb=# INSERT INTO init_table VALUES(generate_series(1,10000), (random() * 10)::integer,(random() * 10)::integer );
INSERT 0 10000 
gaussdb=# CREATE TABLE t1(grade int);
CREATE TABLE 
gaussdb=# INSERT INTO t1 VALUES( 10),( 11);
INSERT 0 2
gaussdb=# CREATE TABLE t2(a int, b int, c int);
CREATE TABLE
gaussdb=# INSERT INTO t2 VALUES(1, 2, 3 );
INSERT 0 1

-- Execution result.
gaussdb=# EXPLAIN SELECT * FROM init_table  WHERE init_table.grade  IN  (SELECT grade FROM t2) AND  init_table.grade != (SELECT * FROM t1);
                             QUERY PLAN                             
--------------------------------------------------------------------
 Seq Scan on init_table  (cost=34.02..130297.99 rows=3755 width=12)
   Filter: ((grade <> $2) AND (SubPlan 1))
   InitPlan 2 (returns $2)
     ->  Seq Scan on t1  (cost=0.00..34.02 rows=2402 width=4)
   SubPlan 1
     ->  Seq Scan on t2  (cost=0.00..29.45 rows=1945 width=0)
(6 rows)

-- Drop.
gaussdb=# DROP TABLE IF EXISTS init_table;
gaussdb=# DROP TABLE IF EXISTS t1;
gaussdb=# DROP TABLE IF EXISTS t2;

In the preceding example, the output is as follows.