使用IN的Postgres查询非常缓慢。

Question

我有一个表格，在列A和列B上都建立了索引。我运行的查询语句如下：

SELECT * FROM table WHERE (A, B) IN ((a_1, b_1), (a_2, b_2), ..., (a_5000, b_5000))

这个查询非常慢！执行计划如下：

Bitmap Heap Scan on table
  Recheck Cond: (((A = a_1) AND (B = b_1)) OR ((A = a_2) AND (B = b_2)) OR ...
  ->  BitmapOr
        ->  Bitmap Index Scan on idx
              Index Cond: ((A = a_1) AND (B = b_1))
        ->  Bitmap Index Scan on idx
              Index Cond: ((A = a_2) AND (B = b_2))
        ...(5000 other Bitmax Index Scan)

与其使用一次索引扫描处理5000个值，PostgreSQL似乎是一次只处理一个值的5000次索引扫描，这就解释了为什么查询如此缓慢。

实际上，做类似以下的操作会更快:

SELECT * FROM table WHERE A IN (a_1, ..., a_5000)

在应用程序中获取结果，然后按列B进行过滤（使用Python）。

我更希望Postgres已经以合理的运行时间过滤了结果。有没有解决方法？

- MG1992

Answer 1

尝试加入到一个公共表达式（CTE）中：

with value_list (a,b) as (
  values 
      (a_1, b_1), 
      (a_2, b_2), ..., 
      (a_5000, b_5000) 
)
select *
from table t
  join value_list v on (t.a, t.b) = (v.a, v.b);

（这假定您列表中没有重复的值）

- user330315

---

有趣啊。为什么会有所不同呢？ - zerkms

@zerkms：理论上（希望）这将欺骗优化器，使其执行单个索引扫描以获取所有值，而不是为每个值执行一次扫描。 - user330315

它运行了！规划器确实只使用了一个哈希连接。不过我觉得奇怪的是，规划器没有足够聪明地处理第一个查询。 - MG1992

@MehdiGMIRA：对于单列 IN 列表，优化器工作得很好。只是在这种情况下，它没有以最佳方式处理 - 但 Postgres 并不孤单。即使条件仅返回表的一小部分，Oracle 对于 (a,b) in (...) 不会考虑索引。在我的笔记本电脑上，有一个包含 700,000 行的表，Postgres 上的 IN 查询只需要 1.5 秒就能返回 1000 行。而 Oracle 需要 50 秒。因此，即使计划不完美，实际上也并不那么糟糕。 - user330315

@a_horse_with_no_name 非常厉害！ - zerkms