PolarDB：分區表掃描運算元

前提條件

支援的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（核心小版本14.9.15.0及以上）

select version();

背景資訊

對資料庫的分區表進行掃描時，最佳化器會對每個子分區產生最優的執行計畫，然後通過Append運算元將子分區的執行計畫串聯起來，作為分區表掃描的最優執行計畫。如果子分區的數量不多，上述過程將會快速完成。然而，PolarDB PostgreSQL版對分區表的分區數量沒有限制，當子分區數量過多時，最佳化器所使用的時間和SQL執行過程中所使用的記憶體使用量將會急劇增大，與掃描相同大小的普通表相比差距尤為明顯。

為瞭解決該問題，PolarDB PostgreSQL版提供了分區表掃描運算元（PartitionedTableScan，簡稱PTS）。與Append運算元相比，PTS能夠明顯減少最佳化器產生執行計畫的時間，且在SQL執行過程中使用更少的記憶體，能夠有效避免OOM。

使用限制

• PTS當前僅支援SELECT，暫不支援DML語句。

• PTS不支援智能分區串連（Partition Wise Join），如果您開啟了enable_partitionwise_join，將不會產生帶有PTS運算元的執行計畫。

參數說明

使用說明

通過參數開啟PTS運算元

SET polar_num_parts_for_pts TO 64;

使用HINT

使用HINT文法PTScan（tablealias），樣本如下：

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)

並行查詢

PTS運算元支援並行查詢，並行的方式包括分區間並行和混合并行兩種，均已預設開啟，無需調整。

• 分區間並行：每個背景工作處理序查詢一個分區。

• 混合并行：分區間和分區內都可以並存執行。

樣本

1. 建立兩張分區表，並分別建立1000個子分區。

   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. 對分區表進行全表掃描的執行計畫如下。

   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. 將兩張分區表進行串連查詢時，執行計畫的產生時間長和SQL執行過程中記憶體使用量多的問題將會更加明顯。

   => 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. 從上述樣本可以看出，分區表的全表查詢由於缺少分區鍵作為過濾條件，無法將查詢集中在部分分區，相比於普通表是沒有任何優勢的。分區表比普通表更加低效。分區表的最佳實務是儘可能使查詢發生分區裁剪，使查詢可以集中在少部分分區上。但是對於一些OLAP情境，不得不對分區表進行全表掃描。此時，使用PTS運算元比Append運算元更加高效。

   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)

   結果顯示，使用PTS運算元後，執行計畫的產生時間得到了明顯的縮短。

效能對比

單條SQL的執行計畫產生時間

單條SQL的記憶體使用量量

PGBench QPS