Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-8.x)/ SQL Optimization/ Hint-based Optimization/ Hints for Specifying Whether to Use Partition-wise Scan Optimization
Updated on 2025-05-29 GMT+08:00
View PDF
Share

Hints for Specifying Whether to Use Partition-wise Scan Optimization

In the SMP scenario, partitioned tables can be concurrently scanned in either of the following modes:

  • Each thread traverses all partitions, but reads only a part of data in the partitions. Since the data read by threads does not overlap, the data read by all threads combined is the whole data.
  • Each thread is bound to only some partitions and the partitions bound to threads do not overlap. Each thread reads all data from its bound partitions. This mode is called Partition-wise Scan.

In some scenarios, Partition-wise Scan can read data more evenly and has higher concurrent read performance.

You can enable Partition-wise Scan in either of the following ways:

  • Set GUC parameter force_smp_partitionwise_scan.
  • Set the partitionwisescan hint.

Description

Specifies whether the statement uses Partition-wise Scan in partitioned tables in SMP scenarios.

Syntax

[no] partitionwisescan[(@tablename)]

Parameters

  • no

    Does not use Partition-wise Scan to query rewriting.

  • @tablename

    Partitioned table for Partition-wise Scan, which cannot be omitted.

  • partitionwisescan

    Uses Partition-wise Scan to concurrently scan partitioned tables.

    Partition-wise Scan supports only partitioned table scanning in the SMP scenario. SeqScan, IndexScan, IndexonlyScan and ImcvScan are supported.

Example

-- Create a partitioned table.
gaussdb=# CREATE TABLE HASH_PART
(
    a INTEGER,
    b INTEGER,
    c INTEGER
) PARTITION BY HASH(a) (
    PARTITION p1,
    PARTITION p2,
    PARTITION p3
);
CREATE TABLE

-- Enable SMP and Partition-wise Join.
gaussdb=# SET query_dop = 5;
SET
gaussdb=# SET enable_smp_partitionwise = on;
SET
gaussdb=# SET enable_force_smp = on;
SET

-- Use the partitionwisescan hint.
gaussdb=# EXPLAIN (COSTS OFF) SELECT /*+ partitionwisescan(HASH_PART)*/ count(*) FROM HASH_PART;
                        QUERY PLAN                         
-----------------------------------------------------------
 Aggregate
   ->  Streaming(type: LOCAL GATHER dop: 1/5)
         ->  Aggregate
               ->  Partition Iterator
                     Iterations: 3
                     ->  Partitioned Seq Scan on hash_part
                           Selected Partitions:  1..3
(7 rows)

-- Drop the partitioned table.
gaussdb=# DROP TABLE HASH_PART;
Parent topic: Hint-based Optimization