问题描述
假设我有一个博客数据库，其中posts表在hstore中存储标签。
键表示标签ID，值是标签名称。
Lets say I have a blog database where posts table stores tags in hstore.
Keys represent tag ids and values are tag names.
例如： 1 =>'测试'，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.
在列上创建gist索引是否是一个很好的解决方案？
我怎么能让它治疗键作为整数？
Would it be a good solution to create gist index on column?
And how I can make it to treat key as an integer?
推荐答案
GIN索引可能是更好的选择：
A GIN index is probably the better choice:
CREATE INDEX posts_tags_idx ON posts USING gin (tags);
适用于所有键。
你如果查询仅针对具有：
CREATE INDEX posts_tags_idx ON posts USING gin (tags -> '4')) WHERE x ? '4';
hstore ? text ... does hstore contain key?
但这似乎不适合您。
hstore 仅存储 text ，而不是整数。
hstore stores text only, not integer. Per documentation:
这篇关于hstore中的索引没有可预测的键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！