Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Optimization/ Hint-based Optimization/ Hints for Specifying ANY Sublink Pullup
Updated on 2024-08-20 GMT+08:00
View PDF
Share

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)]

Parameters

  • no indicates that the optimization is not used.
  • For details about @queryblock, see Hint Specifying the Query Block Where the Hint Is Located. @queryblock can be omitted, indicating that the hint 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.

Examples

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));
ANALYZE nt1;
ANALYZE nt2;
Example: 
-- Do not use hashed_sublink hints.
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)

-- Use hashed_sublink hints.
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 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, a common plan is used.

Parent topic: Hint-based Optimization