本文介紹了關聯子查詢上拉功能的背景及使用方法等內容。
前提條件
支援的PolarDB PostgreSQL版的版本如下:
PostgreSQL 14(核心小版本14.8.11.0及以上)。
您可通過如下語句查看PolarDB PostgreSQL版的核心小版本的版本號碼:
select version();
背景資訊
PostgreSQL最佳化器中使用SubLink
來表示運算式中出現的子查詢以及相關運算子的組合。SubLink
的類型如下:
EXISTS_SUBLINK
:用於實現EXISTS (SELECT ...)
子查詢。ALL_SUBLINK
:用於實現ALL (SELECT ...)
子查詢。ANY_SUBLINK
:用於實現ANY (SELECT ...)
子查詢以及IN (SELECT ...)
子查詢。
最佳化器通常會對帶有關聯查詢的ANY
/IN
/EXISTS
/NOT EXISTS
子查詢嘗試上拉,使其能夠與父查詢被共同最佳化為帶有半串連(Semi Join)或反串連(Anti Join)的執行計畫,從而提升查詢效能。其中,對於ANY_SUBLINK
,如果子查詢引用了上一級父查詢中的變數,將不會進行子查詢上拉,從而錯失了與父查詢進行共同最佳化的機會,子查詢只能夠作為一個獨立的個體被最佳化,導致SQL執行時間大大增加。
PolarDB PostgreSQL版可以通過參數控制ANY_SUBLINK
關聯子查詢上拉。對於帶有關聯查詢的IN
/ANY
子查詢,即使引用了上一級父查詢中的變數,也能夠進行子查詢上拉,從而增大最佳化器的搜尋空間,產生更好的執行計畫。
使用方法
polar_enable_pullup_with_lateral
參數用於是否開啟ANY_SUBLINK
關聯子查詢上拉功能,取值如下:
ON(預設):開啟
ANY_SUBLINK
關聯子查詢上拉。OFF:關閉
ANY_SUBLINK
關聯子查詢上拉。
樣本
準備資料。
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;
關閉關聯子查詢上拉功能後,執行計畫和時間。
=> 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)
開啟關聯子查詢上拉功能後,執行計畫和時間。
=> 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)
通過以上樣本,可以看到,子查詢上拉後,子查詢與父查詢被共同最佳化為一個半串連,位於子查詢中的過濾條件可以極大地過濾父查詢的結果,因此執行時間得到了非常明顯的縮短。如果子查詢沒有上拉,是無法過濾父查詢中要返回的行的。