PostgreSQL中表达式索引的实际限制 [英] Practical limitations of expression indexes in PostgreSQL

问题描述

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

I have a need to store data using the HSTORE type and index by key.

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

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

What are the practical limitations of using expression indexes? In my case, there could be several hundred different types of data, hence several hundred expression indexes. Every insert, update, and select query will have to process against these indexes in order to pick the correct one.

推荐答案

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

I've never played with hstore, but I do something similar when I need an EAV column, e.g.:

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

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

The limitation in doing so is that you need to be explicit in your query to make use of it, i.e. this query would not make use of the above index:

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';

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

In your example it should likely be more like:

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

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

This should avoid adding a reference to the index when there is no size entry. Depending on the PG version you're using the query may need to be modified like so:

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

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

Another big difference between regular and partial index is that the latter cannot enforce a unique constraint in a table definition. This will succeed:

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);

这篇关于PostgreSQL中表达式索引的实际限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！