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.
Example
-- Create a partitioned table.
gaussdb=# CREATE TABLE partition_3_part
(
a INTEGER,
b INTEGER,
c INTEGER
) PARTITION BY HASH(a) (
PARTITION p1,
PARTITION p2,
PARTITION p3
);
-- Enable SMP and Partition-wise Join.
gaussdb=# SET query_dop = 5;
SET
gaussdb=# SET enable_smp_partitionwise = on;
SET
-- Use the partitionwisescan hint.
gaussdb=# EXPLAIN SELECT /*+ partitionwisescan(partition_3_part)*/ count(*) FROM partition_3_part;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Aggregate (cost=14.24..14.31 rows=1 width=8)
-> Streaming (type: GATHER) (cost=14.24..14.31 rows=3 width=8)
Node/s: All datanodes
-> Aggregate (cost=14.18..14.19 rows=3 width=8)
-> Partition Iterator (cost=0.00..14.14 rows=30 width=0)
Iterations: 3
-> Partitioned Seq Scan on partition_3_part (cost=0.00..14.14 rows=30 width=0)
Selected Partitions: 1..3
(8 rows)
-- Drop the partitioned table.
gaussdb=# DROP TABLE partition_3_part;
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot