PostgreSQL查询优化器--逻辑查询优化--视图优化（二）

来源：互联网发布：linux 中文输入法安装编辑：程序博客网时间：2024/06/15 18:22

示例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），这表明视图被重写后又被作为子查询上拉了。

0 0