指定Any子链接提升的Hint

set work_mem='64kB';  --将work内存缩小便于复现该场景
set explain_perf_mode = pretty;   --打开explain pretty选项，可以看到更详尽计划
CREATE TABLE nt1 (a int);
CREATE TABLE nt2 (a int);
INSERT INTO nt1 VALUES(generate_series(1, 50000));
INSERT INTO nt2 VALUES(generate_series(1, 50000));
ANALYZE nt1;
ANALYZE nt2;

--不使用hashed_sublink hint
gaussdb=# EXPLAIN SELECT * FROM nt1 WHERE nt1.a NOT IN (SELECT /*+ no_expand*/ a FROM nt2);
 id |                operation                | E-rows  | E-memory | E-width |  E-costs
----+-----------------------------------------+---------+----------+---------+------------
  1 | ->  Streaming (type: GATHER)            |   25000 |          |       4 | 5079922.80
  2 |    ->  Seq Scan on nt1                  |   25000 | 1MB      |       4 | 5079011.27
  3 |       ->  Materialize  [2, SubPlan 1]   | 1800000 | 32MB     |       4 | 468.98
  4 |          ->  Streaming(type: BROADCAST) |  300000 | 2MB      |       4 | 218.98
  5 |             ->  Seq Scan on nt2         |   50000 | 1MB      |       4 | 121.33
(5 rows)

 Predicate Information (identified by plan id)
-----------------------------------------------
   2 --Seq Scan on nt1
         Filter: (NOT (SubPlan 1))
(2 rows)

--使用hashed_sublink hint
gaussdb=# EXPLAIN SELECT * FROM nt1 WHERE nt1.a NOT IN (SELECT /*+ hashed_sublink no_expand*/ a FROM nt2);
 id |                operation                | E-rows  | E-memory | E-width | E-costs
----+-----------------------------------------+---------+----------+---------+---------
  1 | ->  Streaming (type: GATHER)            |   25000 |          |       4 | 2272.68
  2 |    ->  Seq Scan on nt1                  |   25000 | 1MB      |       4 | 1361.14
  3 |       ->  Materialize  [2, SubPlan 1]   | 1800000 | 32MB     |       4 | 468.98
  4 |          ->  Streaming(type: BROADCAST) |  300000 | 2MB      |       4 | 218.98
  5 |             ->  Seq Scan on nt2         |   50000 | 1MB      |       4 | 121.33
(5 rows)

 Predicate Information (identified by plan id)
-----------------------------------------------
   2 --Seq Scan on nt1
         Filter: (NOT (hashed SubPlan 1))
(2 rows)
--work_mem 会对计划生成执行有所影响若后续进行其他操作需要重置：
reset work_mem;  --将内存设置回退