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

Question

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

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

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

Answer 1

我从来没有玩过 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);