int4与DATE类型复合主键的最优索引选型咨询

结论 #

直接复用现有复合主键对应的唯一B-tree索引即可，前提是主键的字段顺序为 (id, created_at)（你的查询场景正好匹配这个顺序）。如果主键顺序是反过来的，则需要额外创建一个 (id, created_at) 的普通B-tree索引。

原因说明 #

• 你的查询模式是先通过id做等值匹配，再通过created_at做范围过滤，这种场景下，前缀为等值字段、后续为范围字段的复合B-tree索引是最高效的：索引会先快速定位所有id=4的行，再在这个子集里按created_at的范围快速筛选，避免全表扫描。
• 由于表仅支持插入、无更新操作，索引不会产生碎片，维护成本极低；而主键本身就会自动生成对应的唯一索引，不需要额外创建新索引，避免冗余。
• 若主键顺序是(created_at, id)，则该索引无法高效支持你的查询（因为前缀是范围字段，无法快速定位特定id的行），此时需要手动创建索引：

  CREATE INDEX idx_table_id_created_at ON table (id, created_at);
  

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