Strategi materialisasi CTE - Hologres

Common Table Expression (CTE) adalah fitur dalam SQL yang menyederhanakan kueri kompleks dan meningkatkan keterbacaan. Saat CTE digunakan, pengoptimal dapat mematerialisasi set hasilnya. Proses ini menyimpan sementara set hasil untuk digunakan kembali di bagian berikutnya dari kueri. Strategi penggunaan ulang CTE menentukan bagaimana pengoptimal menggunakan kembali CTE untuk menghindari perhitungan berulang dan meningkatkan performa kueri.

Tetapkan strategi penggunaan ulang CTE

Dalam Hologres, pengoptimal dapat secara otomatis menentukan apakah akan menggunakan kembali CTE berdasarkan rencana eksekusi SQL.

Pada versi sebelum Hologres V3.2, Anda dapat mengontrol penggunaan ulang CTE hanya dengan parameter Grand Unified Configuration (GUC) optimizer_cte_inlining.

Pada Hologres V3.2 dan yang lebih baru, Anda dapat mengontrol penggunaan ulang CTE menggunakan parameter GUC optimizer_cte_inlining dan hg_cte_strategy.

Deskripsi parameter

Parameter

Deskripsi

Versi yang didukung

optimizer_cte_inlining

ON (default): Memaksa CTE untuk di-inlining.
OFF: Mengatur strategi CTE ke Reuse.

Semua versi

hg_cte_strategy

AUTO (default): Pengoptimal secara otomatis menentukan apakah akan menggunakan kembali CTE.
INLINING: Memaksa inlining. CTE tidak digunakan kembali, dan subkueri dihitung ulang setiap kali CTE dirujuk. Ini cocok untuk skenario di mana komputasi CTE sederhana dan set hasilnya kecil.
REUSE: Memaksa penggunaan ulang. CTE dihitung hanya sekali, dan hasilnya disimpan dalam cache untuk digunakan kembali. Ini cocok untuk skenario di mana komputasi CTE kompleks, set hasilnya besar, dan CTE dirujuk berkali-kali.

V3.2 dan yang lebih baru

Pengaturan parameter

Versi sebelum Hologres V3.2
```
SET optimizer_cte_inlining ={on|off}
```
Hologres V3.2 dan yang lebih baru
```
SET optimizer_cte_inlining ={on|off}

SET hg_cte_strategy ={AUTO|INLINING|REUSE};
```
- Catatan penggunaan
  - Parameter optimizer_cte_inlining memiliki prioritas lebih tinggi daripada parameter hg_cte_strategy.
  - Jika Anda mengatur optimizer_cte_inlining = off, strategi CTE dipaksa menjadi `REUSE`. Dalam kasus ini, pengaturan parameter `hg_cte_strategy` tidak berpengaruh. Anda hanya dapat menggunakan nilai default `AUTO` untuk `hg_cte_strategy` atau secara eksplisit mengaturnya ke `REUSE`. Anda tidak dapat secara eksplisit mengaturnya ke `INLINING`, karena akan memunculkan kesalahan.
  - Ketika optimizer_cte_inlining = on diatur (baik secara default maupun eksplisit), strategi CTE tidak secara otomatis diatur ke `INLINING`. Sebaliknya, strategi ditentukan oleh nilai hg_cte_strategy.

Contoh

Contoh berikut menunjukkan bagaimana rencana eksekusi berubah ketika Anda mengatur parameter hg_cte_strategy ke nilai yang berbeda.

Siapkan data sampel.

CREATE TABLE t1 (
    a INT,
    b INT,
    c INT
);

Lihat rencana eksekusi untuk pengaturan hg_cte_strategy yang berbeda.

Ketika hg_cte_strategy diatur ke nilai default `AUTO`, rencana eksekusi adalah sebagai berikut. Rencana menunjukkan bahwa `cte1` digunakan kembali, tetapi `cte2` tidak.

