让我们假设一个translation
表:
柱子 | 类型 |
---|---|
? 身份证 | 大整数 |
钥匙 | 文本 |
语言环境 | 变量(10) |
价值 | 文本 |
在 上具有唯一索引(key, locale)
。
我有一个批量端点,我想在其中获得多个键/区域设置组合,例如
{
"key": "foo",
"locales": ["a", "b", "c"]
},
{
"key": "bar",
"locales": ["c", "d", "e"]
},
我目前正在为每个请求项构建一个查询,例如
SELECT *
FROM translation
WHERE (key = 'foo' AND locale in ('a', 'b', 'c'))
OR (key = 'bar' AND locale in ('c', 'd', 'e'));
当请求大小增加时(最大限制为 500 个项目),您可以想象有很多 OR 语句。
我想知道这是否不是查询性能的好方法
鉴于键的基数高于区域设置(总计约 50),我是否应该以其他方式分组以可能减少 OR 条件(在这个特定示例中我会得到 5 个语句而不是 2 个),例如
WHERE (locale = 'a' and key in ('foo')) OR ...
如果我无法避免 OR 语句,这将是一种更好的方法。我可以进一步分析请求并找到模式,例如,通常请求会重复相同的语言环境。
仅过滤键(从查询中省略语言环境)并过滤应用程序层是否更好,这意味着从数据存储返回更多数据?例如
WHERE key in ('foo', 'bar')
编辑 1:
我EXPLAIN (ANALYZE, BUFFERS)
按照@jjanes 的要求做了一个。结果令人鼓舞。我不得不修剪输出,因为它不适合这里。
Bitmap Heap Scan on translation (cost=2162.38..2572.98 rows=129 width=91) (actual time=1.840..2.018 rows=500 loops=1)
Recheck Cond: >>...(repeated my query)<<
Heap Blocks: exact=28
Buffers: shared hit=1028
-> BitmapOr (cost=2162.38..2162.38 rows=131 width=0) (actual time=1.827..1.922 rows=0 loops=1)
Buffers: shared hit=1000
-> Bitmap Index Scan on unique_translation (cost=0.00..4.29 rows=1 width=0) (actual time=0.038..0.038 rows=1 loops=1)
Index Cond: ((key = 'd4f325a3-81ed-4bcc-a387-1dbb34f17896'::text) AND ((locale)::text = 'es-CL'::text))
Buffers: shared hit=2
>>... 499 more of the above<<
Planning Time: 3.229 ms
Execution Time: 6.752 ms
最好的解决方案可能是完全避免
OR
,如如果条件比较有选择性,可以对唯一约束后面的索引使用索引扫描。如果没有,它可能会执行顺序扫描,但性能不会比您的查询差。
当您发现这样的查询强制进行全索引扫描,或者更糟的是全表扫描时,通常多个查询
UNION
ed 可以执行得更好:通过这种方式,您通常会为每个
SELECT
s 获得一个更有效的计划,在这种情况下,一个简单的索引查找,(key, locale)
然后查找*
需要返回的其他值,然后组合每个匹配项的所有数据相对较快。当预期每个结果集返回少量行时,此方法特别有效。
如果查询更复杂,它的缺点是会更冗长,因为您必须在每个
SELECT
. 这可以通过将主查询FROM
变成JOIN
CTE 并从中查询其余部分来解决:注意 1:CTE 语法是 SQL Server,我的主要数据库出没地,您可能需要为 postgres 更改它。
注 2:如果您需要支持旧安装的 postgres,请注意 CTE,直到几个版本之前,它们是一个优化栅栏,阻止谓词下推,可能会使情况变得不那么理想。在那种情况下,您需要坚持使用更冗长的版本。
对于像这样的简单查询,可以对所有条件使用相同的复合索引,Laurenz 的答案会更有效。联合方法更普遍适用,适用于
IN
不支持在多列上使用的数据库。