在布尔字段上添加索引 [英] Adding an index on a boolean field

问题描述

我有一个Rails模型，其中包含一个要搜索的布尔字段（我使用一个范围查找该字段设置为true的所有实例）。我正在使用Postgres。

I have a Rails model with a boolean field that I search on (I use a scope that finds all instances where the field is set to true). I'm using Postgres.

我的直觉是在布尔字段上添加索引。是好的做法，还是Postgres中有一些东西不需要在布尔字段上建立索引？

My instinct is to add an index on the boolean field. Is that good practice, or is there something in Postgres that makes an index on a boolean field unnecessary?

推荐答案

不，您可以如果要对其进行过滤，则为布尔字段编制索引。这是完全合理的做法，尽管对于所有索引，PostgreSQL可能会选择忽略它，如果它不会排除足够多的表-索引扫描加上大量行读取操作可能比顺序扫描更昂贵- -可能会或不会影响您，具体取决于该列中的值。

No, you can index a boolean field if you'll be filtering by it. That's a perfectly reasonable thing to do, although as with all indexes, PostgreSQL may choose to ignore it if it won't exclude enough of the table -- an index scan plus a ton of row fetches may be more expensive than a sequential scan -- which may or may not affect you depending on the values in that column.

您还应该知道PostgreSQL允许您在索引上设置条件，我经常发现这很有用与布尔字段。 （有关详细信息，请参见部分索引。）如果通常要在其中进行过滤或排序在这种范围内，可能最好通过 CREATE INDEX ... ON table（some_field）WHERE boolean_field 这样的服务。

You should also be aware that PostgreSQL lets you put conditions on indexes, which I often find useful with boolean fields. (See Partial Indexes for details.) If you'll be commonly filtering or sorting within that scope, you might be best served by something like CREATE INDEX ... ON table (some_field) WHERE boolean_field.

这篇关于在布尔字段上添加索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！