如何在Python中快速从PostgreSQL获取百万级时序数据（将查询耗时从143秒优化至30秒内）

这问题我之前处理过类似的时序表查询慢的情况，给你几个亲测有效的优化方向，应该能把查询时间压到30秒以内：

1. 先搞定索引——最立竿见影的优化 #

你的查询核心是按date_time范围过滤，目前大概率是全表扫描，这是慢的根源。建议优先做这两个索引优化：

（1）覆盖索引（推荐首选） #

直接创建包含所有查询字段的覆盖索引，这样数据库不用回表查主数据，直接从索引里取结果，速度会飙升：

CREATE INDEX idx_data_five_minutes_covering ON data_five_minutes (date_time)
INCLUDE (script_id, open, high, low, close, volume);

如果你的date_time是带时区的类型，记得保持查询条件和字段类型一致（比如不要用字符串和带时区的timestamp比），否则索引会失效。

（2）复合索引（如果需要排序） #

如果你必须保留排序，原来的ORDER BY id其实没什么意义（查询结果里没id，而且id和业务时序无关），建议改成按date_time, script_id排序，同时建对应的复合索引：

CREATE INDEX idx_data_five_minutes_dt_script ON data_five_minutes (date_time, script_id)
INCLUDE (open, high, low, close, volume);

这样查询时可以直接利用索引的有序性，避免额外的排序开销——排序是查询慢的常见元凶之一。

2. 优化SQL语句本身 #

• 去掉不必要的排序：如果业务上不需要按id排序，直接删掉ORDER BY id，这能省掉大量的CPU和IO开销。
• 用参数化查询：避免直接把字符串拼进SQL，不仅安全，还能让PostgreSQL缓存查询计划，重复查询时更快。

3. Python代码层面的优化 #

你的自定义db_fetchquery可能不如pandas原生的read_sql_query高效，试试改成这样：

import pandas as pd
import psycopg2

# 替换成你的数据库连接信息
conn = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_pwd",
    host="your_host",
    port="your_port"
)

query = """
SELECT script_id, date_time, open, high, low, close, volume 
FROM data_five_minutes 
WHERE date_time >= %s AND date_time <= %s
-- 业务允许的话直接删掉下面这行排序
ORDER BY date_time, script_id;
"""
# 参数化传值，避免SQL注入+缓存查询计划
params = ('2019-01-11 09:35:00', '2019-01-11 09:50:00')
df = pd.read_sql_query(query, conn, params=params)

conn.close()
print(df)

4. 临时调大数据库内存参数（应急用） #

如果查询需要排序，PostgreSQL默认的work_mem可能太小，导致用磁盘排序（慢到离谱）。可以临时给当前会话调大：

SET work_mem = '64MB'; -- 根据你的服务器内存调整，比如16GB内存的话可以设到128MB

测试完如果有效，可以在postgresql.conf里永久调整（记得重启服务）。

5. 长期优化：分区表 #

你的数据是时序数据，按月份分区是绝佳选择——查询2019-01的数据时，只会扫描1月份的分区，不用扫全表90万行。具体步骤大概是：

1. 创建主表（分区父表）
2. 按月创建子分区
3. 把现有数据迁移到对应的分区里
4. 给每个分区建索引（或继承主表索引）
   这个设置稍微复杂，但对长期维护时序数据非常友好，后续查询速度会一直保持高效。

先排查：看执行计划找瓶颈 #

在做任何优化前，先跑一下执行计划，确认问题根源：

EXPLAIN ANALYZE 
SELECT script_id,date_time,open,high,low,close,volume 
FROM data_five_minutes 
WHERE date_time >= '2019-01-11 09:35:00' and date_time <= '2019-01-11 09:50:00' 
ORDER BY id;

如果看到Seq Scan（全表扫描），那建索引是第一要务；如果是Index Scan但还是慢，那大概率是需要覆盖索引或者调内存参数。

按上面的步骤来，尤其是覆盖索引+去掉不必要排序，应该能轻松把查询时间降到30秒以内。

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