怎么正確使用PostgreSQL中的OR

170次閱讀

共計 4117 個字符，預計需要花費 11 分鐘才能閱讀完成。

本篇內容介紹了“怎么正確使用 PostgreSQL 中的 OR”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

在 SQL 語句中，對 OR 使用不當可能會導致較差的查詢效率。這并不意味著不能用 OR 而是在使用 OR 時需考慮可能存在的性能問題。
測試數據：

DROP TABLE a;
CREATE TABLE a(id integer NOT NULL, a_val text NOT NULL);
INSERT INTO a
 SELECT i, md5(i::text)
 FROM generate_series(1, 1000000) i;
DROP TABLE b; 
CREATE TABLE b(id integer NOT NULL, b_val text NOT NULL);
INSERT INTO b
 SELECT i, md5(i::text)
 FROM generate_series(1, 1000000) i;
ALTER TABLE a ADD PRIMARY KEY (id);
ALTER TABLE b ADD PRIMARY KEY (id);
ALTER TABLE b ADD FOREIGN KEY (id) REFERENCES a;
VACUUM (ANALYZE) a;
VACUUM (ANALYZE) b;

OR vs IN
條件語句 p1 OR p2，如可以考慮使用 IN 來改寫，比如：

[local:/data/pg12]:5432 pg12@testdb=# EXPLAIN verbose
SELECT id FROM a
WHERE id = 42
 OR id = 4711;
 QUERY PLAN 
---------------------------------------------------------------------------
 Bitmap Heap Scan on public.a (cost=8.87..16.80 rows=2 width=4)
 Output: id
 Recheck Cond: ((a.id = 42) OR (a.id = 4711))
 -  BitmapOr (cost=8.87..8.87 rows=2 width=0)
 -  Bitmap Index Scan on a_pkey (cost=0.00..4.43 rows=1 width=0)
 Index Cond: (a.id = 42)
 -  Bitmap Index Scan on a_pkey (cost=0.00..4.43 rows=1 width=0)
 Index Cond: (a.id = 4711)
(8 rows)
[local:/data/pg12]:5432 pg12@testdb=# 
[local:/data/pg12]:5432 pg12@testdb=# EXPLAIN verbose
SELECT id FROM a
WHERE id in (42,4711);
 QUERY PLAN 
----------------------------------------------------------------------------
 Index Only Scan using a_pkey on public.a (cost=0.42..8.88 rows=2 width=4)
 Output: id
 Index Cond: (a.id = ANY ( {42,4711} ::integer[]))
(3 rows)
[local:/data/pg12]:5432 pg12@testdb=#

使用 OR 操作符，PG 優化器走的是 Bitmap Index Scan，使用 IN，優化器選擇的路徑是 Index Only Scan，相對于 Bitmap Index Scan 少了 Bitmap 的建立，成本自然要低不少。

OR and Join
在 Join 場景中，如果在參與 join 的表上都存在查詢條件然后在 where 子句中應用 OR 關聯，那么優化器會選擇 a 和 b 連接然后使用 Filter 過濾，由于先進行 join 而沒有進行謂詞下推，因此為了得到 1 行而 filter 了 999999 行，代價巨大。

[local:/data/pg12]:5432 pg12@testdb=# EXPLAIN verbose 
SELECT id, a.a_val, b.b_val
FROM a JOIN b USING (id)
WHERE a.id = 42
 OR b.id = 42;
 QUERY PLAN 
---------------------------------------------------------------------------------------------
 Gather (cost=21965.00..45327.62 rows=2 width=70)
 Output: a.id, a.a_val, b.b_val
 Workers Planned: 2
 -  Parallel Hash Join (cost=20965.00..44327.42 rows=1 width=70)
 Output: a.id, a.a_val, b.b_val
 Inner Unique: true
 Hash Cond: (a.id = b.id)
 Join Filter: ((a.id = 42) OR (b.id = 42))
 -  Parallel Seq Scan on public.a (cost=0.00..12500.67 rows=416667 width=37)
 Output: a.id, a.a_val
 -  Parallel Hash (cost=12500.67..12500.67 rows=416667 width=37)
 Output: b.b_val, b.id
 -  Parallel Seq Scan on public.b (cost=0.00..12500.67 rows=416667 width=37)
 Output: b.b_val, b.id
(14 rows)

在這種情況下，可以通過使用 UNION 來關聯兩個 JOIN 提升性能

[local:/data/pg12]:5432 pg12@testdb=# EXPLAIN verbose
pg12@testdb-# SELECT id, a.a_val, b.b_val
pg12@testdb-# FROM a JOIN b USING (id)
pg12@testdb-# WHERE a.id = 42
pg12@testdb-# UNION
pg12@testdb-# SELECT id, a.a_val, b.b_val
pg12@testdb-# FROM a JOIN b USING (id)
pg12@testdb-# WHERE b.id = 42
pg12@testdb-# ;
 QUERY PLAN 
----------------------------------------------------------------------------------------------------
 Unique (cost=33.83..33.85 rows=2 width=68)
 Output: a.id, a.a_val, b.b_val
 -  Sort (cost=33.83..33.84 rows=2 width=68)
 Output: a.id, a.a_val, b.b_val
 Sort Key: a.id, a.a_val, b.b_val
 -  Append (cost=0.85..33.82 rows=2 width=68)
 -  Nested Loop (cost=0.85..16.90 rows=1 width=70)
 Output: a.id, a.a_val, b.b_val
 -  Index Scan using a_pkey on public.a (cost=0.42..8.44 rows=1 width=37)
 Output: a.id, a.a_val
 Index Cond: (a.id = 42)
 -  Index Scan using b_pkey on public.b (cost=0.42..8.44 rows=1 width=37)
 Output: b.id, b.b_val
 Index Cond: (b.id = 42)
 -  Nested Loop (cost=0.85..16.90 rows=1 width=70)
 Output: a_1.id, a_1.a_val, b_1.b_val
 -  Index Scan using a_pkey on public.a a_1 (cost=0.42..8.44 rows=1 width=37)
 Output: a_1.id, a_1.a_val
 Index Cond: (a_1.id = 42)
 -  Index Scan using b_pkey on public.b b_1 (cost=0.42..8.44 rows=1 width=37)
 Output: b_1.id, b_1.b_val
 Index Cond: (b_1.id = 42)
(22 rows)
[local:/data/pg12]:5432 pg12@testdb=#

兩個子連接選擇了成本最低的 NL join，總成本是原來 SQL 語句成本的 0.1% 都不到，差了 3 個數量級。

“怎么正確使用 PostgreSQL 中的 OR”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！

正文完