All Products
Search

This Product

    PolarDB:Transformasi kueri berbasis biaya (CBQT)

    Document Center

    PolarDB:Transformasi kueri berbasis biaya (CBQT)

    Last Updated:Jul 06, 2025

    PolarDB for PostgreSQL mendukung transformasi kueri berbasis biaya (CBQT) yang melakukan jenis transformasi tertentu berdasarkan biaya eksekusi. Hal ini meningkatkan efisiensi eksekusi beberapa kueri kompleks secara signifikan.

    Informasi latar belakang

    Transformasi kueri adalah proses mengubah pernyataan kueri menjadi pernyataan lain yang setara. Dalam PostgreSQL, transformasi kueri umum mencakup subquery pull-up, penghapusan outer join, preprocessing ekspresi, penghapusan join yang tidak diperlukan, dan penurunan predikat. Transformasi ini dilakukan berdasarkan aturan kesetaraan. Setelah transformasi selesai, rencana kueri dioptimalkan. PostgreSQL secara otomatis melakukan transformasi ini.

    Namun, beberapa transformasi seperti sublink pushdown dan konversi OR ke UNION ALL dapat menghasilkan rencana kueri yang kurang optimal. Oleh karena itu, PolarDB for PostgreSQL menggunakan pendekatan transformasi kueri berbasis biaya (CBQT).

    Untuk kueri kompleks, kerangka kerja CBQT mengumpulkan transformasi kueri berbasis biaya yang dapat diterapkan pada setiap blok kueri dan membentuk ruang keadaan. CBQT menelusuri ruang keadaan menggunakan strategi tertentu untuk memilih keadaan dengan biaya terendah.

    Seperti yang ditunjukkan pada gambar berikut, untuk pernyataan SQL masukan, CBQT mengumpulkan transformasi kueri berbasis biaya A dan B di Query Block 1 dan Query Block 2. Ruang keadaan mencakup keadaan-keadaan berikut:

    • Tidak ada: Tidak ada transformasi yang dilakukan.

    • [1, A]: Transformasi A diterapkan untuk Query Block 1.

    • [1, B]: Transformasi B diterapkan untuk Query Block 1.

    • [2, A]: Transformasi A diterapkan untuk Query Block 2.

    CBQT menerapkan transformasi ini di ruang keadaan secara bergantian. Dengan strategi pencarian default linear, keadaan [1, B] menghasilkan rencana kueri paling optimal. Akibatnya, [1, B] digunakan dalam Rencana 4.

    Prasyarat

    Kluster PolarDB for PostgreSQL Anda harus menjalankan versi mesin database berikut:

    • PolarDB for PostgreSQL 14 dengan versi revisi 2.0.14.13.28.0 atau lebih baru.

    • PolarDB for PostgreSQL 11 dengan versi revisi 2.0.11.15.44.0 atau lebih baru.

    Catatan

    Anda dapat melihat versi revisi di konsol atau mengeksekusi pernyataan SHOW polardb_version; untuk memeriksa versi revisi. Untuk memperbarui versi revisi, lihat Manajemen Versi Revisi.

    Penggunaan

    Konfigurasikan parameter berikut untuk mengelola CBQT yang disediakan oleh PolarDB for PostgreSQL:

    Catatan

    Untuk kluster dengan versi revisi 2.0.14.15.29.0 atau lebih baru, Anda dapat langsung mengonfigurasi parameter di konsol PolarDB. Untuk informasi lebih lanjut, lihat Konfigurasikan Parameter Kluster. Anda juga dapat terhubung ke kluster untuk mengonfigurasi parameter. Untuk informasi tentang cara terhubung ke kluster, lihat Terhubung ke Kluster PolarDB for PostgreSQL.

    Parameter

    Deskripsi

    polar_enable_cbqt

    Menentukan apakah akan mengaktifkan fitur CBQT. Nilai valid:

    • off (default)

    • on

    polar_cbqt_cost_threshold

    Ambang biaya untuk mengaktifkan CBQT. Nilai valid: [0, +∞). Nilai default: 50000. CBQT hanya diaktifkan jika biaya rencana asli melebihi ambang batas.

    polar_cbqt_strategy

    Strategi pencarian untuk ruang keadaan CBQT. Nilai valid:

    • linear (default): pencarian linear. Jika strategi ini digunakan, PolarDB membandingkan biaya eksekusi sebelum dan sesudah setiap keadaan diterapkan, dan memilih rencana optimal.

    • twophase: pencarian dua fase. Jika strategi ini digunakan, PolarDB membandingkan biaya eksekusi ketika semua keadaan diterapkan atau semua kueri tidak diterapkan, dan kemudian memilih rencana optimal.

    polar_cbqt_iteration_limit

    Jumlah iterasi CBQT. Nilai valid: [1, +∞). Nilai default: 10.

    Jumlah iterasi yang lebih besar menunjukkan kemungkinan lebih tinggi untuk memilih rencana optimal dan lebih banyak waktu yang dibutuhkan. Jumlah iterasi yang lebih kecil menunjukkan kemungkinan lebih rendah untuk memilih rencana optimal dan lebih sedikit waktu yang dibutuhkan.

    Fitur penulisan ulang kueri berbasis biaya berikut didukung:

    Parameter

    Deskripsi

    polar_cbqt_convert_or_to_union_all_mode

    Mengonversi klausa OR menjadi UNION ALL untuk meningkatkan efisiensi kueri. Untuk informasi lebih lanjut, lihat Mengonversi kondisi OR menjadi UNION ALL dari dua atau lebih kueri terpisah:

    polar_cbqt_pushdown_sublink

    Menurunkan sublink ke subquery dan menggunakan indeks di subquery untuk menghasilkan jalur parameterisasi guna meningkatkan efisiensi kueri. Untuk informasi lebih lanjut, lihat Penurunan Sublink.

    Contoh

    Dalam contoh ini, fitur penurunan sublink digunakan untuk mendemonstrasikan fitur CBQT dan parameternya.

    1. Buat tabel uji dan sisipkan data uji.

      CREATE TABLE t_small(a int);
