为什么Postgres Hstore索引适用于“？”运算符而不适用于EXIST（函数）？

Question

http://www.postgresql.org/docs/9.2/static/hstore.html网站上写道：

hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators

然而，索引不适用于EXIST函数，这似乎等同于?运算符。

运算符和函数有何区别，使得其中一个更难索引？

未来的Hstore扩展版本是否会使它们真正等效？

- Seamus Abshere

---

EXISTS不是一个函数，而是一个关系运算符。EXISTS(...)子查询中有什么？（可能是您提到的运算符之一） - wildplasser

例如，'SELECT * FROM foo WHERE bar ?'aaa''将使用索引，但是'SELECT * FROM foo WHERE EXIST(bar,'aaa')'不会使用索引 - 即使查询在功能上是等效的。 - Seamus Abshere

实际上有一个名为 EXIST 的函数吗？还是你指的是 EXISTS 运算符？ - wildplasser

hstore扩展定义了函数EXIST - 请查阅技术手册。 - Seamus Abshere

Answer 1

查询“CREATE OPERATOR CLASS”文档，该文档描述了如何为任意运算符创建索引方法。您还需要使用“CREATE OPERATOR”首先基于EXIST函数创建一个运算符。

（注意：我没有hstore的经验）

http://www.postgresql.org/docs/9.0/static/sql-createoperator.html

http://www.postgresql.org/docs/9.0/static/sql-createopclass.html

- Jürgen Strobel

---

那么你认为hstore的贡献者们没有为EXIST做这件事情是有原因的吗？ - Seamus Abshere

就像我之前所说，我不太了解hstore，但是从简单地浏览它来看，也许运算符“？”已经以正确的方式包装了exist函数。请注意，通常情况下，函数不能用于索引，因为它们可能是不确定的并且依赖于非参数数据，只有运算符提供必要的元数据以供索引使用。 - Jürgen Strobel

我向 pgsql-hackers 提出了修复建议。 - Seamus Abshere

Answer 2

这是您的问题：PostgreSQL函数是规划器不透明的。规划器无法知道运算符和函数在语义上是等价的。这经常出现。

PostgreSQL确实有功能索引，因此您可以对不可变函数的输出建立索引，但这可能不能完美地使事情正常工作，因为您可能只能索引返回给定调用的true行，但这仍然可以通过部分索引非常有用。例如，您始终可以执行类似以下的操作：

CREATE INDEX bar_has_aaa ON foo(exists(bar, 'aaa'));

或者

CREATE INDEX bar_has_aaa ON foo(id) where exists (bar, 'aaa');

但我认为这并不完全符合你需要的方向。希望它能指引你朝着正确的方向前进。

编辑：以下方法似乎是更好的解决方案。假设我们有一个名为foo的表：

 CREATE TABLE foo (
        id serial,
        bar hstore
 );

我们可以创建一个名为bar_keys的表方法：

CREATE FUNCTION bar_keys(foo) RETURNS text[] IMMUTABLE LANGUAGE SQL AS $$
    SELECT akeys($1.bar);
$$;

然后我们可以使用GIN进行索引：

 CREATE INDEX foo_bar_keys_idx ON foo USING gin(bar_keys(foo));

而且我们可以在查询中使用它：

  SELECT * FROM foo WHERE foo.bar_keys @> array['aaa'];

这应该使用索引。请注意，您可以直接索引/使用 akeys，但我认为虚拟列会导致更清晰的语法。

- Chris Travers

---

在hstore contrib源代码中，是否有一种方法可以将它们标记为等效或有意启用索引而无需用户干预？ - Seamus Abshere

没有。不过我一直在考虑这个问题。我会更新我的答案，提供另一个更好的解决方案。 - Chris Travers