PolarDB:Operator PartitionedTableScan

Cakupan

Operator ini didukung di PolarDB for PostgreSQL untuk PostgreSQL 14 dengan versi mesin minor 2.0.14.9.15.0 atau yang lebih baru.

Informasi Latar Belakang

Saat melakukan pemindaian pada tabel partisi, pengoptimal menghasilkan rencana eksekusi untuk setiap subpartisi lalu merangkainya menggunakan operator Append. Rangkaian rencana tersebut menjadi rencana eksekusi untuk pemindaian tabel partisi. Jika jumlah subpartisi kecil, proses ini berlangsung cepat. Namun, PolarDB for PostgreSQL tidak membatasi jumlah partisi dalam tabel partisi. Ketika jumlah subpartisi besar, waktu dan memori yang dikonsumsi oleh pengoptimal meningkat tajam—peningkatan ini terutama terlihat jelas jika dibandingkan dengan pemindaian tabel standar berukuran sama.

Untuk mengatasi masalah ini, PolarDB for PostgreSQL menyediakan operator PartitionedTableScan (PTS). Dibandingkan dengan operator Append, operator PTS secara signifikan mengurangi waktu yang dibutuhkan pengoptimal untuk menghasilkan rencana eksekusi dan mengonsumsi lebih sedikit memori selama eksekusi SQL, sehingga membantu mencegah error out-of-memory (OOM).

Batasan

• Operator PTS saat ini hanya mendukung pernyataan SELECT. Operator ini tidak mendukung pernyataan DML.

• Operator PTS tidak mendukung partition-wise joins. Jika Anda mengaktifkan enable_partitionwise_join, pengoptimal tidak akan menghasilkan rencana eksekusi yang mengandung operator PTS.

Deskripsi parameter

Penggunaan

Aktifkan operator PTS dengan mengatur parameter

SET polar_num_parts_for_pts TO 64;

Gunakan hint

Gunakan sintaks hint PTScan(tablealias). Contohnya:

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)

Kueri paralel

Operator PTS mendukung kueri paralel, termasuk inter-partition parallelism dan hybrid parallelism. Keduanya diaktifkan secara default dan tidak memerlukan konfigurasi tambahan.

• Inter-partition parallelism: Setiap proses worker melakukan kueri pada satu partisi.

• Hybrid parallelism: Eksekusi paralel didukung baik antar partisi maupun di dalam satu partisi.

Contoh

1. Buat dua tabel partisi dan buat 1.000 subpartisi untuk masing-masing.

   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. Berikut adalah rencana eksekusi untuk pemindaian tabel penuh pada tabel partisi.

   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. Saat Anda melakukan join antara dua tabel partisi dalam sebuah kueri, waktu pembuatan rencana eksekusi yang lama dan konsumsi memori tinggi menjadi lebih terlihat.

   => 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. Contoh-contoh sebelumnya menunjukkan bahwa pemindaian tabel penuh pada tabel partisi tidak memiliki keunggulan dibandingkan pemindaian pada tabel standar. Hal ini karena kueri tidak menggunakan kunci partisi sebagai kondisi filter, sehingga mencegah partition pruning. Dalam skenario ini, tabel partisi kurang efisien dibandingkan tabel standar. Praktik terbaik untuk tabel partisi adalah selalu menggunakan partition pruning untuk memfokuskan kueri hanya pada sejumlah kecil partisi. Namun, beberapa skenario Pemrosesan Analitik Online (OLAP) memerlukan pemindaian tabel penuh. Dalam kasus seperti ini, operator PTS lebih efisien dibandingkan operator 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)

   Hasil menunjukkan bahwa penggunaan operator PTS secara signifikan memperpendek waktu pembuatan rencana eksekusi.

Perbandingan Kinerja

Waktu pembuatan rencana eksekusi untuk satu pernyataan SQL

Penggunaan memori untuk satu pernyataan SQL

QPS PGBench