怎么正确使用PostgreSQL中的OR

本篇内容介绍了"怎么正确使用PostgreSQL中的OR"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

在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 verboseSELECT id FROM aWHERE 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 verboseSELECT id FROM aWHERE 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_valFROM 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 verbosepg12@testdb-# SELECT id, a.a_val, b.b_valpg12@testdb-# FROM a JOIN b USING (id)pg12@testdb-# WHERE a.id = 42pg12@testdb-# UNIONpg12@testdb-# SELECT id, a.a_val, b.b_valpg12@testdb-# FROM a JOIN b USING (id)pg12@testdb-# WHERE b.id = 42pg12@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"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！