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

创建包含生成列的表：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;