Tingkatkan kinerja kueri dengan Sublink Pushdown untuk IN dan ANY - PolarDB

PolarDB for PostgreSQL memungkinkan Anda menulis ulang kueri untuk mendorong sublink ke bawah. Fitur ini meningkatkan efisiensi eksekusi Pernyataan SQL yang mengandung klausa IN dan ANY.

Informasi Latar Belakang

Dalam PostgreSQL, sublink tipe ANY (klausa IN dan ANY) biasanya ditarik sebagai semi-join (semi join). Namun, jika tabel terhubung adalah subquery yang tidak dapat ditarik sebagai semi-join, PostgreSQL tidak dapat membuat jalur berparameter untuknya. Akibatnya, subquery dieksekusi secara independen, yang memengaruhi kinerja SQL ketika subquery melibatkan sejumlah besar data.

Sebagai contoh, subquery dari pernyataan SQL berikut mengandung klausa GROUP BY dan tidak dapat ditarik ke atas. Waktu eksekusi terutama bergantung pada pemindaian dan pengurutan tabel t_big. Seiring bertambahnya ukuran tabel t_big, waktu eksekusi juga bertambah.

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big GROUP BY a)v WHERE a IN (SELECT a FROM t_small);
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=0.55..59523.15 rows=10000 width=12) (actual time=0.064..1237.621 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46910.99 rows=1000000 width=12) (actual time=0.033..1113.615 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31910.99 rows=1000000 width=8) (actual time=0.024..420.575 rows=1000000 loops=1)
   ->  Index Only Scan using t_small_a_idx on t_small  (cost=0.13..12.16 rows=2 width=4) (actual time=0.028..0.030 rows=2 loops=1)
         Heap Fetches: 2
 Planning Time: 0.256 ms
 Execution Time: 1237.700 ms
(9 rows)

Jika sublink tipe ANY dapat didorong ke dalam subquery, indeks di dalam subquery dapat digunakan untuk meningkatkan efisiensi eksekusi.

EXPLAIN ANALYZE SELECT * FROM (SELECT a, sum(b) b FROM t_big WHERE a IN (SELECT a FROM t_small) GROUP BY a)v;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=17.96..17.99 rows=2 width=12) (actual time=0.061..0.064 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.054..0.056 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.031..0.045 rows=2 loops=1)
               ->  Unique  (cost=1.03..1.04 rows=2 width=4) (actual time=0.014..0.017 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.005..0.006 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.010..0.011 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.527 ms
 Execution Time: 0.143 ms
(15 rows)

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 menanyakan versi revisi. Untuk memperbarui versi revisi, lihat Manajemen Versi Revisi.

Skenario

Fitur sublink pushdown cocok untuk subquery dengan pernyataan GROUP BY yang dirujuk oleh klausa IN atau ANY, terutama ketika subquery melibatkan tabel besar. Mendorong klausa IN atau ANY ke dalam subquery memungkinkan penggunaan indeks tabel besar, mengurangi jumlah akses data.

Batasan

Berikut adalah batasan yang berlaku untuk fitur sublink pushdown:

Klausa IN atau ANY harus merujuk subquery dengan pernyataan GROUP BY. Jika tidak, PostgreSQL sumber terbuka akan membuat jalur berparameter, sehingga fitur sublink pushdown tidak digunakan.
Kolom dalam klausa IN atau ANY harus termasuk dalam kolom dalam pernyataan GROUP BY. Jika tidak, pernyataan SQL setelah pushdown mungkin tidak setara dengan pernyataan SQL asli.
Blok kueri saat ini tidak boleh mengandung outer joins. Jika tidak, pernyataan SQL setelah pushdown tidak setara dengan pernyataan SQL asli.
Hanya satu kolom yang dapat dirujuk dalam klausa IN atau ANY, seperti a in (select a from t) atau a = any(select a from t).
Hanya pernyataan SELECT dan CREATE TABLE AS yang didukung.

Penggunaan

Anda dapat mengonfigurasi parameter berikut untuk mengontrol fitur sublink pushdown:

Parameter

Deskripsi

polar_cbqt_pushdown_sublink

Mengaktifkan atau menonaktifkan fitur sublink pushdown. Nilai valid:

OFF (default): menonaktifkan fitur sublink pushdown.
ON: mengaktifkan fitur sublink pushdown. Sublink didorong ke bawah berdasarkan CBQT.
FORCE: memaksa mengaktifkan fitur sublink pushdown. Sublink didorong ke bawah tanpa memedulikan CBQT. Gunakan nilai parameter ini dalam hint alih-alih pengaturan global.

Contoh

Persiapan Data

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;

Kueri Asli

Dalam rencana kueri asli, kondisi join t_big.a = t_small.a tidak dapat didorong ke bawah sebagai jalur berparameter. Pemindaian tabel penuh harus dilakukan pada tabel t_big, yang tidak efisien.

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..59510.27 rows=10000 width=12) (actual time=0.049..1239.128 rows=2 loops=1)
   Merge Cond: (t_big.a = t_small.a)
   ->  GroupAggregate  (cost=0.42..46909.23 rows=1000000 width=12) (actual time=0.034..1113.324 rows=1000000 loops=1)
         Group Key: t_big.a
         ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..31909.23 rows=1000000 width=8) (actual time=0.025..412.650 rows=1000000 loops=1)
   ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.012..0.013 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.219 ms
 Execution Time: 1239.208 ms
(11 rows)

Aktifkan sublink pushdown dengan mengaktifkan CBQT

Setelah fitur CBQT dan sublink pushdown diaktifkan, klausa a in (select a from t_small) didorong ke bawah ke subquery. Jalur berparameter dibuat untuk tabel t_big berdasarkan kondisi join, yang secara signifikan mengurangi jumlah data yang harus dipindai dan waktu eksekusi.

Catatan

Biaya rencana kueri asli harus melebihi nilai polar_cbqt_cost_threshold agar fitur sublink pushdown digunakan.

-- Aktifkan CBQT
SET polar_enable_cbqt to on;

-- Aktifkan sublink pushdown
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.056..0.059 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.051..0.052 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.045 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.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.007..0.008 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.010..0.010 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.518 ms
 Execution Time: 0.141 ms
(15 rows)

Aktifkan sublink pushdown dengan menggunakan hint

Anda dapat menggunakan hint untuk mengaktifkan fitur sublink pushdown untuk pernyataan SQL. Akibatnya, klausa a in (select a from t_small) didorong ke bawah ke subquery. Jalur berparameter dibuat untuk tabel t_big berdasarkan kondisi join, yang secara signifikan mengurangi jumlah data yang harus dipindai dan waktu eksekusi.

-- Nilai default parameter polar_cbqt_pushdown_sublink adalah off
SET polar_cbqt_pushdown_sublink to off;

EXPLAIN ANALYZE /*+ Set(polar_cbqt_pushdown_sublink force) */ 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.073..0.076 rows=2 loops=1)
   Group Key: t_big.a
   ->  Sort  (cost=17.96..17.96 rows=2 width=8) (actual time=0.067..0.069 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.026..0.040 rows=2 loops=1)
               ->  Unique  (cost=1.03..1.04 rows=2 width=4) (actual time=0.011..0.015 rows=2 loops=1)
                     ->  Sort  (cost=1.03..1.03 rows=2 width=4) (actual time=0.010..0.011 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)
               ->  Index Scan using t_big_a_idx on t_big  (cost=0.42..8.44 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=2)
                     Index Cond: (a = t_small.a)
 Planning Time: 0.788 ms
 Execution Time: 0.156 ms
(15 rows)