PolarDB:The PartitionedTableScan operator

Applicability

PTS is supported in PolarDB for PostgreSQL 14 with minor engine version 2.0.14.9.15.0 or later.

View the minor engine version in the console or by running SHOW polardb_version;. If the version does not meet the requirement, upgrade the minor engine version.

Background

When the optimizer scans a partitioned table, it generates a separate execution plan for each subpartition and chains them together using the Append operator. With a small number of subpartitions, this is fast. But PolarDB for PostgreSQL places no limit on partition count — and as the number grows into the hundreds or thousands, planning time and memory usage increase sharply:

PTS replaces the Append operator for full scans of heavily partitioned tables. Instead of generating a separate plan per subpartition, PTS generates a single unified plan and applies it across all partitions. This keeps planning time and memory bounded regardless of partition count.

When to use PTS

Partitioning works best when queries include the partition key as a filter, which lets the optimizer skip irrelevant partitions (partition pruning). If your queries consistently target a small number of partitions, partition pruning is the right tool.

PTS is for the opposite case: Online Analytical Processing (OLAP) workloads and other scenarios where queries scan all partitions without a partition key filter. In these cases, partition pruning cannot help, and the overhead of the Append operator becomes the bottleneck.

Limitations

• PTS supports only SELECT statements. Data Manipulation Language (DML) statements are not supported.

• PTS does not support partition-wise joins. If enable_partitionwise_join is enabled, the optimizer will not use PTS.

Parameters

Enable PTS

Option 1: Set the parameter (recommended)

Set polar_num_parts_for_pts to activate PTS automatically when the subpartition count exceeds the threshold:

SET polar_num_parts_for_pts TO 64;

Option 2: Use a hint

Force PTS for a specific query using the PTScan(tablealias) hint syntax:

EXPLAIN (COSTS OFF, ANALYZE) /*+ PTScan(part_range) */ SELECT * FROM part_range;

To confirm PTS is active, check that the EXPLAIN output shows PartitionedTableScan as the top-level operator and lists the scanned partitions:

                                   QUERY PLAN
--------------------------------------------------------------------------------
 PartitionedTableScan on part_range (actual time=86.404..86.405 rows=0 loops=1)
   Scan 1000 Partitions: part_range_p0, part_range_p1, part_range_p2,...
   ->  Seq Scan on part_range
 Planning Time: 36.613 ms
 Execution Time: 89.246 ms
(5 rows)

If the output shows Append instead of PartitionedTableScan, PTS was not selected. Check whether enable_partitionwise_join is enabled, which prevents PTS from being used.

Parallel query

PTS supports parallel queries with two modes, both enabled by default without additional configuration:

• Inter-partition parallelism: Each worker process scans one partition.

• Hybrid parallelism: Parallel execution runs both across partitions and within a single partition.

Examples

The following examples compare the Append operator and PTS on a table with 1,000 subpartitions.

Step 1: Create two partitioned tables with 1,000 subpartitions each

CREATE TABLE part_range (a INT, b VARCHAR, c NUMERIC, d INT8) PARTITION BY RANGE (a);
SELECT 'CREATE TABLE part_range_p' || i || ' PARTITION OF part_range FOR VALUES FROM (' || 10 * i || ') TO (' || 10 * (i + 1) || ');'
FROM generate_series(0,999) i;\gexec

CREATE TABLE part_range2 (a INT, b VARCHAR, c NUMERIC, d INT8) PARTITION BY RANGE (a);
SELECT 'CREATE TABLE part_range2_p' || i || ' PARTITION OF part_range2 FOR VALUES FROM (' || 10 * i || ') TO (' || 10 * (i + 1) || ');'
FROM generate_series(0,999) i;\gexec

Step 2: Run a full table scan with the Append operator

Disable PTS to force the Append operator:

SET polar_num_parts_for_pts TO 0;
EXPLAIN (COSTS OFF, ANALYZE) SELECT * FROM part_range;

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Append (actual time=8.376..8.751 rows=0 loops=1)
   ->  Seq Scan on part_range_p0 part_range_1 (actual time=0.035..0.036 rows=0 loops=1)
   ->  Seq Scan on part_range_p1 part_range_2 (actual time=0.009..0.009 rows=0 loops=1)
   ->  Seq Scan on part_range_p2 part_range_3 (actual time=0.010..0.011 rows=0 loops=1)
  ...
  ...
  ...
   ->  Seq Scan on part_range_p997 part_range_998 (actual time=0.009..0.009 rows=0 loops=1)
   ->  Seq Scan on part_range_p998 part_range_999 (actual time=0.010..0.010 rows=0 loops=1)
   ->  Seq Scan on part_range_p999 part_range_1000 (actual time=0.009..0.009 rows=0 loops=1)
 Planning Time: 785.169 ms
 Execution Time: 163.534 ms
(1003 rows)

The Append operator generates 1,000 separate scan nodes. Planning alone takes 785 ms.

