SELECT COUNT查询索引列

Question

我有一个简单的表，用于存储不同id的单词。

CREATE TABLE words (
id INTEGER,
word TEXT,
);
CREATE INDEX ON words USING hash (id);
CREATE INDEX ON words USING hash (word);

现在我只想计算给定单词出现的次数。我的实际查询有点不同，涉及到其他过滤器。

SELECT COUNT(1) FROM "words"
WHERE word = 'car'

我的表有十亿行，但是这个特定查询的答案是大约45k。我希望单词上的索引可以使查询超级快，但它仍然需要1分20秒才能执行，这看起来很不合理。相比之下，SELECT COUNT(1) FROM "words"需要1 min 57.

下面是EXPLAIN的输出:

Aggregate  (cost=48667.00..48667.01 rows=1 width=8)
->  Bitmap Heap Scan on words  (cost=398.12..48634.05 rows=13177 width=0)
Recheck Cond: (word = 'car'::text)
->  Bitmap Index Scan on words_word_idx  (cost=0.00..394.83 rows=13177 width=0)
Index Cond: (word = 'car'::text)

我不明白为什么需要重新检查条件，为什么这个查询没有效率。

Answer 1

散列索引不将索引值存储在索引中，只存储它的32位散列和ctid(指向表行的指针)。这意味着它们不能自己解决哈希冲突，所以它必须到表中获取值，然后重新检查它。与b树索引相比，这可能涉及更多或额外的IO, b树索引确实存储值，并且可以支持仅索引扫描。

Answer 2

只要使用以下操作符之一进行比较，就可以使用B-Tree索引:

<   <=   =   >=   >

我假设您使用=来计算有多少单词。因此，B-Tree索引满足您的要求。

参考:https://www.postgresql.org/docs/current/indexes-types.html INDEXES-TYPES-BTREE