Use hints to control parallel query execution - PolarDB

Parallel queries can run slower than expected when the optimizer selects a suboptimal execution plan. Use parallel hints to override the optimizer and control DOP, table scan behavior, and distribution strategies for joins, aggregations, and sorting.

Version compatibility

Version	Supported hints
PolarDB for MySQL 8.0.1	`PARALLEL`, `NO_PARALLEL`
PolarDB for MySQL 8.0.2	`PARALLEL`, `NO_PARALLEL`, `PQ_DISTRIBUTE`, `PQ_GROUPBY`, `PQ_DISTINCT`, `PQ_WINDOW`, `PQ_ORDERBY`

PolarDB for MySQL 8.0.2 adds PQ_DISTRIBUTE for join distribution strategies and PQ_GROUPBY, PQ_DISTINCT, PQ_WINDOW, and PQ_ORDERBY for query operation execution modes.

Quick reference

Hint	Purpose	Version
`PARALLEL`	Enable parallel query; set DOP or target tables	8.0.1+
`NO_PARALLEL`	Disable parallel query globally or for specific tables	8.0.1+
`PQ_DISTRIBUTE`	Set distribution method for joins	8.0.2
`PQ_GROUPBY`	Set GROUP BY aggregation execution mode	8.0.2
`PQ_DISTINCT`	Set DISTINCT aggregation execution mode	8.0.2
`PQ_WINDOW`	Set window function execution mode	8.0.2
`PQ_ORDERBY`	Set ORDER BY sorting mode	8.0.2
`PQ_PUSHDOWN`	Run subqueries in parallel using the pushdown policy	-
`NO_PQ_PUSHDOWN`	Run subqueries in parallel using the shared access policy	-

Enable or disable parallel query

Enable

SELECT /*+PARALLEL(x)*/ ... FROM ...;   -- x > 0
SELECT /*+ SET_VAR(max_parallel_degree=n) */ * FROM ...   -- n > 0

Disable

SELECT /*+NO_PARALLEL()*/ ... FROM ...;
SELECT /*+ SET_VAR(max_parallel_degree=0) */ * FROM ...

PARALLEL and NO_PARALLEL

Specify which tables to scan in parallel and which to exclude from parallel scans.

Syntax

/*+ PARALLEL [( [query_block] [table_name] [degree] )] */
/*+ NO_PARALLEL [( [query_block] [table_name][, table_name] )] */

Parameters

Parameter	Description
`query_block`	Name of the query block to which the hint applies.
`table_name`	Name of the table to which the hint applies.
`degree`	Degree of parallelism (DOP).

Note

The PARALLEL hint is ineffective for queries that do not support parallel processing or tables that do not support parallel scans.

PARALLEL() vs SET_VAR: behavioral difference

Both methods set the DOP, but they differ in one critical way:

PARALLEL() forces parallel execution even when the table row count is below records_threshold_for_parallelism (default: 10,000).
SET_VAR(max_parallel_degree=n) sets the DOP but does not force parallel execution when the table row count is below records_threshold_for_parallelism.

Choose PARALLEL() when you need to guarantee parallel execution regardless of table size. Use SET_VAR when you want the optimizer to decide based on row count thresholds.

Examples

Force parallel query with default DOP

SELECT /*+PARALLEL()*/ * FROM t1, t2;

Forces parallel execution even when the table row count is below records_threshold_for_parallelism (default: 10000). Uses the max_parallel_degree parameter for DOP. If max_parallel_degree is 0, parallel query is disabled.

Force parallel query with a specific DOP

SELECT /*+PARALLEL(8)*/ * FROM t1, t2;

Forces parallel execution with a DOP of 8, even when the table row count is below records_threshold_for_parallelism.

Set DOP through SET_VAR

SELECT /*+ SET_VAR(max_parallel_degree=8) */ * FROM t1, t2;

Sets max_parallel_degree to 8. Unlike PARALLEL(), this does not force parallel execution when the table row count is below records_threshold_for_parallelism.

Parallel scan on a specific table

SELECT /*+PARALLEL(t1)*/ * FROM t1, t2;

Forces parallel scan on t1 only.

SELECT /*+PARALLEL(t1 8)*/ * FROM t1, t2;