CREATE TABLE t_big(a int, b int, c int);

CREATE INDEX ON t_big(a);

INSERT INTO t_big SELECT i, i, i FROM generate_series(1, 1000000)i;
INSERT INTO t_small VALUES(1), (1000000);

ANALYZE t_small, t_big;

    2. Nonaktifkan fitur CBQT dan aktifkan fitur penurunan sublink. Dalam hal ini, fitur penurunan sublink tidak berlaku. Pemindaian tabel penuh dilakukan pada tabel t_big, yang tidak efisien.

      -- Nonaktifkan fitur CBQT
SET polar_enable_cbqt to off;

-- Aktifkan fitur penurunan sublink
SET polar_cbqt_pushdown_sublink to on;

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

      Hasil sampel:

                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=1.46..59511.17 rows=10000 width=12) (actual time=0.052..1274.435 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46910.13 rows=1000000 width=12) (actual time=0.033..1151.005 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.13 rows=1000000 width=8) (actual time=0.022..433.821 rows=1000000 loops=1)
   ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.015..0.016 rows=2 loops=1)
         Sort Key: t_small.a
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=1)
 Planning Time: 0.904 ms
 Execution Time: 1274.539 ms
(11 rows)

    3. Aktifkan fitur CBQT dan fitur penurunan sublink. Dalam hal ini, fitur penurunan sublink berlaku. Klausa a in (select a from t_small) diturunkan ke subquery. Jalur parameterisasi dihasilkan untuk tabel t_big berdasarkan kondisi join, yang secara signifikan mengurangi jumlah data yang harus dipindai dan waktu eksekusi.

      -- Aktifkan fitur CBQT
