Query with UNION or OR?

UNION_or_OR.md

CREATE TABLE public.friend (
   uid1 INTEGER,
   uid2 INTEGER
);
CREATE INDEX index_uid1 ON friend USING BTREE (uid1);
REATE INDEX index_uid2 ON friend USING BTREE (uid2);

表friend记录user1和user2是朋友关系，而且只会记录单方,(1,2)和(2,1)是一样的。

那么如何查询一个用户的好友呢

• OR

SELECT * from friend where uid1=1 OR uid2=1;//SELECT * from friend where 1 in (uid1,uid2);

• Union

SELECT * from friend where uid1=100 UNION select * from friend where uid2=100;

• Union All

SELECT * from friend where uid1=100 UNION ALL select * from friend where uid2=100;

这三个sql都满足要求，但是性能上有点不同。

我们使用explain analyze得到以下结果：

Bitmap Heap Scan on friend  (cost=8.87..16.77 rows=2 width=8) (actual time=0.021..0.023 rows=5 loops=1)
  Recheck Cond: ((uid1 = 100) OR (uid2 = 100))
  Heap Blocks: exact=2
  ->  BitmapOr  (cost=8.87..8.87 rows=2 width=0) (actual time=0.018..0.018 rows=0 loops=1)');
        ->  Bitmap Index Scan on index_uid1  (cost=0.00..4.43 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)
              Index Cond: (uid1 = 100)
        ->  Bitmap Index Scan on index_uid2  (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=4 loops=1)
              Index Cond: (uid2 = 100)
Planning time: 0.069 ms
Execution time: 0.042 ms

Unique  (cost=16.92..16.93 rows=2 width=8) (actual time=0.032..0.034 rows=5 loops=1)
  ->  Sort  (cost=16.92..16.92 rows=2 width=8) (actual time=0.032..0.033 rows=5 loops=1)
        Sort Key: friend.uid1, friend.uid2
        Sort Method: quicksort  Memory: 25kB
        ->  Append  (cost=0.42..16.91 rows=2 width=8) (actual time=0.015..0.023 rows=5 loops=1)
              ->  Index Scan using index_uid1 on friend  (cost=0.42..8.44 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)
                    Index Cond: (uid1 = 100)
              ->  Index Scan using index_uid2 on friend friend_1  (cost=0.42..8.44 rows=1 width=8) (actual time=0.006..0.008 rows=4 loops=1)'
                    Index Cond: (uid2 = 100)
Planning time: 0.109 ms
Execution time: 2.159 ms

Append  (cost=0.42..16.91 rows=2 width=8) (actual time=0.015..0.036 rows=5 loops=1)
  ->  Index Scan using index_uid1 on friend  (cost=0.42..8.44 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)
        Index Cond: (uid1 = 100)
  ->  Index Scan using index_uid2 on friend friend_1  (cost=0.42..8.44 rows=1 width=8) (actual time=0.007..0.012 rows=4 loops=1)
        Index Cond: (uid2 = 100)
Planning time: 0.121 ms
Execution time: 0.089 ms

可以看出or性能是最好的。其次union all，最差的是union。因为union需要去重。