PolarDB:Correlated subquery pull-up

Versi yang Didukung

Versi PolarDB for PostgreSQL yang didukung:

• PostgreSQL 18 (tidak ada persyaratan versi minor kernel)

• PostgreSQL 17 (tidak ada persyaratan versi minor kernel)

• PostgreSQL 14 (versi minor kernel 2.0.14.8.11.0 atau lebih baru)

Latar Belakang

Dalam pengoptimal PostgreSQL, SubLink digunakan untuk merepresentasikan subkueri beserta operator terkaitnya dalam suatu ekspresi. Jenis-jenis SubLink adalah sebagai berikut:

• EXISTS_SUBLINK: mengimplementasikan subkueri EXISTS (SELECT ...).

• ALL_SUBLINK: mengimplementasikan subkueri ALL (SELECT ...).

• ANY_SUBLINK: mengimplementasikan subkueri ANY (SELECT ...) dan subkueri IN (SELECT ...).

Pengoptimal biasanya mencoba menarik naik (pull up) subkueri berkorelasi jenis ANY/IN/EXISTS/NOT EXISTS agar dapat dioptimalkan bersama kueri induk menjadi rencana eksekusi dengan semi-join atau anti-join, sehingga meningkatkan performa kueri. Namun, untuk ANY_SUBLINK, jika subkueri mereferensikan variabel dari kueri induk, subkueri tersebut tidak ditarik naik, sehingga kehilangan peluang optimasi bersama dengan kueri induk. Subkueri hanya dapat dioptimalkan sebagai unit independen, yang secara signifikan meningkatkan waktu eksekusi SQL.

PolarDB for PostgreSQL menyediakan parameter untuk mengontrol penarikan naik subkueri berkorelasi ANY_SUBLINK. Untuk subkueri berkorelasi IN/ANY, meskipun mereferensikan variabel dari kueri induk, subkueri tetap dapat ditarik naik, sehingga memperluas ruang pencarian pengoptimal dan menghasilkan rencana eksekusi yang lebih baik.

Penggunaan

Parameter polar_enable_pullup_with_lateral mengontrol apakah penarikan naik subkueri berkorelasi ANY_SUBLINK diaktifkan. Nilai yang valid:

• ON (default): mengaktifkan penarikan naik subkueri berkorelasi ANY_SUBLINK.

• OFF: menonaktifkan penarikan naik subkueri berkorelasi ANY_SUBLINK.

Contoh

Siapkan data.

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 SELECT i, 1 FROM generate_series(1, 100000) i;
CREATE TABLE t2 AS SELECT * FROM t1;

Rencana eksekusi dan waktu saat penarikan naik subkueri berkorelasi dinonaktifkan.

=> SET polar_enable_pullup_with_lateral TO OFF;

=> EXPLAIN (COSTS OFF, ANALYZE)
   SELECT * FROM t1
   WHERE t1.a IN (SELECT a FROM t2 WHERE t2.b = t1.b AND t2.b = 1);
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on t1 (actual time=67.631..1641827.119 rows=100000 loops=1)
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Result (actual time=0.005..13.124 rows=50000 loops=100000)
           One-Time Filter: (t1.b = 1)
           ->  Seq Scan on t2 (actual time=0.005..7.718 rows=50000 loops=100000)
                 Filter: (b = 1)
 Planning Time: 0.145 ms
 Execution Time: 1641847.702 ms
(9 rows)

Rencana eksekusi dan waktu saat penarikan naik subkueri berkorelasi diaktifkan.

=> SET polar_enable_pullup_with_lateral TO ON;

=> EXPLAIN (COSTS OFF, ANALYZE)
   SELECT * FROM t1
   WHERE t1.a IN (SELECT a FROM t2 WHERE t2.b = t1.b AND t2.b = 1);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Hash Semi Join (actual time=64.783..173.482 rows=100000 loops=1)
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on t1 (actual time=0.016..25.440 rows=100000 loops=1)
         Filter: (b = 1)
   ->  Hash (actual time=64.550..64.551 rows=100000 loops=1)
         Buckets: 131072  Batches: 2  Memory Usage: 2976kB
         ->  Seq Scan on t2 (actual time=0.010..30.330 rows=100000 loops=1)
               Filter: (b = 1)
 Planning Time: 0.195 ms
 Execution Time: 178.050 ms
(10 rows)

Seperti yang ditunjukkan pada contoh sebelumnya, setelah subkueri ditarik naik, subkueri dan kueri induk dioptimalkan bersama menjadi semi-join. Kondisi filter dari subkueri dapat secara signifikan menyaring hasil kueri induk, sehingga menghasilkan pengurangan waktu eksekusi yang drastis. Tanpa penarikan naik subkueri, baris yang akan dikembalikan dari kueri induk tidak dapat disaring.