PostgreSQL - 查询 HSTORE 值的 GIN 索引

Question

我有以下构造函数（作为测试）：

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

我应该如何编写这个？

- IamIC

Answer 1

您的初步尝试是正确的，但需要使用（部分）B树索引和位图索引扫描来依赖它：

create index on product(((ext->'size')::int)) where ((ext->'size') is not null);

对于质量（mass）也是一样的，如果计划者没有立即得到信息，可以添加两个where子句，即where ext->'size' is not null 和同样的对于质量。

如果存在某种模式（这很可能，因为大多数带有尺寸的产品也都有质量），可以考虑创建一个多列索引来结合这两个条件 - 一个为sac，另一个为desc。

如你所写的gin索引，连同伴随的查询（存在语法错误），基本上会做相同的事情但是无序；它将会更慢。

- Denis de Bernardy

---

谢谢Dennis。在我的测试中，我已经像你展示的那样包含了部分索引，但实际上它们更慢，无论是插入还是查询。事实上，使用GIN进行查询要快得多。为什么你说我需要部分索引来提高可靠性呢？ - IamIC

实际上，从技术上讲，那是一个表达式索引，而不是部分索引。 - IamIC

进一步测试表明，表达式索引实际上平均更快。使用GIN可以获得复杂查询的胜利。这确实是一个典型的工作负载测试场景。 - IamIC

是的。我昨天建议的实际上是表达式和部分索引。如果您在不断地执行相同类型的查询，特别是如果有任何需要对结果进行排序并将它们绑定到限制条件的情况下，我的经验是您将从（预先排序和部分）表达式索引中获得更好的性能；如果您不断针对广泛变化的条件（例如tsvector）进行查询，则GIN将胜出。 - Denis de Bernardy

Answer 2

阅读 hstore 文档，你的（最后一个）查询 size>=41 并不意味着 "当 size 大于或等于 41 时"。请注意，这里的“>=”是指匹配大于等于 41 的所有值。

text => text    make single-pair hstore

接下来，您不能写成mass<=20，因为没有这样的操作。使用@>运算符：

hstore @> hstore    does left operand contain right?

你可以写：

SELECT count(id)
FROM product
WHERE ext @> 'size=>41,mass=>20';

然而，它仅选取尺寸为41且重量为20的这些产品。

- Grzegorz Szpetkowski