Practical Limitations of Expression Indexes in PostgreSQL

Question

I 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 indices? In my case, there may be several hundred different data types, and therefore several hundred expression indices. Each insert, update, and select request must process these indexes in order to select the correct one.

Answer 1

I have never played hstore, but I do something like this when I need an EAV column, for example:

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

The limitation here is that you need to be explicit in your request in order to use it, i.e. this request would not use the above index:

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

But this one thing:

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

In your example, it more likely looks like:

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

This should be avoided by adding a reference to the index in the absence of input size. Depending on the version of PG you are using, you may need to change it like this:

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

Another big difference between the regular and partial index is that the latter cannot provide a unique constraint on the definition of the table. This will be successful:

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

Below will not be:

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

But it will be:

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