示例2 在简单视图上进行聚集操作。
基于表t1和t2的视图v_t_1_2,进行聚集操作,查询执行计划如下:
test=# EXPLAIN SELECT * FROM t1, (SELECT * FROM t1, t2) t12 WHERE t1.a1<20;
QUERY PLAN
------------------------------------------------------------------
Nested Loop (cost=0.00..250291.15 rows=20000000 width=24)
-> Nested Loop (cost=0.00..273.65 rows=20000 width=16)
-> Seq Scan on t1 (cost=0.00..15.00 rows=1000 width=8)
-> Materialize (cost=0.00..8.70 rows=20 width=8)
-> Index Scan using t1_a1_key on t1 (cost=0.00..8.60 rows=20 width=8)
Index Cond: (a1 < 20)
-> Materialize (cost=0.00..20.00 rows=1000 width=8)
-> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
(8 行记录)
从查询执行计划看,视图v_t_1_2不存在,表t1和t2进行嵌套循环连接(Nested Loop)后执行了聚集操作(Aggregate),这表明视图被重写。
但是,在a1列上执行MAX操作,和连接没有关系,所以连接操作本可以消除。PostgreSQL对于这种情况有待改进。
如果在视图的基表上直接进行聚集操作,查询执行计划如下:
test=# EXPLAIN SELECT MAX(a1) FROM v_t_1_2;
QUERY PLAN
--------------------------------------------------------------------------
Aggregate (cost=1500315.00..1500315.01 rows=1 width=4)
-> Nested Loop (cost=0.00..1250315.00 rows=100000000 width=4)
-> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=4)
-> Materialize (cost=0.00..195.00 rows=10000 width=0)
-> Seq Scan on t2 (cost=0.00..145.00 rows=10000 width=0)
(5 行记录)
从查询执行计划看,表t1和t2进行嵌套循环连接(Nested Loop)后执行了聚集操作(Aggregate)。在a1列上执行MAX操作,和连接没有关系,连接操作是可以消除的,但PostgreSQL无论是对视图还是表,在本示例的情况下,都存在进一步优化的可能。
示例3 在简单视图上进行分组等操作。
直接用视图和表做连接操作,并执行分组操作,查询执行计划如下:
test=# EXPLAIN SELECT a1, a3 FROM t3, v_t_1_2 WHERE a1<20 GROUP BY a1, a3;
QUERY PLAN
--------------------------------------------------------------------------------------------
Group (cost=472433920.22..486683920.22 rows=190000 width=8)
-> Sort (cost=472433920.22..477183920.22 rows=1900000000 width=8)
Sort Key: t1.a1, t3.a3
-> Nested Loop (cost=0.00..23752698.63 rows=1900000000 width=8)
-> Nested Loop (cost=0.00..2528.63 rows=190000 width=8)
-> Seq Scan on t3 (cost=0.00..145.00 rows=10000 width=4)
-> Materialize (cost=0.00..8.68 rows=19 width=4)
-> Index Only Scan using t_idx_t1 on t1 (cost=0.00..8.58 rows=19 width=4)
Index Cond: (a1 < 20)
-> Materialize (cost=0.00..195.00 rows=10000 width=0)
-> Seq Scan on t2 (cost=0.00..145.00 rows=10000 width=0)
(11 行记录)
从查询执行计划看,视图v_t_1_2不存在,表t3和t1进行嵌套循环连接(Nested Loop)后再与t2进行嵌套循环连接,之后执行了排序操作(Sort)和分组操作(Group),这表明视图被重写。
直接用视图和表做连接操作,并执行分组和去重操作操作,查询执行计划如下:
test=# EXPLAIN SELECT DISTINCT a1, a3 FROM t3, v_t_1_2 WHERE a1<20 GROUP BY a1, a3;
QUERY PLAN
--------------------------------------------------------------------------------------------
Unique (cost=472433920.22..486684870.22 rows=190000 width=8)
-> Group (cost=472433920.22..486683920.22 rows=190000 width=8)
-> Sort (cost=472433920.22..477183920.22 rows=1900000000 width=8)
Sort Key: t1.a1, t3.a3
-> Nested Loop (cost=0.00..23752698.63 rows=1900000000 width=8)
-> Nested Loop (cost=0.00..2528.63 rows=190000 width=8)
-> Seq Scan on t3 (cost=0.00..145.00 rows=10000 width=4)
-> Materialize (cost=0.00..8.68 rows=19 width=4)
-> Index Only Scan using t_idx_t1 on t1 (cost=0.00..8.58 rows=19 width=4)
Index Cond: (a1 < 20)
-> Materialize (cost=0.00..195.00 rows=10000 width=0)
-> Seq Scan on t2 (cost=0.00..145.00 rows=10000 width=0)
(12 行记录)
从查询执行计划看,视图v_t_1_2不存在,表t3和t1进行嵌套循环连接(Nested Loop)后再与t2进行嵌套循环连接,之后执行了排序操作(Sort)、分组操作(Group)、去重操作(Unique),这表明视图被重写后又被作为子查询上拉了。