Forces parallel scan on t1 with a DOP of 8.

Parallel subqueries using query blocks

SELECT /*+PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1);

Forces parallel execution for the named query block subq1, using the default max_parallel_degree for DOP.

SELECT /*+PARALLEL(@subq1 8)*/ SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1);

Forces parallel execution for query block subq1 with a DOP of 8.

Parallel subqueries using inline hints

SELECT SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+PARALLEL()*/ SUM(t1.a) FROM t1);

Forces parallel execution for the subquery using the default max_parallel_degree.

SELECT SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+PARALLEL(8)*/ SUM(t1.a) FROM t1);

Forces parallel execution for the subquery with a DOP of 8.

Disable parallel query

SELECT /*+NO_PARALLEL()*/ * FROM t1, t2;

Disables parallel query for the entire statement.

Disable parallel query for specific tables

SELECT /*+NO_PARALLEL(t1)*/ * FROM t1, t2;

Disables parallel scans for t1 only. If parallel query is enabled, the system may still perform parallel scans on t2.

SELECT /*+NO_PARALLEL(t1, t2)*/ * FROM t1, t2;

Disables parallel scans for both t1 and t2.

Disable parallel subqueries

SELECT /*+NO_PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1);

SELECT SUM(t.a) FROM t WHERE t.a =
  (SELECT /*+NO_PARALLEL()*/ SUM(t1.a) FROM t1);

Both statements disable parallel execution for the subquery.

PQ_DISTRIBUTE (join operations)

Control the distribution method for parallel join operations. PQ_DISTRIBUTE supports two modes: single-table distribution and two-table join distribution.

Syntax

Single-table distribution -- set the distribution method for one table:

/*+ PQ_DISTRIBUTE([query_block] table_name strategy) */

Two-table join distribution -- set the parallel join mode between table_name and the previous table:

/*+ PQ_DISTRIBUTE([query_block] table_name strategy1 [strategy2]) */

When only strategy1 is specified, it sets the distribution method for table_name. When both strategy1 and strategy2 are specified, it sets the parallel join mode between table_name and the previous table.

Note

The previous table can be a physical table or an intermediate table from a prior join operation.

Parameters

Parameter	Description
`query_block`	Name of the query block to which the hint applies.
`table_name`	Name of the table to which the hint applies.
`strategy`	Distribution method. See valid values below.

Strategy values

Strategy	Description
`PQ_GATHER`	Aggregate data to a worker of the previous phase.
`PQ_HASH`	Shuffle and distribute data to multiple workers of the previous phase.
`PQ_BROADCAST`	Broadcast data to multiple workers of the previous phase.
`PQ_NONE`	Do not distribute data.

Examples

Single-table distribution

SELECT /*+ PARALLEL(t1) PQ_DISTRIBUTE(t1 PQ_GATHER) */ * FROM t AS t1;

Scans t1 in parallel. Data is not distributed; results are gathered by the leader.

SELECT /*+ PARALLEL(t1) PQ_DISTRIBUTE(t1 PQ_HASH) */ t1.a, SUM(t1.b) FROM t AS t1 GROUP BY t1.a;

Scans t1 in parallel and distributes data to the workers of the next phase based on the group key.

Two-table join distribution

SELECT /*+ PARALLEL(t1) PARALLEL(t2) PQ_DISTRIBUTE(t2 PQ_HASH PQ_HASH) */ *
FROM t AS t1 STRAIGHT_JOIN t AS t2 ON t1.b = t2.c;

Both tables are scanned in parallel and data is hash-distributed to workers of the next phase, which execute a collocated join. Results are then gathered by the leader.

SELECT /*+ PARALLEL(t1) PARALLEL(t2) PQ_DISTRIBUTE(t2 PQ_GATHER PQ_GATHER) */ *
FROM t AS t1 STRAIGHT_JOIN t AS t2 ON t1.b = t2.c;

Both tables are scanned in parallel and data is sent to the leader, which gathers the data and completes the join operation.

Conflicting strategies

SELECT /*+ PARALLEL(t1) PARALLEL(t2) PQ_DISTRIBUTE(t2 PQ_HASH PQ_GATHER) */ *
FROM t AS t1 STRAIGHT_JOIN t AS t2 ON t1.b = t2.c;

