PostgreSQL大表（140M行）created_at查询优化方案咨询

针对你提到的1.4亿行users表中created_at <= '2026-04-17 00:00:00'查询耗时过长的问题，以下是几个实用的优化方向：

1. 为created_at创建B-tree索引 #

PostgreSQL中B-tree索引天然适合范围查询（比如<、<=、>等），能直接定位到符合条件的数据范围，避免全表扫描。

• 常规创建索引（锁表，适合低峰期操作）：

CREATE INDEX idx_users_created_at ON users(created_at);

• 无锁创建索引（适合业务高峰期，不影响读写）：

CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);

2. 避免SELECT *，仅查询所需列 #

SELECT *会拉取所有列的数据，即使有索引，也需要频繁回表读取整行数据，开销极大。如果业务只需要部分字段，明确指定列名：

SELECT id, name, created_at FROM users WHERE created_at <= '2026-04-17 00:00:00';

如果需要的列固定，还可以创建覆盖索引，把所需列包含到索引中，彻底避免回表：

CREATE INDEX idx_users_created_at_covering ON users(created_at) INCLUDE (id, name, email);

3. 更新表统计信息 #

PostgreSQL的查询规划器依赖准确的统计信息来选择最优执行计划，旧的统计信息可能导致规划器选错执行路径。执行以下命令更新统计：

ANALYZE users;

4. 采用时间分区表 #

如果created_at字段是递增的（比如用户注册时间随时间增长），将表按时间分区是长期优化的最佳方案之一。分区后查询只会扫描符合条件的分区，而非全表：

• 创建分区主表：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

• 创建具体分区（示例按季度划分）：

CREATE TABLE users_2024_q1 PARTITION OF users FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE users_2024_q2 PARTITION OF users FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
-- 根据数据时间范围创建更多分区

5. 调整PostgreSQL配置参数 #

针对大表查询，适当调整内存相关参数可以显著提升性能：

• 修改postgresql.conf中的参数（根据服务器硬件配置调整）：

shared_buffers = 8GB  -- 建议设置为物理内存的1/4
work_mem = 64MB       -- 单个查询可使用的内存，避免磁盘临时文件

• 重新加载配置（无需重启服务）：

SELECT pg_reload_conf();

6. 确保时间条件无隐式转换 #

确认created_at的类型与查询中的时间字符串匹配，避免隐式类型转换导致索引失效。如果created_at是TIMESTAMP WITHOUT TIME ZONE，显式指定类型更稳妥：

SELECT * FROM users WHERE created_at <= TIMESTAMP '2026-04-17 00:00:00';

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