Scheduled Acceleration

How it works

The PostgreSQL query optimizer handles partitioned tables by generating an optimal plan for each partition, then combining them with the Append operator. For tables with few partitions, this is fast. As the partition count grows, the cost compounds significantly.

Consider a two-level hash-partitioned table with 100 partitions at each level — 10,000 partitions in total. Querying it with the standard Append operator produces a plan like this:

explain analyze select * from part_hash;

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Append  (cost=0.00..344500.00 rows=16300000 width=22)
   ->  Seq Scan on part_hash_sys0102  (cost=0.00..26.30 rows=1630 width=22)
   ->  Seq Scan on part_hash_sys0103  (cost=0.00..26.30 rows=1630 width=22)
   ->  Seq Scan on part_hash_sys0104  (cost=0.00..26.30 rows=1630 width=22)
  ...
   ->  Seq Scan on part_hash_sys10200  (cost=0.00..26.30 rows=1630 width=22)
 Planning Time: 3183.644 ms
 Execution Time: 633.779 ms
(10003 rows)

Planning time exceeds 3 seconds. On a standard table, the same query plans in under 0.1 ms — a difference of several hundred times. Beyond slow planning, the process also consumes large amounts of memory, which can cause out-of-memory (OOM) errors.

The problem is worse for join queries. A join between two 10,000-partition tables with Append produces a planning time of over 221 seconds.

PartitionedTableScan solves this by replacing the per-partition plan generation used by Append with a single unified operator. The same queries with PartitionedTableScan enabled:

explain analyze select * from part_hash;

                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 PartitionedTableScan on part_hash  (cost=0.00..1.00 rows=1 width=22) (actual time=134.348..134.352 rows=0 loops=1)(Iteration partition number 10000)
    Scan Partitions: part_hash_sys0102, part_hash_sys0103, ...part_hash_sys10198, part_hash_sys10199, part_hash_sys10200
    ->  Seq Scan on part_hash  (cost=0.00..1.00 rows=1 width=22)
  Planning Time: 293.778 ms
  Execution Time: 384.202 ms
(5 rows)

explain analyze select count(*) from part_hash a join part_hash2 b on a.a=b.b where b.c = '0001';

                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2.02..2.03 rows=1 width=8) (actual time=152.322..152.326 rows=1 loops=1)
    ->  Nested Loop  (cost=0.00..2.02 rows=1 width=0) (actual time=152.308..152.311 rows=0 loops=1)
          Join Filter: (a.a = b.b)
          ->  PartitionedTableScan on part_hash a  (cost=0.00..1.00 rows=1 width=4) (actual time=152.305..152.306 rows=0 loops=1)(Iteration partition number 10000)
                Scan Partitions: part_hash_sys0102, part_hash_sys0103,, part_hash_sys10198, part_hash_sys10199, part_hash_sys10200
                ->  Seq Scan on part_hash a  (cost=0.00..1.00 rows=1 width=4)
          ->  PartitionedTableScan on part_hash2 b  (cost=0.00..1.00 rows=1 width=4) (never executed)
                ->  Seq Scan on part_hash2 b  (cost=0.00..1.00 rows=1 width=4)
                      Filter: ((c)::text = '0001'::text)
  Planning Time: 732.952 ms
  Execution Time: 436.927 ms
(11 rows)

Planning time comparison with 10,000 partitions:

Limitations

• Supported on PolarDB for PostgreSQL 14 with a minor engine version of 2.0.11.9.32.0 or later. To enable this feature on a cluster with an earlier version, contact us.

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

• Incompatible with partition-wise join. When partition-wise join is enabled, the optimizer does not generate a PartitionedTableScan plan.

Enable PartitionedTableScan

SET polar_num_parts_for_pts 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)

EXPLAIN SELECT /*+PARTEDSCAN(prt1) */ * 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)

Parallel query

PartitionedTableScan supports parallel query through the Parallel PartitionedTableScan operator. Two parallelism modes are available:

EXPLAIN (COSTS OFF) SELECT /*+PARTEDSCAN(prt1) */ * FROM prt1;

                 QUERY PLAN
---------------------------------------------
 Gather
   Workers Planned: 4
   ->  Parallel PartitionedTableScan on prt1
         ->  Seq Scan on prt1
(4 rows)

EXPLAIN (COSTS OFF) SELECT /*+PARTEDSCAN(prt1) */ * FROM prt1;

                 QUERY PLAN
---------------------------------------------
 Gather
   Workers Planned: 8
   ->  Parallel PartitionedTableScan on prt1
         ->  Parallel Seq Scan on prt1
(4 rows)

Partition pruning

PartitionedTableScan supports partition pruning in three stages, the same as Append. For details, see Partition pruning.

Performance benchmark

Note: The following data is from a development environment and is for reference only. Actual performance varies based on your configuration and workload. The test isolates the operator as the only variable between Append and PartitionedTableScan.

Test setup:

-- Create the test table
CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY HASH(a) PARTITIONS 16;

-- Benchmark queries
pgbench -i --scale=10
pgbench -c 64 -j 64 -n -T60

-- Append
EXPLAIN SELECT * FROM prt1 WHERE b = 10;

-- PartitionedTableScan
EXPLAIN SELECT /*+PARTEDSCAN(prt1) */ * FROM prt1 WHERE b = 10;

What's next

• Parallel query

• Partition pruning

• Partition-wise join