PolarDB:The PartitionedTableScan operator

Applicability

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

Background information

When you scan a partitioned table, the optimizer generates an execution plan for each subpartition and then chains them together using the Append operator. This chain of plans becomes the execution plan for the partitioned table scan. If the number of subpartitions is small, this process completes quickly. However, PolarDB for PostgreSQL does not limit the number of partitions in a partitioned table. When the number of subpartitions is large, the time and memory that the optimizer consumes increase sharply. This increase is especially noticeable when compared to scanning a standard table of the same size.

To solve this problem, PolarDB for PostgreSQL provides the PartitionedTableScan (PTS) operator. Compared with the Append operator, the PTS operator significantly reduces the time that the optimizer takes to generate an execution plan. It also consumes less memory during SQL execution, which helps prevent out-of-memory (OOM) errors.

Limits

• The PTS operator currently supports only SELECT statements. It does not support DML statements.

• The PTS operator does not support partition-wise joins. If you enable enable_partitionwise_join, the optimizer will not generate an execution plan that contains the PTS operator.

Parameter descriptions

Usage

Enable the PTS operator by setting a parameter

SET polar_num_parts_for_pts TO 64;

Use a hint

Use the hint syntax PTScan(tablealias). For 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. It supports inter-partition parallelism and hybrid parallelism. Both are enabled by default and do not require any configuration.

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

• Hybrid parallelism: Parallel execution is supported both between partitions and within a single partition.

Examples

1. Create two partitioned tables and create 1,000 subpartitions for 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

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

   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 you join the two partitioned tables in a query, the long execution plan generation time and high memory consumption become more apparent.

   => 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 preceding examples show that a full table scan on a partitioned table has no advantage over a scan on a standard table. This is because the query lacks a partition key as a filter condition, which prevents partition pruning. In this scenario, a partitioned table is less efficient than a standard table. The best practice for partitioned tables is to use partition pruning whenever possible to focus the query on a small number of partitions. However, some Online Analytical Processing (OLAP) scenarios require a full table scan. In these cases, 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 using the PTS operator significantly shortens the execution plan generation time.

Performance comparison

Execution plan generation time for a single SQL statement

Memory usage for a single SQL statement

PGBench QPS