Index in hstore with no predictable key

Question

Lets say I have a blog database where posts table stores tags in hstore.
Keys represent tag ids and values are tag names.

ex : 1=>'Test', 56=>'SQL', 42=>'Java'

I want to have optimized selects on posts with tag filter. How and what type of select should I create on tags column in order to optimize this query

SELECT * FROM posts WHERE tags?'4'

The issue is that I don't have predictable key names on order to create index based on their value.

Would it be a good solution to create gist index on column?
And how I can make it to treat key as an integer?

Answer 1

A GIN index is probably the better choice:

CREATE INDEX posts_tags_idx ON posts USING gin (tags);

That works for all keys.
You could further optimize the index if queries are only for a specific key with a partial index:

CREATE INDEX posts_tags_idx ON posts USING gin (tags -> '4')) WHERE x ? '4';

hstore ? text ... does hstore contain key?

But that doesn't seem to be an option for you.

hstore stores text only, not integer. Per documentation:

Keys and values are simply text strings.