PostgreSQL简单关联查询执行过慢问题求助

先仔细拆解了你的执行计划，几个核心性能瓶颈一目了然，咱们一步步来解决：

1. 给order表添加精准复合索引——解决全表扫描的核心问题 #

你的order表当前采用并行全表扫描，扫了将近2700万行（862万符合条件，1901万被过滤），这一步就耗时78秒，是最大的性能黑洞。

问题根源在于：过滤条件product = 'BTC-USD' + (close_time > '2018-03-28 07:00:00' OR close_time IS NULL)没有对应的索引，数据库只能硬扫全表。

直接创建这个覆盖索引：

CREATE INDEX idx_order_product_close_time_id ON public."order" USING btree (product, close_time, id);

• 设计逻辑：
  • 先按product快速筛选出所有BTC-USD的订单，大幅缩小扫描范围；
  • 接着用close_time过滤出符合时间要求的行；
  • 最后把关联需要的id字段纳入索引，数据库无需回表读取原数据，直接从索引中获取所有必要信息，IO开销骤降。

2. 给order_history表补充覆盖索引——减少Join时的回表开销 #

你已有的order_history_time_idx和order_history_order_id索引可以进一步优化为覆盖索引，让Join操作无需读取堆表数据：

CREATE INDEX idx_order_history_order_id_time_amount ON public.order_history USING btree (order_id, "time", amount);

这个索引包含了Join所需的order_id，以及查询返回的time和amount字段，数据库直接从索引取数，避免了额外的磁盘IO操作。

3. 调整work_mem参数——让Hash Join在内存中完成 #

从执行计划可见，Hash操作拆分了32个Batches，说明默认的work_mem配额不足，数据库不得不将中间结果写入临时文件，大幅拖慢速度。如果你的服务器内存充足（比如16G以上），可以先临时调高参数测试效果：

-- 当前会话生效
SET work_mem = '64MB';

若优化效果明显，再修改postgresql.conf永久生效（需重启服务）：

ALTER SYSTEM SET work_mem = '64MB';

⚠️ 注意：work_mem是单个操作的内存配额，并行查询的每个Worker都会占用该额度，不要设置过大——比如32G内存的服务器，设置64MB~128MB是安全范围。

4. 验证优化效果 #

添加索引、调整参数后，重新执行EXPLAIN(ANALYZE, BUFFERS)，你会看到：

• order表的扫描会从Parallel Seq Scan变为Index Scan，扫描行数和耗时都会大幅下降；
• Hash Join的Batches数减少甚至消失，无需写入临时文件；
• 整体执行时间应该能从195秒压缩到几秒甚至更短。

如果还有性能问题，再根据新的执行计划调整即可。

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