PolarDB:Correlated subquery pull-up

Applicability

Fitur ini didukung di kluster PolarDB for PostgreSQL yang menjalankan PostgreSQL 14 serta memiliki versi mesin minor 2.0.14.8.11.0 atau lebih baru.

Informasi latar belakang

Pengoptimal PostgreSQL menggunakan SubLink untuk merepresentasikan subkueri beserta operator terkaitnya dalam suatu ekspresi. Jenis-jenis SubLink berikut tersedia:

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

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

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

Pengoptimal biasanya berusaha menarik ke atas (pull up) subkueri berkorelasi yang menggunakan operator ANY, IN, EXISTS, atau NOT EXISTS. Hal ini memungkinkan subkueri dan kueri luarnya dioptimalkan bersama menjadi rencana eksekusi yang menggunakan semi-join atau anti-join, sehingga meningkatkan performa kueri. Namun, untuk ANY_SUBLINK, jika subkueri mereferensikan variabel dari kueri luar, subkueri tersebut tidak ditarik ke atas. Akibatnya, peluang optimasi bersama dengan kueri luar terlewatkan, dan subkueri hanya dapat dioptimalkan secara independen—yang secara signifikan meningkatkan waktu eksekusi SQL.

PolarDB for PostgreSQL dan memungkinkan Anda menggunakan parameter untuk mengontrol penarikan ke atas subkueri berkorelasi ANY_SUBLINK. Untuk subkueri berkorelasi yang menggunakan IN atau ANY, subkueri dapat ditarik ke atas meskipun mereferensikan variabel dari kueri luar. Hal ini memperluas ruang pencarian pengoptimal dan membantu menghasilkan rencana eksekusi yang lebih baik.

Penggunaan

Parameter polar_enable_pullup_with_lateral mengaktifkan atau menonaktifkan penarikan ke atas subkueri berkorelasi ANY_SUBLINK. Nilai yang valid adalah:

• ON (default): Mengaktifkan penarikan ke atas subkueri berkorelasi ANY_SUBLINK.

• OFF: Menonaktifkan penarikan ke atas subkueri berkorelasi ANY_SUBLINK.

Example

Prepare 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;

View the execution plan and running time after the feature is disabled.

=> 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)

View the execution plan and running time after the feature is enabled.

=> 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)

Contoh ini menunjukkan bahwa setelah subkueri ditarik ke atas, pengoptimal menggabungkan subkueri dan kueri luar menjadi semi-join. Kondisi filter dalam subkueri kemudian secara signifikan mengurangi set hasil dari kueri luar, sehingga sangat mengurangi waktu eksekusi. Jika subkueri tidak ditarik ke atas, subkueri tersebut tidak dapat menyaring baris yang dikembalikan oleh kueri luar.