postgresql - 为什么 PostgreSQL 不在 "WHERE NOT IN"条件下使用索引。

Question

我有两个表 db100 和 db60，它们具有相同的字段:x、y、z。像这样为字段 z 上的两个表创建索引:

CREATE INDEX db100_z_idx
  ON db100
  USING btree
  (z COLLATE pg_catalog."default");
CREATE INDEX db60_z_idx
  ON db60
  USING btree
  (z COLLATE pg_catalog."default");

试图从 db60 中查找 db100 中不存在的 z 值:

select db60.z from db60 where db60.z not in (select db100.z from db100)

据我所知，执行查询所需的所有信息都显示在索引中。所以，我希望只使用索引。但是，它改为对表使用顺序扫描:

"Seq Scan on db60  (cost=0.00..25951290012.84 rows=291282 width=4)"
"  Filter: (NOT (SubPlan 1))"
"  SubPlan 1"
"    ->  Materialize  (cost=0.00..80786.26 rows=3322884 width=4)"
"          ->  Seq Scan on db100  (cost=0.00..51190.84 rows=3322884 width=4)"

有人能解释一下为什么 PostgreSQL 在这个例子中不使用索引吗？

这两个表都包含几百万条记录，执行需要一段时间。

我知道在“为空”条件下使用左连接会产生更好的结果。但是，问题是关于这个特定的语法。

我正在使用 PG v 9.5

Answer 1

子计划 1 用于从 db100 中选择 db100.z。您选择了所有 行，因此索引是无用的。您真的想在此处从 db100 中选择 DISTINCT z，然后应该使用索引。

在主查询中，您有 select db60.z from db60 where db60.z not in ...。同样，您选择了所有 行，但条件不 为真时除外，因此索引再次不适用，因为它适用于相反的条件。

一般来说，只有在规划者认为这样的使用会加速查询处理时才使用索引。它始终取决于有多少不同的值以及行如何分布在磁盘上的物理页面上。搜索具有特定值列的所有行的索引与查找不具有相同值的行不同；索引指示在哪些页面和哪些位置找到行，但不能简单地反转该集合。

鉴于 - 在您的情况下 - z 是某种 text 类型，无法构建有意义的“负”索引(这实际上几乎是一种真实主义，尽管在某些情况下可能会出现“负”指数)。你应该看看trigram indexes ，因为它们在文本索引方面往往比 btree 快得多。

您真的想提取具有相同 z 值的所有 291,282 行，或者在这里也可以使用 DISTINCT 子句？这应该会大大加快速度。