相关子查询与不相关子查询的优化（二）

 

四 PostgreSQL对相关子查询的优化

4.1 非相关子查询，多种优化情况

示例1：普通的非相关聚集子查询，被一次性地求解

test=# EXPLAIN SELECT * FROM t1 WHERE t1.a1>(SELECT MIN(t2.a2) FROM t2);
                                             QUERY PLAN
--------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=65.19..150.31 rows=3210 width=8)
   Recheck Cond: (a1 > $1)
   InitPlan 2 (returns $1) //非相关子查询的结果作为输入，帮助在t1表上完成过滤操作
     ->  Result  (cost=0.05..0.06 rows=1 width=0)
           InitPlan 1 (returns $0)
             ->  Limit  (cost=0.00..0.05 rows=1 width=4) //以只读索引扫描完成MIN操作
                   ->  Index Only Scan using t2_a2_key on t2  (cost=0.00..467.94 rows=9582 width=4)
                         Index Cond: (a2 IS NOT NULL)
   ->  Bitmap Index Scan on t1_a1_key  (cost=0.00..64.33 rows=3210 width=0)
         Index Cond: (a1 > $1)
(10 行记录)

从查询执行计划看，非相关的、聚集子查询利用索引优化，并首先被执行且只被执行了一次，结果作为t1表的参数（$1）参与了位图堆扫描（Bitmap Heap Scan），尽管子查询没有被消除，但是子查询被优化只被执行一次。

示例2： IN谓词表达的非相关子查询

IN非相关子查询，查询执行计划如下：

test=# EXPLAIN SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2>10);
                            QUERY PLAN
------------------------------------------------------------------
 Hash Join  (cost=27.50..58.61 rows=990 width=8)
   Hash Cond: (t2.a2 = t1.a1)
   ->  Seq Scan on t2  (cost=0.00..17.50 rows=990 width=4)
         Filter: (a2 > 10)
   ->  Hash  (cost=15.00..15.00 rows=1000 width=8)
         ->  Seq Scan on t1  (cost=0.00..15.00 rows=1000 width=8)
(6 行记录) 

从查询执行计划看，子查询不存在，表t1和t2直接做了哈希连接（Hash Join），把子查询上拉到父查询中用哈希连接实现IN非相关子查询的优化。

另外一个IN非相关子查询，查询执行计划如下：

 test=# EXPLAIN SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2=10);
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..16.55 rows=1 width=8)
   ->  Index Scan using t1_a1_key on t1  (cost=0.00..8.27 rows=1 width=8)
         Index Cond: (a1 = 10)
   ->  Index Only Scan using t2_a2_key on t2  (cost=0.00..8.27 rows=1 width=4)
         Index Cond: (a2 = 10)
(5 行记录)

从查询执行计划看，子查询不存在，表t1和t2直接做了嵌套循环半连接（Nested Loop Semi Join），把子查询上拉到父查询中用嵌套循环半连接实现IN操作。另外，由于子查询上拉，使得增加连接条件“a1=a2”，而原先的条件“a2=10”可以利用常量传递优化技术，使得“a1=a2=10”，所以查询执行计划中，两个索引扫描的条件分别为：a1 = 10、a2 = 10。

4.2 相关子查询，多种优化情况

再对比一个IN相关子查询，子查询没有别优化，查询执行计划如下：

 test=# EXPLAIN SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t1.a1=10);
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..8770.00 rows=500 width=8)
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Result  (cost=0.00..15.00 rows=1000 width=4)
           One-Time Filter: (t1.a1 = 10) //表t2的结果上执行的过滤和t2没有任何关系，多此一举
           ->  Seq Scan on t2  (cost=0.00..15.00 rows=1000 width=4)
(6 行记录)

从查询执行计划看，子查询存在（SubPlan 1），表t2上执行了顺序扫描后的结果作为了子查询（SubPlan 1）。 “a1=10”与t2表没有任何关联却被作为过滤器在子查询中使用，不合适。对于此种情况，PostgreSQL没有进行子查询的优化。另外，这个IN子查询如果把t2上拉，WHERE条件变为“a1=a2 AND t1.a1=10”，是完全可以支持子查询优化的。如果 t1.a1存在选择率较低的索引，则优化效果更好。

另外一个相关子查询的例子，子查询被优化：

test=# EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.b1= t2.b2 AND t1.a1=10);
                                QUERY PLAN
--------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..30.41 rows=1 width=8)
   Join Filter: (t1.b1 = t2.b2)
   ->  Index Scan using t1_a1_key on t1  (cost=0.00..8.27 rows=1 width=8)
         Index Cond: (a1 = 10)
   ->  Seq Scan on t2  (cost=0.00..15.00 rows=1000 width=4)
(5 行记录) 

从查询执行计划看，子查询不存在，用嵌套循环半连接（Nested Loop Semi Join）实现EXISTS操作，连接条件上有索引被利用。

从以上几个例子看，PostgreSQL对子查询的优化，并没有明确的规律区分是相关或非相关子查询。