Step 3: Join two partitioned tables with the Append operator

The planning overhead compounds when joining two heavily partitioned tables:

SET polar_num_parts_for_pts TO 0;
EXPLAIN (COSTS OFF, ANALYZE)
      SELECT COUNT(*) FROM part_range a
      JOIN part_range2 b ON a.a = b.a
      WHERE b.c = '0001';

                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=3191.718..3212.437 rows=1 loops=1)
   ->  Gather (actual time=2735.417..3212.288 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual time=2667.247..2667.789 rows=1 loops=3)
               ->  Parallel Hash Join (actual time=1.957..2.497 rows=0 loops=3)
                     Hash Cond: (a.a = b.a)
                     ->  Parallel Append (never executed)
                           ->  Parallel Seq Scan on part_range_p0 a_1 (never executed)
                           ->  Parallel Seq Scan on part_range_p1 a_2 (never executed)
                           ->  Parallel Seq Scan on part_range_p2 a_3 (never executed)
                           ...
                           ...
                           ...
                           ->  Parallel Seq Scan on part_range_p997 a_998 (never executed)
                           ->  Parallel Seq Scan on part_range_p998 a_999 (never executed)
                           ->  Parallel Seq Scan on part_range_p999 a_1000 (never executed)
                     ->  Parallel Hash (actual time=0.337..0.643 rows=0 loops=3)
                           Buckets: 4096  Batches: 1  Memory Usage: 0kB
                           ->  Parallel Append (actual time=0.935..1.379 rows=0 loops=1)
                                 ->  Parallel Seq Scan on part_range2_p0 b_1 (actual time=0.001..0.001 rows=0 loops=1)
                                       Filter: (c = '1'::numeric)
                                 ->  Parallel Seq Scan on part_range2_p1 b_2 (actual time=0.001..0.001 rows=0 loops=1)
                                       Filter: (c = '1'::numeric)
                                 ->  Parallel Seq Scan on part_range2_p2 b_3 (actual time=0.001..0.001 rows=0 loops=1)
                                       Filter: (c = '1'::numeric)
                                 ...
                                 ...
                                 ...
                                 ->  Parallel Seq Scan on part_range2_p997 b_998 (actual time=0.001..0.001 rows=0 loops=1)
                                       Filter: (c = '1'::numeric)
                                 ->  Parallel Seq Scan on part_range2_p998 b_999 (actual time=0.000..0.001 rows=0 loops=1)
                                       Filter: (c = '1'::numeric)
                                 ->  Parallel Seq Scan on part_range2_p999 b_1000 (actual time=0.002..0.002 rows=0 loops=1)
                                       Filter: (c = '1'::numeric)
 Planning Time: 1900.615 ms
 Execution Time: 3694.320 ms
(3013 rows)

Planning time reaches 1.9 seconds. The query lacks a partition key filter, so partition pruning cannot reduce the work — all 1,000 partitions on each table are included in the plan.

Step 4: Run the same queries with PTS

Enable PTS with a threshold of 10:

SET polar_num_parts_for_pts TO 10;
EXPLAIN (COSTS OFF, ANALYZE) SELECT * FROM part_range;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 PartitionedTableScan on part_range (actual time=86.404..86.405 rows=0 loops=1)
   Scan 1000 Partitions: part_range_p0, part_range_p1, part_range_p2,...
   ->  Seq Scan on part_range
 Planning Time: 36.613 ms
 Execution Time: 89.246 ms
(5 rows)

Planning time drops from 785 ms to 37 ms. For the join query:

SET polar_num_parts_for_pts TO 10;
EXPLAIN (COSTS OFF, ANALYZE)
      SELECT COUNT(*) FROM part_range a
      JOIN part_range2 b ON a.a = b.a
      WHERE b.c = '0001';

                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Aggregate (actual time=61.384..61.388 rows=1 loops=1)
   ->  Merge Join (actual time=61.378..61.381 rows=0 loops=1)
         Merge Cond: (a.a = b.a)
         ->  Sort (actual time=61.377..61.378 rows=0 loops=1)
               Sort Key: a.a
               Sort Method: quicksort  Memory: 25kB
               ->  PartitionedTableScan on part_range a (actual time=61.342..61.343 rows=0 loops=1)
                     Scan 1000 Partitions: part_range_p0, part_range_p1, part_range_p2, ...
                     ->  Seq Scan on part_range a
         ->  Sort (never executed)
               Sort Key: b.a
               ->  PartitionedTableScan on part_range2 b (never executed)
                     ->  Seq Scan on part_range2 b
                           Filter: (c = '1'::numeric)
 Planning Time: 96.675 ms
 Execution Time: 64.913 ms
(16 rows)

Planning time drops from 1,900 ms to 97 ms, and total execution time drops from 3,694 ms to 65 ms.

Performance comparison

The following data was collected in a staging environment with consistent configuration and does not represent a standard benchmark. Results are for comparison purposes only.