PostgreSQL-查询HSTORE值的GIN索引 [英] PostgreSQL - query against GIN index of HSTORE value
问题描述
我有以下构造函数(作为测试):
CREATE TABLE产品(id BIGSERIAL PRIMARY KEY,ext hstore) ;
创建索引ix_product_ext使用GIN(ext)打开产品;
插入
INTO产品(id,ext)
SELECT id,('size =>'|| CEILING(10 + RANDOM()* 90)||', mass =>'|| CEILING(10 + RANDOM()* 90)):: hstore
FROM generate_series(1,100000)id;
我有以下查询,可以正常运行:
选择COUNT(id)
FROM(
SELECT id
FROM产品
WHERE(ext->'size') :: INT> = 41
AND(ext->'mass'):: INT< = 20
)T
但是我相信正确的方法是使用@>运算符。我有以下内容,但是它给出了语法错误:
SELECT COUNT(id)
FROM(
从产品
中选择id
WHERE ext @>'size> = 41,mass< = 20'
)T
我应该怎么写?
阅读 hstore 记录您(最后一次查询) size> = 41
并不表示大小大于或等于41:
text =>文本使单对hstore
以下是您不能写 mass< = 20
,因为没有这样的操作。使用 @>
运算符:
hstore @> hstore左操作数是否包含右?
您可以编写:
从产品
中选择计数(id)
WHERE ext @> ‘size => 41,mass => 20';
但是,只有尺寸等于41且质量等于20的这些产品才需要。 p>
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?
Reading hstore documentation your (last query) size>=41
does not mean "when size is greater or equal than 41":
text => text make single-pair hstore
Following that you can't write mass<=20
, because there is no such operation. Using @>
operator:
hstore @> hstore does left operand contain right?
you can write:
SELECT count(id)
FROM product
WHERE ext @> 'size=>41,mass=>20';
However it takes only these products where size is equal to 41 and mass is equal to 20.
这篇关于PostgreSQL-查询HSTORE值的GIN索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!