PostgreSQL BRIN 索引与区间扫描优化实战

示例表与数据（时间序列）：CREATE TABLE events (

ts timestamptz NOT NULL,

uid bigint NOT NULL,

payload jsonb NOT NULL

);

创建 BRIN 索引并设置分段大小：CREATE INDEX idx_events_ts_brin ON events USING BRIN (ts) WITH (pages_per_range = 64);

VACUUM ANALYZE events;

范围查询与计划验证：EXPLAIN ANALYZE SELECT * FROM events WHERE ts >= now() - interval '1 day' AND ts < now();

维护建议：REINDEX INDEX idx_events_ts_brin;

VACUUM (VERBOSE, ANALYZE) events;