示例4 在简单视图上执行外连接操作。
直接用视图和表做外连接操作,查询执行计划如下:
test=# EXPLAIN SELECT * FROM t3 LEFT JOIN v_t_1_2 V ON V.a1=t3.a3 WHERE V.a1<20;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=0.00..2657.97 rows=190000 width=24)
-> Seq Scan on t2 (cost=0.00..145.00 rows=10000 width=8)
-> Materialize (cost=0.00..138.02 rows=19 width=16)
-> Nested Loop (cost=0.00..137.93 rows=19 width=16)
-> Index Scan using t_idx_t1 on t1 (cost=0.00..8.58 rows=19 width=8)
Index Cond: (a1 < 20)
-> Index Scan using t_idx_t3 on t3 (cost=0.00..6.80 rows=1 width=8)
Index Cond: (a3 = t1.a1)
(8 行记录)
从查询执行计划看,视图v_t_1_2不存在,表t3和t1进行嵌套循环连接(Nested Loop)后再与t2进行嵌套循环连接,分拆了视图上的表t1和t2的连接,这表明视图被重写。
示例5 复杂视图的优化。
直接用复杂视图和表做连接操作,查询执行计划如下:
test=# EXPLAIN SELECT * FROM t3, v_t_gd_1_2 WHERE t3.a3<20;
QUERY PLAN
--------------------------------------------------------------------------------------------
Nested Loop (cost=21373964.88..47373973.51 rows=1900000000 width=16)
-> Unique (cost=21373964.88..22623964.88 rows=100000000 width=8)
-> Group (cost=21373964.88..22123964.88 rows=100000000 width=8)
-> Sort (cost=21373964.88..21623964.88 rows=100000000 width=8)
Sort Key: t1.b1, t2.b2
-> Nested Loop (cost=0.00..1250315.00 rows=100000000 width=8)
-> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=4)
-> Materialize (cost=0.00..195.00 rows=10000 width=4)
-> Seq Scan on t2 (cost=0.00..145.00 rows=10000 width=4)
-> Materialize (cost=0.00..8.68 rows=19 width=8)
-> Index Scan using t_idx_t3 on t3 (cost=0.00..8.58 rows=19 width=8)
Index Cond: (a3 < 20)
(12 行记录)
从查询执行计划看,视图v_t_gd_1_2不存在,表t1和t2先执行连接后完成分组与去重操作,这些,都是视图v_t_gd_1_2定义的操作,等到视图定义的操作执行完毕后,才与表t3的物化结果进行嵌套循环连接。这表明视图重写后没有被优化。
从以上示例看,PostgreSQL对视图优化的支持,限于简单视图,对复杂视图的支持不够。从代码的角度看,PostgreSQL没有把SPJ的优化和非SPJ的优化融合在一起考虑,而是明确分为几个阶段,先在subquery_planner函数中进行了SPJ的逻辑查询优化,然后在子函数grouping_planner中才考虑非SPJ的优化,这时SPJ的优化已经结束没有被进一步与非SPJ融合在一起得到考虑。而视图被重写后的优化走的就是先对SQL语句中的SPJ部分优化后对其中的非SPJ进行优化的过程,所以PostgreSQL对复杂视图重写后的优化支持不够。
本文节选自《数据库查询优化器的艺术--原理解析与SQL性能优化》2014年1-2月出版