Help Center/ GaussDB/ Centralized_8.x/ SQL Optimization/ Hint-based Tuning/ Hints for Specifying the Degree of Parallelism for Scans
Updated on 2024-06-03 GMT+08:00
View PDF

Hints for Specifying the Degree of Parallelism for Scans

Description

The degree of parallelism (DOP) is specified for table scans in a parallel execution plan.

Syntax

scandop([@queryblock] table dop_num)

Parameters

  • 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.
  • table specifies the table to be scanned. You can specify only one table. Use a table alias (if any) instead of a table name.
  • dop_num specifies the DOP for table scans.
  • scandop specifies a hint for specifying the DOP for scans.

Examples

-- Preparation
CREATE TABLE cst1(a int, b int, c int, d bigint);
set explain_perf_mode = pretty;  -- Open the explain pretty option to view a detailed plan.
-- Usage
gaussdb=# EXPLAIN (costs off) SELECT /*+ Set(query_dop 2) scandop(cst1 2)*/ * FROM cst1;
 id |                 operation                  
----+--------------------------------------------
  1 | ->  Streaming(type: LOCAL GATHER dop: 1/2)
  2 |    ->  Seq Scan on cst1
(2 rows)

In a parallel plan, you can use scandop hints to specify the degree of parallelism.

The hint takes effect only when dop_num is the same as the current degree of parallelism (query_dop) or is set to 1.

gaussdb=# EXPLAIN (costs off) SELECT /*+ Set(query_dop 2) scandop(cst1 4)*/ * FROM cst1;
 id |      operation       
----+----------------------
  1 | ->  Seq Scan on cst1
(1 row)

In some scenarios with subqueries, the Stream operator deteriorates and is eliminated. In this case, although the scandop hint takes effect, no stream plan is generated.

gaussdb=# EXPLAIN(costs off) SELECT /*+ Set(query_dop 2) scandop(cst1 2) */ count(a) FROM cst1 WHERE b > (SELECT max(a) FROM cst1 ORDER BY c LIMIT 1);
 id |                 operation                  
----+--------------------------------------------
  1 | ->  Aggregate
  2 |    ->  Aggregate
  3 |       ->  Seq Scan on cst1
  4 |    ->  Limit  [3, InitPlan 1 (returns $0)]
  5 |       ->  Aggregate
  6 |          ->  Seq Scan on cst1
(6 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   3 --Seq Scan on cst1
         Filter: (b > $0)
(2 rows)

Compare it with a plan without hints.

 id |                 operation                  
----+--------------------------------------------
  1 | ->  Aggregate
  2 |    ->  Seq Scan on cst1
  3 |    ->  Limit  [2, InitPlan 1 (returns $0)]
  4 |       ->  Aggregate
  5 |          ->  Seq Scan on cst1
(5 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   2 --Seq Scan on cst1
         Filter: (b > $0)
(2 rows)

You can see that the plan with scandop hint has an additional Aggregate operator, which is the residual after the Stream operator is eliminated.

A common plan has only one layer of Aggregate operator. The two plans do not affect the final result.

Parent topic: Hint-based Tuning