向 PostgreSQL 多列部分索引添加日期时间约束

我有一个名为的 PostgreSQL 表queries_query，其中有很多列。

其中两列，created and user_sid，我的应用程序经常在 SQL 查询中一起使用，以确定给定用户在过去 30 天内执行了多少次查询。我查询这些统计数据的时间早于最近 30 天的情况非常非常少。

这是我的问题：

我目前已通过运行以下命令在这两列上创建了多列索引：

CREATE INDEX CONCURRENTLY some_index_name ON queries_query (user_sid, created)

但我想进一步限制索引只关心创建日期在过去 30 天内的那些查询。我尝试执行以下操作：

CREATE INDEX CONCURRENTLY some_index_name ON queries_query (user_sid, created)
WHERE created >= NOW() - '30 days'::INTERVAL`

但这会引发一个异常，表明我的函数必须是不可变的。

我很想让这个工作正常进行，这样我就可以优化我的索引，并减少 Postgres 执行这些重复查询所需的资源。

你会得到一个异常now()因为该函数不是IMMUTABLE（显然）并且，引用the manual:

索引定义中使用的所有函数和运算符都必须是“不可变的”...

我看到两种利用（更有效）部分索引的方法：

1.有条件使用的部分索引constant date:

CREATE INDEX queries_recent_idx ON queries_query (user_sid, created)
WHERE created > '2013-01-07 00:00'::timestamp;

Assuming created实际上定义为timestamp。提供一个是行不通的timestamp一个常数timestamptz柱子 （timestamp with time zone）。演员阵容来自timestamp to timestamptz（或反之亦然）取决于当前时区设置并且是不是一成不变的。使用匹配数据类型的常量。了解带/不带时区的时间戳的基础知识：

• 在 Rails 和 PostgreSQL 中完全忽略时区

删除并重新创建该索引在流量较低的时间进行，也许每天或每周都有一个 cron 作业（或者任何对你来说足够好的）。创建索引非常快，尤其是相对较小的部分索引。该解决方案也不需要向表中添加任何内容。

假设没有并发访问对于表，可以使用如下函数完成自动索引重建：

CREATE OR REPLACE FUNCTION f_index_recreate()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   DROP INDEX IF EXISTS queries_recent_idx;
   EXECUTE format('
      CREATE INDEX queries_recent_idx
      ON queries_query (user_sid, created)
      WHERE created > %L::timestamp'
    , LOCALTIMESTAMP - interval '30 days');  -- timestamp constant
--  , now() - interval '30 days');           -- alternative for timestamptz
END
$func$;

Call:

SELECT f_index_recreate();

now()（就像你一样）相当于CURRENT_TIMESTAMP并返回timestamptz。投射到timestamp with now()::timestamp or use LOCALTIMESTAMP反而。

• 仅选择今天（自午夜以来）的时间戳

db<>fiddle here
_{Old sqlfiddle}

如果你必须处理并发访问到桌子上，使用DROP INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY。但你不能将这些命令包装到一个函数中，因为，根据文档:

...常规的CREATE INDEX命令可以在一个 交易区块，但是CREATE INDEX CONCURRENTLY cannot.

所以，与两笔独立的交易:

CREATE INDEX CONCURRENTLY queries_recent_idx2 ON queries_query (user_sid, created)
WHERE  created > '2013-01-07 00:00'::timestamp;  -- your new condition

Then:

DROP INDEX CONCURRENTLY IF EXISTS queries_recent_idx;

（可选）重命名为旧名称：

ALTER INDEX queries_recent_idx2 RENAME TO queries_recent_idx;

2. 以“已归档”标签为条件的部分索引

Add an archived标记到您的表：

ALTER queries_query ADD COLUMN archived boolean NOT NULL DEFAULT FALSE;

UPDATE您选择“退休”旧行的时间间隔的列并创建一个索引，如下所示：

CREATE INDEX some_index_name ON queries_query (user_sid, created)
WHERE NOT archived;

向您的查询添加匹配条件（即使看起来多余）以允许它使用索引。检查与EXPLAIN ANALYZE查询计划程序是否流行 - 它应该能够使用索引来进行较新日期的查询。但它无法理解不完全匹配的更复杂的条件。

您不必删除并重新创建索引，但UPDATE表上的操作可能比重新创建索引更昂贵，并且表会变得稍大一些。

我会选择first选项（索引重建）。事实上，我在几个数据库中使用这个解决方案。第二种会带来更昂贵的更新。

随着时间的推移，这两种解决方案都保留了它们的有用性，但随着索引中包含更多过时的行，性能会慢慢恶化。