function - Postgres 可以在部分索引 where 子句中使用函数吗？

Question

我有一个大的 Postgres 表，我想在其中对已索引的 2 列中的 1 列进行部分索引。我能否以及如何在部分索引的 where 子句中使用 Postgres 函数，然后让选择查询使用该部分索引？

示例场景

第一栏是“杂志”，第二栏是“卷”，第三栏是“期”。所有杂志都可以有相同的“卷”和“期”号，但我希望索引只包含该杂志的最新两卷。这是因为一本杂志可能比其他杂志更老，并且比新杂志的销量更高。

创建了两个不可变的严格函数来确定杂志 f_current_volume('gq') 和 f_previous_volume('gq') 的当前和过去年份的数量。注意:当前/过去的数量 # 每年仅更改一次。

我尝试使用这些函数创建部分索引，但是在查询中使用 explain 时，它只会对当前卷杂志进行序列扫描。

CREATE INDEX ix_issue_magazine_volume ON issue USING BTREE ( magazine, volume ) 
  WHERE volume IN (f_current_volume(magazine), f_previous_volume(magazine));

-- Both these do seq scans.
select * from issue where magazine = 'gq' and volume = 100;
select * from issue where magazine = 'gq' and volume = f_current_volume('gq');

为了完成这项工作，我做错了什么？如果可能的话，为什么 Postgres 需要这样做才能使用索引？

-- UPDATE: 2013-06-17, the following surprisingly used the index.
-- Why would using a field name rather than value allow the index to be used?
select * from issue where magazine = 'gq' and volume = f_current_volume(magazine);

Answer 1

不变性和“当前”

如果您的 f_current_volume 函数改变了它的行为 - 正如它的名字所暗示的那样，并且存在 f_previous_volume 函数，那么数据库可以自由返回 完全虚假的结果。

PostgreSQL 会拒绝让您创建索引，提示您只能使用IMMUTABLE 函数。问题是，根据文档，将函数标记为 IMMUTABLE 意味着您正在告诉 PostgreSQL 有关该函数行为的一些信息。你是说“我保证这个函数的结果不会改变，请随意在此基础上做出假设。”

最大的假设之一是在建立索引时。如果函数在多次调用时针对不同的输入返回不同的输出，事情就会变得啪啪。或者如果你不走运，可能会砰。从理论上讲，您可以通过 REINDEX 对所有内容进行更改来更改不可变函数，但唯一真正安全的方法是 DROP 使用它的每个索引，DROP 函数，使用新定义重新创建函数并重新创建索引。

如果您有一些不经常更改的东西，那么这实际上非常有用，但您确实在不同的时间点有两个不同的不可变函数，而这些函数恰好具有相同的名称。

部分索引匹配

PostgreSQL 的部分索引匹配非常愚蠢 - 但是，正如我在为此编写测试用例时发现的那样，它比以前聪明得多。它忽略了一个虚拟的 OR true。它使用 WHERE (a%100=0 OR a%1000=0) 上的索引进行 WHERE a = 100 查询。它甚至通过不可内联的身份函数获得了它:

regress=> CREATE TABLE partial AS SELECT x AS a, x 
          AS b FROM generate_series(1,10000) x;
regress=> CREATE OR REPLACE FUNCTION identity(integer) 
          RETURNS integer AS $$
          SELECT $1; 
          $$ LANGUAGE sql IMMUTABLE STRICT;
regress=> CREATE INDEX partial_b_fn_idx 
          ON partial(b) WHERE (identity(b) % 1000 = 0);
regress=> EXPLAIN SELECT b FROM partial WHERE b % 1000 = 0;
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Index Only Scan using partial_b_fn_idx on partial  (cost=0.00..13.05 rows=50 width=4)
(1 row)

但是，无法证明IN子句匹配，eg:

regress=> DROP INDEX partial_b_fn_idx;
regress=> CREATE INDEX partial_b_fn_in_idx ON partial(b)
          WHERE (b IN (identity(b), 1));
regress=> EXPLAIN SELECT b FROM partial WHERE b % 1000 = 0;
                               QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on partial  (cost=10000000000.00..10000000195.00 rows=50 width=4)

那么我的建议呢？将 IN 重写为 OR 列表:

CREATE INDEX ix_issue_magazine_volume ON issue USING BTREE ( magazine, volume ) 
  WHERE (volume = f_current_volume(magazine) OR volume = f_previous_volume(magazine));

... 在当前版本上它可能会正常工作，只要您牢记上面概述的不变性规则。那么，第二个版本:

select * from issue where magazine = 'gq' and volume = f_current_volume('gq');

可能。 更新:不，不会；要使用它，Pg 必须识别 magazine='gq' 并意识到 f_current_volume('gq') 因此等同于 f_current_volume(magazine )。它不会尝试通过部分索引匹配来证明该级别的等价性，因此正如您在更新中指出的那样，您必须直接编写 f_current_volume(magazine)。我早该发现的。理论上，如果规划器足够聪明，PostgreSQL 可以在第二个查询中使用索引，但我不确定您将如何有效地寻找像这样的替换值得的地方。

第一个示例，volume = 100 将永远不会使用索引，因为在查询计划时 PostgreSQL 不知道 f_current_volumne('gg'); 将评估为100。不过，您可以将 OR 子句 OR volume = 100 添加到您的部分索引 WHERE 子句中，然后 PostgreSQL 会计算出来。