Postgresql涉及复杂视图查询的优化案例
一、前言
对于含有union , group by 等的视图，我们称之为复杂视图。 这类的视图会影响优化器对于视图的提升，也就是视图无法与父查询进行合并，从而影响访问路径、连接方法、连接顺序等。本文通过例子，给大家展示PostgreSQL这类问题及针对该问题的优化方法。
二、Union 视图的优化
1、构建例子
create table t1(id1 integer);
insert into t1 select generate_series(1,10);
create table t2(id2 integer,name char(500));
insert into t2 select generate_series(1,1000000),repeat('a',400);
create index ind_t2 on t2(id2);
create table t3(id3 integer,name char(500));
insert into t3 select generate_series(1,1000000),repeat('a',400);
create index ind_t3 on t3(id3);
create or replace view v_t2_t3 as
select id2 as id from t2
union
select id3 as id from t3;
2、分析执行计划
执行计划如下：
testdb=# explain analyze select * from t1,v_t2_t3 where id1=id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=447340.31..483340.14 rows=99999 width=8) (actual time=1313.700..1313.711 rows=10 loops=1)
Merge Cond: (t1.id1 = t2.id2)
-> Sort (cost=1.27..1.29 rows=10 width=4) (actual time=0.019..0.021 rows=10 loops=1)
Sort Key: t1.id1
Sort Method: quicksort Memory: 25kB
-> Seq Scan on t1 (cost=0.00..1.10 rows=10 width=4) (actual time=0.009..0.011 rows=10 loops=1)
-> Unique (cost=447339.04..457338.98 rows=1999988 width=4) (actual time=1313.674..1313.681 rows=10 loops=1)
-> Sort (cost=447339.04..452339.01 rows=1999988 width=4) (actual time=1313.673..1313.676 rows=19 loops=1)
Sort Key: t2.id2
Sort Method: external merge Disk: 27488kB
-> Append (cost=0.00..183333.70 rows=1999988 width=4) (actual time=0.017..923.420 rows=2000000 loops=1)
-> Seq Scan on t2 (cost=0.00..76666.94 rows=999994 width=4) (actual time=0.016..547.533 rows=1000000 loops=1)
-> Seq Scan on t3 (cost=0.00..76666.94 rows=999994 width=4) (actual time=0.014..261.595 rows=1000000 loops=1)
Planning Time: 3.124 ms
Execution Time: 1316.691 ms
(15 rows)
问题分析：视图 v_t2_t3 并没有与 t1进行合并（Unique 节点），而是 t1 与视图 v_t2_t3 的结果进行连接。这个执行计划的问题在于 t1 表的数据量很少，如果能把 t1.id1 传入到视图，视图内部访问 t2 , t3 时就可以走索引，效率上要更高。
3、修改方式一
testdb=# explain analyze select * from t1,v_t2_t3 where id1=id and id=any(array(select id1 from t1));
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=98.95..100.08 rows=10 width=8) (actual time=0.126..0.129 rows=10 loops=1)
Hash Cond: (t1.id1 = t2.id2)
InitPlan 1 (returns $0)
-> Seq Scan on t1 t1_1 (cost=0.00..1.10 rows=10 width=4) (actual time=0.001..0.002 rows=10 loops=1)
-> Seq Scan on t1 (cost=0.00..1.10 rows=10 width=4) (actual time=0.005..0.006 rows=10 loops=1)
-> Hash (cost=97.60..97.60 rows=20 width=4) (actual time=0.115..0.116 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> HashAggregate (cost=97.20..97.40 rows=20 width=4) (actual time=0.112..0.114 rows=10 loops=1)
Group Key: t2.id2
-> Append (cost=0.42..97.15 rows=20 width=4) (actual time=0.060..0.105 rows=20 loops=1)
-> Index Only Scan using ind_t2 on t2 (cost=0.42..48.43 rows=10 width=4) (actual time=0.060..0.072 rows=10 loops=1)
Index Cond: (id2 = ANY ($0))
Heap Fetches: 10
-> Index Only Scan using ind_t3 on t3 (cost=0.42..48.43 rows=10 width=4) (actual time=0.022..0.032 rows=10 loops=1)
Index Cond: (id3 = ANY ($0))
Heap Fetches: 10
Planning Time: 0.171 ms
Execution Time: 0.163 ms
(18 rows)
分析：通过增加条件 id=any(array(select id1 from t1)) ， 可以看到该条件可以传入到视图内部。视图内部对于 t2 , t3 的访问是走索引的。
4、修改方式二
test=# explain analyze select * from t1,lateral(select * from v_t2_t3 where id1=id limit all); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=16.93..43202.70 rows=5080 width=8) (actual time=0.080..0.127 rows=10 loops=1)
-> Seq Scan on t1 (cost=0.00..35.40 rows=2540 width=4) (actual time=0.016..0.018 rows=10 loops=1)
-> Unique (cost=16.93..16.94 rows=2 width=4) (actual time=0.010..0.010 rows=1 loops=10)
-> Sort (cost=16.93..16.93 rows=2 width=4) (actual time=0.009..0.010 rows=2 loops=10) Sort Key: t2.id2 Sort Method: quicksort Memory: 25kB
-> Append (cost=0.42..16.92 rows=2 width=4) (actual time=0.005..0.008 rows=2 loops=10)
-> Index Only Scan using ind_t2 on t2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=10)
Index Cond: (id2 = t1.id1) Heap Fetches: 10
-> Index Only Scan using ind_t3 on t3 (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=10)
Index Cond: (id3 = t1.id1) Heap Fetches: 10
Planning Time: 0.199 ms
Execution Time: 6.820 ms (15 行记录)
通过lateral 语法，可以将t1 的值作为条件传入视图
5、问题分析结论
对于类似 v_t2_t3 这种含有 union 的复杂视图，除非是指定明确的值，如 v_t2_t3.id=xxx ， 才可以传入的视图内部。 而对于连接条件，如: id1=id，则需要修改语法。
