PolarDB:The PartitionedTableScan operator

Prerequisites

The feature is supported on PolarDB for PostgreSQL clusters that run the following engine:

PostgreSQL 14 (revision version 14.9.15.0 or later)

select version();

Background information

During the scan of a partitioned table, the optimizer generates the optimal query plan for each partition, and then uses the Append operator to combine these plans in parallel as the optimal query plan for the entire partitioned table. If the number of partitions is small, this process is fast. However, PolarDB for PostgreSQL does not limit the number of partitions in a partitioned table. As the number of partitions grows, the time consumed by the optimizer and the memory consumed by SQL statement executions significantly increase. The performance of querying the partitioned table is notably lower than the performance of querying a common table of the same size.

To meet the requirements for query performance, PolarDB for PostgreSQL provides the PTS operator as a more efficient solution. Compared with the Append operator, the PTS operator significantly reduces the time required by the optimizer to generate query plans and uses less memory to execute SQL statements to avoid OOM errors.

Limits

• The PTS operator can be used only in SELECT statements.

• The PTS operator cannot be used in partition-wise joins. If you enable partition-wise joins, the execution plan does not contain PTS operators.

Parameters

Usage

Enable the PTS operator by using parameters

SET polar_num_parts_for_pts TO 64;

Use hints

Use the hint PTScan(tablealias). See the following example:

EXPLAIN (COSTS OFF, ANALYZE) /*+ PTScan(part_range) */ 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)

Parallel query

The PTS operator supports parallel queries. Inter-partition parallelism and hybrid parallelism are supported and enabled by default. No further configurations are required.

• Inter-partition parallelism: Each worker queries only one partition.

• Hybrid parallelism: Both inter-partition parallel queries and intra-partition parallel queries can be performed.

Examples

1. Create two partitioned tables and create 1,000 partitions.

   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

2. The following query plan for a full table scan on the partitioned table is generated.

   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)

3. When two partitioned tables are joined for queries, the query performance is even lower and even more memory is consumed by SQL statement executions.

   => 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)

4. The example shows that the performance of a full table query of a partitioned table is lower than that of a common table. This is because a full table query does not include any condition that can limit the query in a specific partition. Partition pruning can be used in a full table query to query only a few partitions. However, some online analytical processing (OLAP) queries still need to scan the entire partitioned table. In this case, the PTS operator is more efficient than the Append operator.

   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)

   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)

   The results show that the time required to generate the query plan by using the PTS operator is much less than that by using the Append operator.

Performance comparison

The time required to generate the query plan of a single SQL statement

Memory usage by a single SQL statement

PGBench QPS