示例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性能优化》2014年1-2月出版从查询执行计划看,可以利用索引扫描快速完成SQL的计算,更为简洁快速。