Help Center/ GaussDB/ Developer Guide(Centralized_3.x)/ SQL Optimization/ Hint-based Tuning/ Hint for Materializing a Sub-plan Result
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Hint for Materializing a Sub-plan Result

Function

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