The distribution methods conflict (PQ_HASH for t1 and PQ_GATHER for t2), so a parallel query plan cannot be generated.

PQ_GROUPBY (GROUP BY aggregation)

Control the parallel execution mode for GROUP BY aggregation.

Syntax

/*+ PQ_GROUPBY(strategy) */

Strategy values

Strategy	Description
`PQ_ONEPHASE`	Distribute data to workers by group key. Workers perform aggregation in parallel.
`PQ_TWOPHASE_GATHER`	Each worker aggregates separately. The leader merges partial results into the final result.
`PQ_TWOPHASE_HASH`	Each worker aggregates separately, then distributes data to the next-phase workers by group key for final aggregation.
`PQ_SERIAL`	Perform GROUP BY aggregation serially.

Examples

SELECT /*+ PARALLEL(t1) PQ_GROUPBY(PQ_ONEPHASE) */ t1.a, SUM(t1.b) FROM t AS t1 GROUP BY t1.a;

Scans t1 in parallel and distributes data to the next-phase workers based on t1.a. Workers perform aggregation in parallel, and results are gathered by the leader.

SELECT /*+ PARALLEL(t1) PQ_GROUPBY(PQ_TWOPHASE_HASH) */ t1.a, SUM(t1.b) FROM t AS t1 GROUP BY t1.a;

Workers scan t1 in parallel and aggregate data separately. Intermediate results are distributed to the next-phase workers by t1.a for final aggregation. Results are gathered by the leader.

PQ_DISTINCT (DISTINCT aggregation)

Control the parallel execution mode for DISTINCT operations.

Syntax

/*+ PQ_DISTINCT(strategy) */

Strategy values

Strategy	Description
`PQ_TWOPHASE_GATHER`	Each worker aggregates separately. The leader merges partial results into the final result.
`PQ_SERIAL`	Perform DISTINCT aggregation serially.

PQ_WINDOW (window functions)

Control the parallel execution mode for window functions.

Syntax

/*+ PQ_WINDOW([window_name] strategy) */

Note

A hint that specifies a window_name takes priority over a hint without one.

Parameters

Parameter	Description
`window_name`	Name of the window function to apply the strategy to. If omitted, the strategy applies to all window functions.
`strategy`	Execution mode. See valid values below.

Strategy values

Strategy	Description
`PQ_ONEPHASE`	Distribute data by the PARTITION BY clause. Workers compute the window function in parallel.
`PQ_SERIAL`	Perform window function computation serially.

Example

SELECT /*+ PQ_WINDOW(PQ_ONEPHASE) PQ_WINDOW(win PQ_SERIAL) */
  ROW_NUMBER() OVER(win) AS 'row_number',
  RANK() OVER(PARTITION BY name ORDER BY salary DESC)
FROM employee_salaries WINDOW win AS (PARTITION BY dept ORDER BY salary DESC);

The named window win runs serially. All other window functions are distributed to workers by the PARTITION BY key for parallel computation.

PQ_ORDERBY (sorting)

Control the parallel execution mode for ORDER BY sorting.

Syntax

/*+ PQ_ORDERBY(strategy) */

Strategy values

Strategy	Description
`PQ_TWOPHASE_GATHER`	Workers sort data in parallel. The leader merges partial results into the final sorted result.
`PQ_SERIAL`	Perform sorting serially.

PQ_PUSHDOWN and NO_PQ_PUSHDOWN (parallel subqueries)

Control the parallel execution policy for subqueries. For details on subquery support, see View parallel query execution plans.

Syntax

/*+ PQ_PUSHDOWN [( [query_block])] */       -- Pushdown policy
/*+ NO_PQ_PUSHDOWN [( [query_block])] */    -- Shared access policy

Examples

Pushdown policy:

EXPLAIN SELECT /*+ PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ QB_NAME(qb1) */ a FROM t1);

Shared access policy:

EXPLAIN SELECT /*+ NO_PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ QB_NAME(qb1) */ a FROM t1);

Inline hint without a query block reference:

EXPLAIN SELECT * FROM t2 WHERE t2.a =
                 (SELECT /*+ NO_PQ_PUSHDOWN() */ a FROM t1);