SET polar_enable_cbqt to on;

-- Aktifkan fitur penurunan sublink
SET polar_cbqt_pushdown_sublink to on;

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

      Hasil sampel:

                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=17.96..17.99 rows=2 width=12) (actual time=0.060..0.063 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.052..0.053 rows=2 loops=1)
         Sort Key: t_big.a
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8) (actual time=0.032..0.046 rows=2 loops=1)
               ->  Unique  (cost=1.03..1.04 rows=2 width=4) (actual time=0.014..0.018 rows=2 loops=1)
                     ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.013..0.014 rows=2 loops=1)
                           Sort Key: t_small.a
                           Sort Method: quicksort  Memory: 25kB
                           ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4) (actual time=0.006..0.007 rows=2 loops=1)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..8.44 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.644 ms
 Execution Time: 0.150 ms
(15 rows)

    4. Jika biaya eksekusi pernyataan SQL tidak mencapai ambang biaya CBQT, fitur CBQT tidak diaktifkan. Rencana kueri asli digunakan. Dalam contoh berikut, biaya dari rencana kueri asli adalah 59511.17 dan parameter polar_cbqt_cost_threshold diatur ke 500000:

      -- Aktifkan fitur CBQT
SET polar_enable_cbqt to on;

--- Atur ambang biaya CBQT
SET polar_cbqt_cost_threshold to 500000;

-- Aktifkan fitur penurunan sublink
SET polar_cbqt_pushdown_sublink to on;

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

      Hasil sampel:

                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=1.46..59511.17 rows=10000 width=12) (actual time=0.059..1253.452 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46910.13 rows=1000000 width=12) (actual time=0.041..1127.255 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.13 rows=1000000 width=8) (actual time=0.029..414.488 rows=1000000 loops=1)
   ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.014..0.015 rows=2 loops=1)
         Sort Key: t_small.a
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=1)
 Planning Time: 0.280 ms
 Execution Time: 1253.558 ms
(11 rows)

    5. Atur strategi pencarian untuk ruang keadaan CBQT. Dalam contoh berikut, dua sublink dapat diturunkan, tetapi menurunkan hanya sublink kedua adalah optimal.

      • Atur polar_cbqt_strategy ke linear, yaitu strategi pencarian linear. CBQT memilih rencana optimal.

        -- Aktifkan fitur CBQT
SET polar_enable_cbqt to on;

--- Atur strategi pencarian untuk ruang keadaan CBQT
SET polar_cbqt_strategy to linear;

-- Aktifkan fitur penurunan sublink
SET polar_cbqt_pushdown_sublink to on;

EXPLAIN SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_big) UNION ALL SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

        Hasil sampel:

                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Append  (cost=0.85..105692.60 rows=500002 width=12)
   ->  Merge Semi Join  (cost=0.85..98174.56 rows=500000 width=12)
         Merge Cond: (t_big_1.a = t_big.a)
         ->  GroupAggregate  (cost=0.42..46910.13 rows=1000000 width=12)
               Group Key: t_big_1.a
               ->  Index Scan using t_big_a_idx on t_big t_big_1  (cost=0.42..31910.13 rows=1000000 width=8)
         ->  Index Only Scan using t_big_a_idx on t_big  (cost=0.42..26264.42 rows=1000000 width=4)
   ->  GroupAggregate  (cost=17.96..17.99 rows=2 width=12)
         Group Key: t_big_2.a
         ->  Sort  (cost=17.96..17.96 rows=2 width=8)
               Sort Key: t_big_2.a
               ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8)
                     ->  Unique  (cost=1.03..1.04 rows=2 width=4)
                           ->  Sort  (cost=1.03..1.03 rows=2 width=4)
                                 Sort Key: t_small.a
                                 ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4)
                     ->  Index Scan using t_big_a_idx on t_big t_big_2  (cost=0.42..8.44 rows=1 width=8)
                           Index Cond: (a = t_small.a)
