为什么PostgreSQL在搜索文本索引时比整数索引更快？

Question

CREATE TABLE index_test
(
  id int PRIMARY KEY NOT NULL,
  text varchar(2048) NOT NULL,
  value int NOT NULL
);
CREATE INDEX idx_index_value ON index_test ( value );
CREATE INDEX idx_index_value_and_text ON index_test ( value, text );
CREATE INDEX idx_index_text_and_value ON index_test ( text, value );
CREATE INDEX idx_index_text ON index_test ( text );

表中包含10000个随机行，'value'列的整数取值范围为0到100，'text'列的取值为128位的随机MD5哈希值。抱歉使用了不恰当的列名。

我的搜索如下:

select * from index_test r where r.value=56;
select * from index_test r where r.value=56 and r.text='dfs';
select * from index_test r where r.text='sdf';

每当我进行一些搜索时...

1. 如果只有“text”和/或“value”列上的索引
2. 如果结合了（“text”和“value”在一起）索引

... 所以，每当我看到以下图片：

整数列“value”的搜索是

• 较慢
• 由两个搜索组合而成：* Bitmap Heap Scan on index_test * 和 * Bitmap Index Scan on idx_index_value *

字符列“text”的搜索是

• 更快
• 总是使用索引扫描

为什么搜索字符串比搜索整数更容易？ 为什么搜索计划会以这种方式不同？ 是否存在类似的情况，可以重现此效果并对开发人员有所帮助？

- KutaBeach

Answer 1

作为文本哈希，它本身就是唯一的，因此表格中的10k行中只会有一个匹配该文本的行。

56这个值将在10k行中大约出现100次，并且它将分散在整个表格中。因此，计划程序首先访问索引，找到包含这些行的页面。然后，它访问每个分散的页面以检索这些行。

- Clodoaldo Neto

---

仅仅因为哈希是唯一的并不意味着这个哈希值会是数据库中唯一的。例如，哈希的一个用途可能是检查在您的表中可以表示为重复、复杂的事物。这就否定了您的论点（这甚至不是文本索引比数字索引更快的原因之一）。 - user2611793

@MikeBethany 你没有仔细阅读问题或者没有理解它。再努力一下，然后搜索“基数”。 - Clodoaldo Neto

我更感兴趣的是学习我的错误，以便提高自己。让我们一起努力找出正确的答案。 - user2611793

@MikeBethany 我的工作已经在这个答案中完成了。现在轮到你了。你需要通过构建一个与问题联系的答案来测试你的评论。 - Clodoaldo Neto

@ClodoaldoNeto - 调用随机的128位哈希并不能保证它们的唯一性。事实上，哈希值是不唯一的，根据定义，因为恰好有2 ** 128个哈希值。作者没有说他们经过筛选以确保所有10,000个哈希值都是唯一的。虽然在128位中的10,000个条目中发生碰撞的可能性很小，但这绝不是不可能的，那么你为什么认为它们是明确和定义上唯一的呢？ - dwanderson