如何为PostgreSQL的最新数据查询创建低开销索引？

我有如下结构的PostgreSQL表：

CREATE TABLE items(
  id       bigint primary key,
  updated  timestamp
);

我需要查询最近更新的记录，常规查询语句为：

SELECT id, updated FROM items ORDER BY updated DESC LIMIT 1;

但当表数据量达数千万行时，这个查询速度很慢。我考虑为updated字段创建索引，但这会占用大量空间且可能降低插入性能。

Partial Indexes（部分索引）看似符合需求，但我仅需保留最新的一条记录，比如尝试过以下语句，但不确定是否可行：

CREATE INDEX items_updated ON items (updated) WHERE updated = MAX(updated);

我期望能创建仅包含前N条记录的索引（此处N=1，以下为非合法语法示例）：

CREATE INDEX items_updated ON items (updated DESC) LIMIT 1;

请问是否存在既能避免全列索引的空间开销、不显著降低插入性能，又能优化目标查询的方案？

1. 维护专用的"最新记录"小表 #

创建一张仅存储单条最新记录的小表，通过触发器自动更新：

-- 创建存储最新记录的表
CREATE TABLE latest_item (
  id bigint primary key,
  updated timestamp
);

-- 初始化数据
INSERT INTO latest_item SELECT id, updated FROM items ORDER BY updated DESC LIMIT 1;

-- 编写触发器函数
CREATE OR REPLACE FUNCTION update_latest_item()
RETURNS TRIGGER AS $$
BEGIN
  -- 仅当新记录的更新时间晚于当前最新记录时，才更新小表
  IF NEW.updated > (SELECT COALESCE(updated, '1970-01-01') FROM latest_item) THEN
    TRUNCATE latest_item;
    INSERT INTO latest_item VALUES (NEW.id, NEW.updated);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到items表的插入/更新操作
CREATE TRIGGER trigger_update_latest
AFTER INSERT OR UPDATE ON items
FOR EACH ROW EXECUTE FUNCTION update_latest_item();

• 查询时直接访问小表：SELECT * FROM latest_item;，性能几乎无损耗
• 触发器仅在必要时更新小表，对原表插入/更新性能影响极小
• 空间开销可以忽略，仅存储一条记录

2. 使用物化视图 #

创建只包含最新记录的物化视图，按需刷新：

CREATE MATERIALIZED VIEW latest_item AS
SELECT id, updated FROM items ORDER BY updated DESC LIMIT 1;

• 查询直接访问物化视图，速度极快
• 刷新方式：
  • 手动刷新：REFRESH MATERIALIZED VIEW latest_item;
  • 若需要近实时数据，可通过触发器在原表更新后自动刷新，但频繁刷新会有轻微性能损耗，适合更新频率不极高的场景

3. 带时间阈值的部分索引（业务场景受限） #

如果业务上能确定最新记录一定在某个时间窗口内（比如最近30天），可以创建仅包含该窗口数据的部分索引：

CREATE INDEX items_updated_recent ON items (updated DESC)
WHERE updated > NOW() - INTERVAL '30 days';

• 索引空间远小于全列索引
• 局限性：若最新记录超出设定的时间窗口，查询仍会触发全表扫描，仅适合特定业务场景

你写的CREATE INDEX items_updated ON items (updated) WHERE updated = MAX(updated);不可行，因为PostgreSQL部分索引的条件必须是静态表达式，不能使用MAX()这类聚合函数，无法动态维护索引的筛选条件。

内容的提问来源于stack exchange，提问作者Carl Mastrangelo