请假各位大哥 pg 中一个相对比较简单的查询为什么效率这么低

[zhwguest]

其实就是一个主表，里面有两个指向明细表的索引。

create table pairs1(id serial primary key, ai int not null, bi int not null);

create table pairs2(id serial primary key, ai int not null, bi int not null);

create table items1(id serial primary key, name text not null);

create table items2(id serial primary key, name text not null);

create view pairs as select * from pairs1 union all select * from pairs2;

create view items as select * from items1 union all select * from items2;

create view viewpairs as select t0.id, t1.id as aid, t1.name as aname, t2.id as bid, t2.name as bname from pairs t0 left join items t1 on t0.ai=t1.id left join items t2 on t0.bi=t2.id;

$ explain select * from viewpairs;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Merge Right Join  (cost=5319.09..15347.88 rows=658063 width=76)
   Merge Cond: (items1_1.id = pairs1.bi)
   ->  Merge Append  (cost=0.32..151.82 rows=2540 width=36)
         Sort Key: items1_1.id
         ->  Index Scan using items1_pkey on items1 items1_1  (cost=0.15..63.20 rows=1270 width=36)
         ->  Index Scan using items2_pkey on items2 items2_1  (cost=0.15..63.20 rows=1270 width=36)
   ->  Sort  (cost=5318.77..5448.31 rows=51816 width=44)
         Sort Key: pairs1.bi
         ->  Merge Right Join  (cost=326.20..1261.29 rows=51816 width=44)
               Merge Cond: (items1.id = pairs1.ai)
               ->  Merge Append  (cost=0.32..151.82 rows=2540 width=36)
                     Sort Key: items1.id
                     ->  Index Scan using items1_pkey on items1  (cost=0.15..63.20 rows=1270 width=36)
                     ->  Index Scan using items2_pkey on items2  (cost=0.15..63.20 rows=1270 width=36)
               ->  Sort  (cost=325.88..336.08 rows=4080 width=12)
                     Sort Key: pairs1.ai
                     ->  Append  (cost=0.00..81.20 rows=4080 width=12)
                           ->  Seq Scan on pairs1  (cost=0.00..30.40 rows=2040 width=12)
                           ->  Seq Scan on pairs2  (cost=0.00..30.40 rows=2040 width=12)
(19 rows)

问了 ai ，提示在 pairs1 和 pairs2 中为 ai 和 bi 创建索引，但我总觉得有点不靠谱。

[xiaoxinshiwo]

-> Sort (cost=5318.77..5448.31 rows=51816 width=44)
Sort Key: pairs1.bi

[lysShub]

你这也不简单吧？这么多联表，视图套视图。。。

看 cost 有 sort, 把联表条件的列加上索引试试

[liprais]

join 了之后再 union all

[sagaxu]

这查询慢才符合预期，一个 where 条件都没有，如果你是 DB 引擎，你怎么优化？

[zbinlin]

你这都没 where

[kkwa56188]

union all 了以后, views 里的 id 就没有索引了.

[sthwrong]

查所有数据还用黑魔法吗？运气好可能比你 4 个表依次读出来程序组装还快点。