sql - 为什么 Postgresql 搜索 Text 索引比 Int 索引快？

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 行随机填充，“值”列具有从 0 到 100 的整数，“文本”列具有随机 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. 如果仅显示“文本”和/或“值”列的索引
2. 如果组合(“文本”和“值”一起)索引出现

...所以，每当我看到下面的图片时:

搜索整数列“值”是

• 较慢
• 结合了 2 个搜索:*Bitmap Heap Scan on index_test* 和 *Bitmap Index Scan on idx_index_value*

搜索 varchar 列 'text' 是

• 更快
• 始终使用索引扫描

为什么搜索 String 比搜索 Integer 更容易？为什么搜索计划会有这样的不同？有没有类似的情况可以重现这种效果，对开发者有帮助？

Answer 1

由于文本是一个散列，根据定义是唯一的，因此在表的 10k 行中将只有一行与该文本匹配。

56 值将在 10k 行中出现大约 100 次，并且会散布在整个表中。所以计划者首先进入索引并找到这些行所在的页面。然后它访问每个分散的页面以检索行。