Would someone provide a bit of insight into how I might index all keys of an hstore?
Example: I want to retrieve all entries from a table which contain a specific key:
SELECT * FROM my_table WHERE hstore_col ? 'MyKey'
The problem is that I need all keys to be indexed, not just a particular one. There are currently 300+ unique keys present, so creating an index for each and every key is not an option. Therefore, the following index declaration falls short, and this is where the challenge arises:
CREATE INDEX "my_index" ON table _col ((hstore_col->'MyKey'))
You should be able to get by with a gist or gin index on the column itself versus an index on every key, as both those index types have support for the ? operator.
For example:
create index idx_my_table_gist_hstore_col
on my_table using gist (hstore_col)
;