PostgreSQL中位图索引的替代方案

Question

我有一个包含数亿行数据的表格，其模式如下所示。

tabe AA {
 id integer primay key,
 prop0 boolean not null,
 prop1 boolean not null,
 prop2 smallint not null,
 ...
}

"属性"字段（prop0，prop1等）每个都只有少数不同的值。我通常会根据这些属性字段的给定条件查询“id”。我认为位图索引最适合这种查询。但是PostgreSQL似乎不支持位图索引。

我尝试了在每个字段上使用B树索引，但是根据查询解释，这些索引未被使用。

是否有更好的替代方法来解决这个问题？

（我正在使用PostgreSQL 9）

- tk.

Answer 1

你真正的问题是错误的模式设计，而不是索引。应该将属性放在一个不同的表格中，并且你当前的表格应该使用多对多关系链接到那个表格。

BIT数据类型也可能会有用，只需要查看手册。

- Frank Heikens

---

所有属性彼此正交。所以如果我对N个属性进行归一化，那么就会有N个表。你的意思是我应该将N个属性分组为m个组，并创建m个表，这些表是组成员的排列组合，然后将AA表与这些m个表关联起来，以增加每个字段的基数？ - tk.

不，我的意思是你需要一个名为“properties”的表，“aa”和“properties_aa”。最后一张表仅保存属性和您的aa表之间的关系。这个表会很大，但可以索引。布尔值几乎不可能被索引，您只有3个选项：NULL、FALSE和TRUE。表“properties_aa”中的ID是更好的候选者。 - Frank Heikens

Answer 2

在经常或几乎总是查询的属性上创建多列索引。如果需要，可以创建多个多列索引。

另一种选择是，当您不经常查询相同的属性时，可以使用触发器维护描述数据的单词的tsvector列。

prop0=true
prop1=false
prop2=4

会是

'propzero nopropone proptwo4'::tsvector

使用GIN索引，然后使用全文搜索进行搜索：

where tsv @@ 'popzero & nopropone & proptwo4'::tsquery

- Tometzky

Answer 3

索引只有在实际加速查询时才会被使用，这并不总是情况。特别是对于小型表（比如数千行），全表扫描（在Postgres执行计划中称为“seq scan”）可能确实更快。

当您尝试该语句时，表中有多少行？查询看起来像什么？也许有其他条件阻止了索引的使用。您是否分析了表以获得最新的统计信息？

- user330315

---

正如我之前提到的，行数达到了数亿。我通常会执行“select id from AA where prop0 = true and prop1 = false and prop2 = 3 and ...”语句。我分析了表格以更新统计数据。 - tk.

你能发布执行计划吗？最好是EXPLAIN ANALYZE的输出？真/假值分布如何？如果选择的行数超过了所有行的约20％，则顺序扫描通常比索引查找更快。因此，如果prop0 = true产生了一半的所有行，则使用索引没有任何好处。如果您始终使用所有列，则对它们进行复合索引可能比每个属性一个索引更有意义。 - user330315