PostgreSQL 生成列(Generated Columns)与表达式索引实践

---

title: PostgreSQL 生成列(Generated Columns)与表达式索引实践

keywords: generated always, stored, expression index, date bucket, performance

description: 使用生成列简化派生字段并结合索引优化查询，减少冗余计算与提升性能。

tags:

• PostgreSQL
• date bucket
• expression index
• generated always
• stored
• 建模
• 性能
• 数据库

categories:

• 文章资讯
• 技术教程

---

创建包含生成列的表：

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  amount numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL,
  day date GENERATED ALWAYS AS (created_at::date) STORED,
  amount_cents integer GENERATED ALWAYS AS (round(amount*100)) STORED
);

为生成列创建索引与查询：

CREATE INDEX idx_orders_day ON orders(day);
CREATE INDEX idx_orders_amount_cents ON orders(amount_cents);

EXPLAIN ANALYZE SELECT count(*) FROM orders WHERE day = current_date;