Postgres-这是在布尔列上创建部分索引的正确方法吗？

仲孙磊

2023-03-14

问题内容：

我有下表：

CREATE TABLE recipemetadata
(
  --Lots of columns
  diet_glutenfree boolean NOT NULL,
);

FALSE除非有人提出一些疯狂的新无麸质饮食来席卷全国，否则大多数情况下，每一行都将被设置为。

我需要能够非常快速地查询该值为true的行。我创建了索引：

CREATE INDEX IDX_RecipeMetadata_GlutenFree ON RecipeMetadata(diet_glutenfree) WHERE diet_glutenfree;

它似乎可以工作，但是我不知道如何确定是否仅索引值为true的行。我想确保它不会像在索引任何具有任何值的行时那样愚蠢。

我应该在WHERE子句中添加运算符，还是此语法完全有效？希望这不是那些将被否决30次的超级简单的RTFM问题之一。

更新：

我继续进行，并向RecipeMetadata添加了10,000行具有随机值的行。然后我在桌子上做了一个分析，然后做了一个REINDEX来确定。当我运行查询时：

select recipeid from RecipeMetadata where diet_glutenfree;

我得到：

'Seq Scan on recipemetadata  (cost=0.00..214.26 rows=5010 width=16)'
'  Filter: diet_glutenfree'

因此，即使只有大约一半的行具有此标志，它似乎仍在对表进行顺序扫描。索引被忽略。

如果我做：

select recipeid from RecipeMetadata where not diet_glutenfree;

我得到：

'Seq Scan on recipemetadata  (cost=0.00..214.26 rows=5016 width=16)'
'  Filter: (NOT diet_glutenfree)'

因此，无论如何，都不会使用该索引。

问题答案：

我已经确认索引可以正常工作。

我重新创建了随机数据，只是这次设置为零diet_glutenfree，random() > 0.9所以只有10％的几率on。

然后，我重新创建了索引，然后再次尝试查询。

SELECT RecipeId from RecipeMetadata where diet_glutenfree;

返回值：

'Index Scan using idx_recipemetadata_glutenfree on recipemetadata  (cost=0.00..135.15 rows=1030 width=16)'
'  Index Cond: (diet_glutenfree = true)'

和：

SELECT RecipeId from RecipeMetadata where NOT diet_glutenfree;

返回值：

'Seq Scan on recipemetadata  (cost=0.00..214.26 rows=8996 width=16)'
'  Filter: (NOT diet_glutenfree)'

似乎我的第一次尝试受到了污染，因为PG估计，如果无论如何它都要加载一半以上的行，则扫描整个表比击中索引要快。

但是，我想我会在该列的完整索引上得到这些确切的结果。有没有一种方法可以验证在部分索引中建立索引的行数？

更新

该指数约为40k。我创建了同一列的完整索引，它的索引超过200k，因此看起来绝对是部分索引。