PartitionedTableScan Node

Background information

The Append operator is a commonly used but low-performance query operator for partitioned tables. To meet the requirements for query performance, PolarDB provides the PartitionedTableScan operator as a more efficient solution. The PartitionedTableScan operator is used to solve the issue of low query performance when a large number of partitions exist in a partitioned table.

Limits

• The PolarDB for PostgreSQL (Compatible with Oracle) minor engine version must be V1.1.32 or later.

• PartitionedTableScan only supports SELECT statements and does not support DML statements.

• PartitionedTableScan does not support partitionwise joins. If you enable partitionwise joins, the execution plan does not contain PartitionedTableScan operators.

• PartitionedTableScan cannot be executed in parallel.

Precautions

The PartitionedTable Scan Node feature is supported only for clusters whose minor engine version is V1.1.32 and later. If you want to enable this feature for existing clusters, contact technical support.

Usage

Create a partitioned table.

CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY Hash(a) partitions 16;

Enable the PartitionedTableScan operator by using parameters

When the number of partitions of a partitioned table is greater than the value of the polar_num_parts_for_partitionedscan parameter, the PartitionedTableScan operator is generated. Valid values of the polar_num_parts_for_partitionedscan parameter: -1 to 2147483647. Default value: 64.

Example:

SET polar_num_parts_for_partitionedscan to -1;
explain select * from prt1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 PartitionedTableScan on prt1  (cost=0.00..1.00 rows=1 width=40)
   ->  Seq Scan on prt1  (cost=0.00..1.00 rows=1 width=40)
(2 rows)

Use hints

Use the hint syntax PARTEDSCAN (table alias). Example:

EXPLAIN select /*+PARTEDSCAN(prt1) */ select * from prt1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 PartitionedTableScan on prt1  (cost=0.00..1.00 rows=1 width=40)
   ->  Seq Scan on prt1  (cost=0.00..1.00 rows=1 width=40)
(2 rows)

Performance tests

Compared to Append, PartitionedTableScan is more efficient. In this section, tests are conducted to compare the performance difference between PartitionedTableScan and Append.

The following SQL statements are used for testing:

explain select  * from prt1 where b = 10; 

explain select /*+PARTEDSCAN(prt1) */ * from prt1 where b = 10;  

The execution duration of a single SQL statement

Memory usage of a single SQL statement

Queries per second (QPS)

pgbench -i --scale=10
pgbench -c 64 -j 64 -n -T60
Query:
	explain select  * from prt1 where b = 10; 
	explain select /*+PARTEDSCAN(prt1) */ * from prt1 where b = 10;  

Conclusions

Based on the preceding performance tests, PartitionedTableScan shows significant performance improvement compared with Append, especially when a large number of partitions are involved. If the tables that are involved in your business have a large number of partitions, and queries require a long time to be executed, we recommend that you use PartitionedTableScan to improve the performance.