CODEWITHSUNDEEP
postgresqlindexinghstore
Boris Barroso
Boris Barroso

Reputation: 1812

PostgreSQL indexes for hstore boolean attributes

I have an hstore column called extras and I have defined there many attributes some of them are boolean and I would like to index some of them for example extras->'delivered' in this case which would be the best way to index some of these attributes.

If you answer could you tell me if your technique applies for decimal or other types.

Thanx.

Upvotes: 2

Views: 2987

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324395

Indexing individual keys in a hstore field

The current hstore version doesn't have typed values. All values are text. So you can't directly define a "boolean" index on an hstore value. You can, however, cast the value to boolean and index the cast expression.

CREATE INDEX sometable_extras_delivered_bool 
ON sometable ( ((extras->'delivered')::boolean) );

Only queries that use the expression (extras->'delivered')::boolean) will benefit from the index. If the index expression uses a cast, the query expressions must too.

This b-tree index on a hstore field will be less efficient to create and maintain than a b-tree index of a boolean col directly in the table. It'll be much the same to query.

Indexing all keys in a hstore field

If you want a general purpose index that indexes all hstore keys, you can only index them as text. There's no support for value typing in hstore in PostgreSQL 9.3. See indexes on hstore.

This is useful when you don't know in advance which keys you need to index.

(Users on later, pre-release at time of writing versions of PostgreSQL with the json-compatible hstore version 2 will find that their hstore supports typed values).

Reconsider your data model

Frankly, if you're creating indexes on fields in a hstore that you treat as boolean, then consider re-thinking your data model. You are quite likely better off having this boolean as a normal field of the table that contains the hstore.

You can store typed values in json, but you don't get the GIN / GiST index support that's available for hstore. This will improve in 9.4 or 9.5, with hstore 2 adding support for typed, nested, indexable hstores and a new json representation being built on top of that.

Partial indexes

For booleans you may also want to consider partial index expressions where the boolean is a predicate on another index, instead of the actual indexed column. E.g:

CREATE INDEX sometable_ids_delivered ON sometable(id) WHERE (delivered);

or, for the hstore field:

CREATE INDEX sometable_ids_delivered ON sometable(id) WHERE ((extras->'delivered')::boolean);

Exactly what's best depends on your queries.

Upvotes: 6

Related Questions