Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-2.x)/ SQL Optimization/ Hint-based Tuning/ Hint for Materializing a Sub-plan Result
Updated on 2025-02-27 GMT+08:00
View PDF
Share

Hint for Materializing a Sub-plan Result

Description

You can materialize a sub-plan result to temporarily store the query record. This hint is used only in the INSERT statement.

When the INSERT INTO ... SELECT statement is used to insert a large amount of data with multiple duplicate rows, the index needs to be compared for multiple times. As a result, the execution takes a long time. This HINT is used to materialize the results of subplans and temporarily store query records to reduce the number of index comparisons and shorten the statement execution time.

Syntax

material_subplan

Examples

Create a table and insert data into the table.

create table test(a int, b int) with(storage_type = ustore);
create index on test(a);
create table test_src(a int, b int);
insert into test_src values(generate_series(1,10), generate_series(1,100000));
Normal INSERT INTO...SELECT statement: 
insert into test select /*+ nestloop(test_src t1)*/ * from test_src where not exists(select 1 from test t1 where t1.a = test_src.a);
Execute plan: 
                       QUERY PLAN                        
---------------------------------------------------------
 Insert on test
   ->  Nested Loop Anti Join
         ->  Seq Scan on test_src
         ->  Index Only Scan using test_a_idx on test t1
               Index Cond: (a = test_src.a)
(5 rows)
Use the material_subplan hint operator: 
insert /*+ material_subplan*/ into test select /*+ nestloop(test_src t1)*/ * from test_src where not exists(select 1 from test t1 where t1.a = test_src.a);
The execution plan is as follows: 
                          QUERY PLAN                           
---------------------------------------------------------------
 Insert on test
   ->  Materialize
         ->  Nested Loop Anti Join
               ->  Seq Scan on test_src
               ->  Index Only Scan using test_a_idx on test t1
                     Index Cond: (a = test_src.a)
(6 rows)
Parent topic: Hint-based Tuning