PostgreSQL - 查询 HSTORE 值的 GIN 索引
我有以下构造函数（作为测试）：
CREATE TABLE product (id BIGSERIAL PRIMARY KEY, ext hstore);
CREATE INDEX ix_product_ext ON product USING GIN(ext);
INSERT
INTO product (id, ext)
SELECT id, ('size=>' || CEILING(10 + RANDOM() * 90) || ',mass=>' || CEILING(10 + RANDOM() * 90))::hstore
FROM generate_series(1, 100000) id;
我有以下查询，它工作正常：
SELECT COUNT(id)
FROM (
SELECT id
FROM product
WHERE (ext->'size')::INT >= 41
AND (ext->'mass')::INT <= 20
) T
但我相信执行此操作的正确方法是使用 @>;操作员。我有以下内容，但它给出了语法错误：
SELECT COUNT(id)
FROM (
SELECT id
FROM product
WHERE ext @> 'size>=41,mass<=20'
) T
我应该如何写这个？
I have the following constructor (as a test):
CREATE TABLE product (id BIGSERIAL PRIMARY KEY, ext hstore);
CREATE INDEX ix_product_ext ON product USING GIN(ext);
INSERT
INTO product (id, ext)
SELECT id, ('size=>' || CEILING(10 + RANDOM() * 90) || ',mass=>' || CEILING(10 + RANDOM() * 90))::hstore
FROM generate_series(1, 100000) id;
I have the following query, which works ok:
SELECT COUNT(id)
FROM (
SELECT id
FROM product
WHERE (ext->'size')::INT >= 41
AND (ext->'mass')::INT <= 20
) T
But I believe the correct way to do this is using the @> operator. I have the following, but it gives a syntax error:
SELECT COUNT(id)
FROM (
SELECT id
FROM product
WHERE ext @> 'size>=41,mass<=20'
) T
How should I write this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您最初的尝试是正确的，但您需要使用（部分）btree 索引和位图索引扫描来依赖它：
质量相同，如果规划器没有当场获得它，则添加两个 where 子句，即
其中 ext->'size' 不为 null并且质量相同。如果存在某种模式（这很可能，因为大多数具有尺寸的产品也有质量），可能会创建一个结合两者的多列索引 - 一个是囊，另一个是描述。
您编写的 gin 索引以及随附的查询（带有语法错误）基本上会执行相同的操作，但无序；会慢一些。
Your initial attempt is correct but you need to use (partial) btree indexes and bitmap index scans to rely on it:
The same for mass, and if the planner doesn't get it on the spot add two where clauses, ie
where ext->'size' is not nulland the same for mass.If there is a pattern of some kind (which is likely, since most products with a size also have a mass), potentially create a multicolumn index combining the two - one sac, the other desc.
The gin index as you wrote it, along with the accompanying query (with a syntax error) will basically do the same thing but unordered; it'll be slower.
阅读 hstore 文档（最后一个查询）
size>=41并不意味着“当size大于或等于41时”：后面不能写
mass<=20，因为没有这样的操作。使用@>运算符：您可以这样写：
但是，它仅需要尺寸等于 41 且质量等于 20 的这些产品。
Reading hstore documentation your (last query)
size>=41does not mean "when size is greater or equal than 41":Following that you can't write
mass<=20, because there is no such operation. Using@>operator:you can write:
However it takes only these products where size is equal to 41 and mass is equal to 20.