PostgreSQL 中表达式索引的实际限制

我需要使用 HSTORE 类型和键索引来存储数据。

CREATE INDEX ix_product_size ON product(((data->'Size')::INT))
CREATE INDEX ix_product_color ON product(((data->'Color')))
etc.

使用表达式索引有哪些实际限制？就我而言，可能有数百种不同类型的数据，因此有数百种表达式索引。每个插入、更新和选择查询都必须针对这些索引进行处理，以便选择正确的索引。

我从来没有玩过 hstore，但当我需要 EAV 列时我会做类似的事情，例如：

create index on product_eav (eav_value) where (eav_type = 'int');

这样做的限制是您需要在查询中明确地使用它，即该查询不会使用上述索引：

select product_id
from product_eav
where eav_name = 'size'
and eav_value = :size;

但这一个会：

select product_id
from product_eav
where eav_name = 'size'
and eav_value = :size
and type = 'int';

在您的示例中，它可能应该更像是：

create index on product ((data->'size')::int) where (data->'size' is not null);

这应该避免在没有大小条目时添加对索引的引用。根据您使用的 PG 版本，查询可能需要进行如下修改：

select product_id
from products
where data->'size' is not null
and data->'size' = :size;

常规索引和部分索引之间的另一个大区别是后者不能在表定义中强制执行唯一约束。这将成功：

create unique index foo_bar_key on foo (bar) where (cond);

以下情况不会：

alter table foo add constraint foo_bar_key unique (bar) where (cond);

但这会：

alter table foo add constraint foo_bar_excl exclude (bar with =) where (cond);