postgresql - 将日期时间约束添加到 PostgreSQL 多列部分索引

Question

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

其中两个列，created 和 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 执行这些重复查询所需的资源。

Answer 1

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

All functions and operators used in an index definition must be "immutable" ...

我看到了两种利用(效率更高的)部分索引的方法:

1。条件使用常量日期的部分索引:

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

假设 created 实际上被定义为timestamp。为 timestamptz 列提供 timestamp 常量(timestamp with time zone)是行不通的。从 timestamp 到 timestamptz 的转换(反之亦然)取决于当前时区设置并且不是不可变的 .使用匹配数据类型的常量。了解带/不带时区的时间戳的基础知识:

• Ignoring time zones altogether in Rails and 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$;

调用:

SELECT f_index_recreate();

now()(就像您之前那样)相当于 CURRENT_TIMESTAMP 并返回 timestamptz。使用 now()::timestamp 转换为 timestamp 或改用 LOCALTIMESTAMP。

• Select today's (since midnight) timestamps only

db<> fiddle here
<子>旧sqlfiddle

---

如果您必须处理对表的并发访问，请使用DROP INDEX CONCURRENTLY 和CREATE INDEX CONCURRENTLY。但是你不能将这些命令包装到一个函数中，因为，per documentation :

... a regular CREATE INDEX command can be performed within atransaction block, but 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

然后:

DROP INDEX CONCURRENTLY IF EXISTS queries_recent_idx;

可选地，重命名为旧名称:

ALTER INDEX queries_recent_idx2 RENAME TO queries_recent_idx;

2。带有“归档”标签条件的部分索引

将 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 选项(索引重建)。事实上，我正在几个数据库中使用这个解决方案。第二种会导致更新成本更高。

随着时间的推移，这两种解决方案都保持有用性，随着更多过时的行被包含在索引中，性能会慢慢下降。