(18 rows)

      • Atur polar_cbqt_strategy ke twophase, yaitu strategi pencarian dua fase. Kedua sublink diturunkan.

        -- Aktifkan fitur CBQT
SET polar_enable_cbqt to on;

--- Atur strategi pencarian untuk ruang keadaan CBQT
SET polar_cbqt_strategy to twophase;

-- Aktifkan fitur penurunan sublink
SET polar_cbqt_pushdown_sublink to on;

EXPLAIN SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_big) UNION ALL SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

        Hasil sampel:

                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.85..113192.60 rows=1000002 width=12)
   ->  GroupAggregate  (cost=0.85..88174.56 rows=1000000 width=12)
         Group Key: t_big.a
         ->  Merge Semi Join  (cost=0.85..73174.56 rows=1000000 width=8)
               Merge Cond: (t_big.a = t_big_1.a)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.13 rows=1000000 width=8)
               ->  Index Only Scan using t_big_a_idx on t_big t_big_1  (cost=0.42..26264.42 rows=1000000 width=4)
   ->  GroupAggregate  (cost=17.96..17.99 rows=2 width=12)
         Group Key: t_big_2.a
         ->  Sort  (cost=17.96..17.96 rows=2 width=8)
               Sort Key: t_big_2.a
               ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8)
                     ->  Unique  (cost=1.03..1.04 rows=2 width=4)
                           ->  Sort  (cost=1.03..1.03 rows=2 width=4)
                                 Sort Key: t_small.a
                                 ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4)
                     ->  Index Scan using t_big_a_idx on t_big t_big_2  (cost=0.42..8.44 rows=1 width=8)
                           Index Cond: (a = t_small.a)
(18 rows)

    6. Atur jumlah iterasi CBQT. Atur polar_cbqt_iteration_limit ke 1 untuk membatasi jumlah iterasi CBQT menjadi 1. Dalam skenario sebelumnya, meskipun menurunkan hanya sublink kedua adalah optimal, CBQT tidak menerapkan keadaan tersebut karena jumlah iterasi yang terbatas.

      -- Aktifkan fitur CBQT
SET polar_enable_cbqt to on;

--- Atur strategi pencarian untuk ruang keadaan CBQT
SET polar_cbqt_strategy to twophase;

--- Atur jumlah iterasi CBQT
SET polar_cbqt_iteration_limit to 1;

-- Aktifkan fitur penurunan sublink
SET polar_cbqt_pushdown_sublink to on;

EXPLAIN SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_big) UNION ALL SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);

      Hasil sampel:

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.85..113192.60 rows=1000002 width=12)
   ->  GroupAggregate  (cost=0.85..88174.56 rows=1000000 width=12)
         Group Key: t_big.a
         ->  Merge Semi Join  (cost=0.85..73174.56 rows=1000000 width=8)
               Merge Cond: (t_big.a = t_big_1.a)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.13 rows=1000000 width=8)
               ->  Index Only Scan using t_big_a_idx on t_big t_big_1  (cost=0.42..26264.42 rows=1000000 width=4)
   ->  GroupAggregate  (cost=17.96..17.99 rows=2 width=12)
         Group Key: t_big_2.a
         ->  Sort  (cost=17.96..17.96 rows=2 width=8)
               Sort Key: t_big_2.a
               ->  Nested Loop  (cost=1.46..17.95 rows=2 width=8)
                     ->  Unique  (cost=1.03..1.04 rows=2 width=4)
                           ->  Sort  (cost=1.03..1.03 rows=2 width=4)
                                 Sort Key: t_small.a
                                 ->  Seq Scan on t_small  (cost=0.00..1.02 rows=2 width=4)
                     ->  Index Scan using t_big_a_idx on t_big t_big_2  (cost=0.42..8.44 rows=1 width=8)
                           Index Cond: (a = t_small.a)
(18 rows)