PostgreSQL查询优化器--逻辑查询优化--子查询优化（三）

 

示例2 子查询反嵌套（子查询展开）技术，PostgreSQL支持。

在FROM子句中的子查询，查询执行计划如下：

  test=# EXPLAIN SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 >10) v_t2 WHERE t1.a1<10 AND v_t2.a2<20;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..18.01 rows=90 width=16) //t1、t2做连接，没有把t2作为子查询
   ->  Index Scan using t2_a2_key on t2  (cost=0.00..8.45 rows=10 width=8) //扫描t2
         Index Cond: ((a2 > 10) AND (a2 < 20))
   ->  Materialize  (cost=0.00..8.45 rows=9 width=8)
         ->  Index Scan using t1_a1_key on t1  (cost=0.00..8.41 rows=9 width=8) //扫描t1
               Index Cond: (a1 < 10)
(6 行记录)

 从查询执行计划看，在表t2上的子查询没有单独执行，而是和表t1进行了嵌套循环连接，子查询被消除了，所以PostgreSQL支持子查询反嵌套技术，即我们所说的子查询上拉。
 
示例3 聚集子查询消除技术，PostgreSQL支持。
聚集子查询作为WHERE条件子句的一部分，查询执行计划如下：
 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），但子查询没有被消除。
表t2的a2列上存在唯一索引，根据索引，完全可以在查询执行计划形成阶段，对标量聚集子查询快速求值，这样原先的SQL就可以等价变形为“EXPLAIN SELECT * FROM t1 WHERE t1.a1>1;”，使得PostgreSQL更容易得到更好的查询执行计划。如下的查询执行计划比上一个查询执行计划事实上更优一些（结果从查询执行计划看，前一个花费更少，因为预计得到的元组数是3210而如下的查询执行计划预计得到的元组数是10000，但根据实际数据的情况，我们可以推知真实的结果中元组数是9999，所以应该是下面的查询执行计划估算更为接近真实花费）：
temp=# EXPLAIN SELECT * FROM t1 WHERE t1.a1>1;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on t1  (cost=0.00..170.00 rows=10000 width=8)
   Filter: (a1 > 1)
(2 行记录)
 
从查询执行计划看，可以通过顺序扫描完成查询，事实上更为快速。

把带有聚集操作的子查询作为IN子查询的操作数，查询执行计划如下：
  test=# EXPLAIN SELECT * FROM t1 WHERE t1.a1 IN (SELECT MIN(t2.a2) FROM t2);
                                            QUERY PLAN
--------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.03..8.34 rows=1 width=8)
   ->  Result  (cost=0.03..0.04 rows=1 width=0)
         InitPlan 1 (returns $0)
           ->  Limit  (cost=0.00..0.03 rows=1 width=4)
                 ->  Index Only Scan using t_idx_t2 on t2  (cost=0.00..343.25 rows=10000 width=4)
                       Index Cond: (a2 IS NOT NULL)
   ->  Index Scan using t_idx_t1 on t1  (cost=0.00..8.27 rows=1 width=8)
         Index Cond: (a1 = ($0))
(8 行记录)
 
从查询执行计划看，聚集子查询利用索引优化，并首先被执行，结果作为t1表的参数（$0）参与了嵌套循环连接（Nested Loop），子查询没有被消除。
另外，聚集子查询的结果如果能在查询执行计划形成阶段求值得到最小值1，则SQL语句可以变形为“EXPLAIN SELECT * FROM t1 WHERE t1.a1 IN (1);”，完全没有必要进行嵌套循环连接，这一点PostgreSQL多此一举。变形的SQL的查询执行计划如下：
test=# EXPLAIN SELECT * FROM t1 WHERE t1.a1=1;
                            QUERY PLAN
------------------------------------------------------------------
 Index Scan using t_idx_t1 on t1  (cost=0.00..8.27 rows=1 width=8)
   Index Cond: (a1 = 1)
(2 行记录) 
 
从查询执行计划看，可以利用索引扫描快速完成SQL的计算，更为简洁快速。
 
本文节选自《数据库查询优化器的艺术--原理解析与SQL性能优化》2014年1-2月出版