Hints for Specifying ANY Sublink Pullup

Description

A method of optimizing operators is specified when an ANY sublink is pulled up.

Syntax

[no] hashed_sublink[(@queryblock)], material_sublink[(@queryblock)]

Parameters

• no indicates that the optimization is not used.
• For details about @queryblock, see Hint for Specifying the Query Block Where the Hint Is Located. This parameter can be omitted, indicating that it takes effect in the current query block. If it is not specified, the hint does not have parentheses (()).

• hashed_sublink uses hash table optimization for specified sublinks.

• material_sublink uses materialized optimization for specified sublinks.

Examples

• material_sublink
  Table creation statements:

  CREATE TABLE SUPPLIER (
      S_SUPPKEY BIGINT NOT NULL,
      S_NAME CHAR(25) NOT NULL,
      S_ADDRESS VARCHAR(40) NOT NULL,
      S_NATIONKEY INT NOT NULL,
      S_PHONE CHAR(15) NOT NULL,
      S_ACCTBAL DECIMAL(15, 2) NOT NULL,
      S_COMMENT VARCHAR(101) NOT NULL,
      S_STATEKEY INT NOT NULL
  );
  set explain_perf_mode=pretty; -- Open the explain pretty option to view a detailed plan.

  Example:

  -- material_sublink hint
  gaussdb=#  EXPLAIN (blockname on, costs off) SELECT max(s_acctbal) nation_total, s_nationkey
  FROM supplier WHERE s_suppkey < 10000
  GROUP BY s_nationkey having nation_total = ANY (SELECT /*+ material_sublink */ avg(s_acctbal) FROM supplier GROUP BY s_statekey);
   id |                 operation                 | Query Block 
  ----+-------------------------------------------+-------------
    1 | ->  HashAggregate                         | sel$1
    2 |    ->  Seq Scan on supplier@"sel$1"       | sel$1
    3 |    ->  Materialize  [1, SubPlan 1]        | 
    4 |       ->  HashAggregate                   | sel$2
    5 |          ->  Seq Scan on supplier@"sel$2" | sel$2
  (5 rows)
  
   Predicate Information (identified by plan id) 
  -----------------------------------------------
     1 --HashAggregate
           Filter: (SubPlan 1)
     2 --Seq Scan on supplier@"sel$1"
           Filter: (s_suppkey < 10000)
  (4 rows)

  The material operation is performed on the sublink. If the hint is not used, hashed is used.

• hashed_sublink
  Table creation and preparation:

  set work_mem='64kB';  -- Reduce the work memory to reproduce the scenario.
  set explain_perf_mode = pretty;   -- Open the explain pretty option to view a detailed plan.
  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));

  Example:

  -- 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-width |      E-costs       
  ----+----------------------------------------+--------+---------+--------------------
    1 | ->  Seq Scan on nt1                    |  49869 |       4 | 1627.725..3255.450
    2 |    ->  Seq Scan on nt2  [1, SubPlan 1] |  99738 |       4 | 0.000..1378.380
  (2 rows)
  
   Predicate Information (identified by plan id) 
  -----------------------------------------------
     1 --Seq Scan on nt1
           Filter: (NOT (hashed SubPlan 1))
  (2 rows)
  -- work_mem affects plan generation and execution. If you perform other operations, you need to reset it.
  reset work_mem;  -- Roll back the memory settings.

  The hashed operation is performed on the sublink. If the hint is not used, material is used.