首页 文章

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

提问于
浏览 次
6

我有下表：

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 子句中添加运算符，还是这种语法完全有效？希望这不是那些超级简单的RTFM问题之一，将被投票30次 .

UPDATE:

我已经使用随机值向RecipeMetadata添加了10,000行 . 然后我在 table 上做了一个ANALYZE和一个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)'

所以无论如何，这个索引都没有被使用 .

sql postgresql postgresql-9.1

2 回答

  • 4

    我已经确认索引按预期工作 .

    我重新创建了随机数据，这次只将 diet_glutenfree 设置为 random() > 0.9 ，所以 on 位的概率只有10％ .

    然后我重新创建索引并再次尝试查询 .

    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估计扫描整个表格的速度更快，而不是如果必须加载超过一半的行，则不要点击索引 .

    但是，我想我会在列的完整索引上得到这些确切的结果 . 有没有办法验证部分索引中索引的行数？

    UPDATE

    该指数约为40k . 我创建了同一列的完整索引，它超过200k，所以它看起来肯定是偏的 .

    回复于
  • 1

    一位字段的索引没有意义 . 为了理解规划者做出的决策，您必须考虑页面而不是行 .

    对于8K页面和80个（存储的）行大小，每页有100行 . 假设一个随机分布，页面只包含 true 值的行的可能性是可忽略的， pow (0.5, 100) ，大约1e-33，IICC . （当然，对于'false'也是如此）因此，对于 gluten_free == true 上的查询，无论如何都必须提取每个页面，然后进行过滤 . 使用索引只会导致获取更多页面（：索引） .

    回复于

相关问题