EXPLAIN WITH cte1 AS (SELECT DISTINCT a,b,c FROM t1),cte2 AS (SELECT a,b,c FROM t1)
SELECT * FROM cte1 
UNION ALL 
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
UNION ALL
SELECT * FROM cte2;

Hasil berikut dikembalikan.

QUERY PLAN
Gather  (cost=0.00..25.00 rows=4 width=12)
  CTE cte1  (cost=0.00..5.00 rows=1 width=12)
      ->  Forward  (cost=0.00..5.00 rows=1 width=12)
            ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
                  Group Key: t1_2.a, t1_2.b, t1_2.c
                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                        Hash Key: t1_2.a, t1_2.b, t1_2.c
                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                              ->  Seq Scan on t1 t1_2  (cost=0.00..5.00 rows=1 width=12)
  ->  Append  (cost=0.00..20.00 rows=4 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=12)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 3.2.0

Ketika hg_cte_strategy diatur ke `INLINING`, rencana eksekusi adalah sebagai berikut. Rencana menunjukkan bahwa tidak ada CTE yang digunakan kembali.

SET hg_cte_strategy = INLINING;
EXPLAIN WITH cte1 AS (SELECT DISTINCT a,b,c FROM t1), 
cte2 AS (SELECT a,b,c FROM t1) 
SELECT * FROM cte1 
UNION ALL 
SELECT * FROM cte1 
UNION ALL 
SELECT * FROM cte2 
UNION ALL 
SELECT * FROM cte2;

Hasil berikut dikembalikan.

QUERY PLAN
Gather  (cost=0.00..20.00 rows=4 width=12)
  ->  Append  (cost=0.00..20.00 rows=4 width=12)
        ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
              Group Key: t1.a, t1.b, t1.c
              ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                    Hash Key: t1.a, t1.b, t1.c
                    ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                          ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=12)
        ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
              Group Key: t1_1.a, t1_1.b, t1_1.c
              ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                    Hash Key: t1_1.a, t1_1.b, t1_1.c
                    ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                          ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1 t1_2  (cost=0.00..5.00 rows=1 width=12)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
              ->  Seq Scan on t1 t1_3  (cost=0.00..5.00 rows=1 width=12)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 3.2.0

Ketika hg_cte_strategy diatur ke `REUSE`, rencana eksekusi adalah sebagai berikut. Rencana menunjukkan bahwa baik `cte1` maupun `cte2` digunakan kembali.

SET hg_cte_strategy = REUSE;
EXPLAIN WITH cte1 AS (SELECT DISTINCT a,b,c FROM t1), 
cte2 AS (SELECT a,b,c FROM t1) 
SELECT * FROM cte1 
UNION ALL 
SELECT * FROM cte1 
UNION ALL
SELECT * FROM cte2 
UNION ALL
SELECT * FROM cte2;

Hasil berikut dikembalikan.

QUERY PLAN
Gather  (cost=0.00..30.00 rows=4 width=12)
  CTE cte1  (cost=0.00..5.00 rows=1 width=12)
      ->  Forward  (cost=0.00..5.00 rows=1 width=12)
            ->  HashAggregate  (cost=0.00..5.00 rows=1 width=12)
                  Group Key: t1_1.a, t1_1.b, t1_1.c
                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=12)
                        Hash Key: t1_1.a, t1_1.b, t1_1.c
                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                              ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=12)
  CTE cte2  (cost=0.00..5.00 rows=1 width=12)
      ->  Forward  (cost=0.00..5.00 rows=1 width=12)
            ->  Local Gather  (cost=0.00..5.00 rows=1 width=12)
                  ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=12)
  ->  Append  (cost=0.00..20.00 rows=4 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte1  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte2  (cost=0.00..5.00 rows=1 width=12)
        ->  CTE Scan on cte2  (cost=0.00..5.00 rows=1 width=12)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 3.2.0