八月 14, 2024
摘要：在本教程中，您将学习“好的”和“坏的” OR，以及您可以采取哪些措施来避免后者。
目录
测试用例设置
我们将使用这个简单的设置进行演示：
CREATE TABLE a(id integer NOT NULL, a_val text NOT NULL);
INSERT INTO a
SELECT i, md5(i::text)
FROM generate_series(1, 100000) i;
CREATE TABLE b(id integer NOT NULL, b_val text NOT NULL);
INSERT INTO b
SELECT i, md5(i::text)
FROM generate_series(1, 100000) 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;
假设我们想在text列上运行带有等于和LIKE条件的查询，因此我们需要一些索引：
CREATE INDEX a_val_idx ON a(a_val text_pattern_ops);
CREATE INDEX b_val_idx ON b(b_val text_pattern_ops);
如果您不理解text_pattern_ops，请查看文档。
“好的” OR
在 SQL 查询的大多数部分，OR都很好：如果不使用它从查询结果中筛选出行，它不会对查询性能产生负面影响。
因此，如果您的OR出现在SELECT列表的CASE表达式中，请不要担心。
不幸的是，你通常会在它表现差的地方发现OR：在WHERE子句中。
“坏的” OR
现在来看一个在WHERE子句中带有OR的例子，它的表现还不错：
EXPLAIN (COSTS off)
SELECT id FROM a
WHERE id = 42
OR a_val = 'value 42';
QUERY PLAN
-----------------------------------------------------------
Bitmap Heap Scan on a
Recheck Cond: ((id = 42) OR (a_val = 'value 42'::text))
-> BitmapOr
-> Bitmap Index Scan on a_pkey
Index Cond: (id = 42)
-> Bitmap Index Scan on a_val_idx
Index Cond: (a_val = 'value 42'::text)
(7 rows)
PostgreSQL 实际上使用到了索引扫描进行查询，因为它可以用 “位图 OR” 将两个索引的位图组合起来。但请注意，位图索引扫描比普通索引扫描的成本更高，因为它必须生成位图。此外，它会使用更多的内存；这些位图中的每一个都可以用到work_mem数量的内存。
在(id, a_val)上的多列索引对此查询根本没有帮助，因此没有成本更低的方法来执行它。
IN 优于 OR
现在将上面查询转换一下，让它表现的更糟糕一些：
EXPLAIN (COSTS off)
SELECT id FROM a
WHERE id = 42
OR id = 4711;
QUERY PLAN
--------------------------------------------
Bitmap Heap Scan on a
Recheck Cond: ((id = 42) OR (id = 4711))
-> BitmapOr
-> Bitmap Index Scan on a_pkey
Index Cond: (id = 42)
-> Bitmap Index Scan on a_pkey
Index Cond: (id = 4711)
(7 rows)
同样使用了位图索引扫描。但是有一个简单的方法可以重写该查询，从而摆脱掉烦人的OR：
EXPLAIN (COSTS off)
SELECT id FROM a
WHERE id IN (42, 4711);
QUERY PLAN
---------------------------------------------------
Index Only Scan using a_pkey on a
Index Cond: (id = ANY ('{42,4711}'::integer[]))
(2 rows)
看到了吗？一旦你摆脱了OR，就可以使用高效的索引扫描！
你可能会说，这只对于等于条件有好处，但是下面的查询呢：
SELECT id FROM a
WHERE a_val LIKE 'something%'
OR a_val LIKE 'other%';
为了改进该查询，请注意 PostgreSQL 优化器将上一个查询中的IN重写为了= ANY。
这是 SQL 标准中“量化比较谓词”的情况：针对右侧的任何值，如果比较是TRUE，则ANY为 true（SQL 标准仅针对右侧的子查询定义了此规则，但 PostgreSQL 将语法扩展到了数组）。
现在的LIKE也是一个比较运算符，所以我们可以改写成：
EXPLAIN (COSTS off)
SELECT id FROM a
WHERE a_val LIKE ANY (ARRAY['something%', 'other%']);
QUERY PLAN
----------------------------------------------------------
Seq Scan on a
Filter: (a_val ~~ ANY ('{something%,other%}'::text[]))
(2 rows)
遗憾的是，此处无法使用到索引。
“糟糕的” OR
如果OR将来自不同表的条件组合在一起，情况就会变得非常糟糕：
EXPLAIN (COSTS off)
SELECT id, a.a_val, b.b_val
FROM a JOIN b USING (id)
WHERE a.id = 42
OR b.id = 42;
QUERY PLAN
---------------------------------------------
Merge Join
Merge Cond: (a.id = b.id)
Join Filter: ((a.id = 42) OR (b.id = 42))
-> Index Scan using a_pkey on a
-> Index Scan using b_pkey on b
(5 rows)
在这里，我们必须计算两个表之间的完全连接，然后过滤掉所有与条件匹配的行。在我们的示例中，这意味着计算 100000 行，只是为了丢弃不匹配条件的 99999 行。
避免糟糕的 OR
幸运的是，有一个等效的查询，其编写时间更长，但执行成本要低得多：
EXPLAIN (COSTS off)
SELECT id, a.a_val, b.b_val
FROM a JOIN b USING (id)
WHERE a.id = 42
UNION
SELECT id, a.a_val, b.b_val
FROM a JOIN b USING (id)
WHERE b.id = 42;
QUERY PLAN
----------------------------------------------------------
Unique
-> Sort
Sort Key: a.id, a.a_val, b.b_val
-> Append
-> Nested Loop
-> Index Scan using a_pkey on a
Index Cond: (id = 42)
-> Index Scan using b_pkey on b
Index Cond: (id = 42)
-> Nested Loop
-> Index Scan using a_pkey on a a_1
Index Cond: (id = 42)
-> Index Scan using b_pkey on b b_1
Index Cond: (id = 42)
(14 rows)
查询的两个部分都可以利用高效的索引扫描，只返回一行，并且由于行恰好相同，因此UNION会将它们减少到一行。
如果您可以确定查询的两个分支将返回不同的集合，则最好使用UNION ALL而不是UNION，因为这样不必进行额外的处理来删除重复项。
使用此技巧时，您应该注意，以这种方式重写查询，并不总是会产生等效的查询：如果原始查询可以返回相同的行，则这些行将被UNION删除。在我们的例子中，我们不必担心，因为在查询结果中包含了主键。