PostgreSQL子查询返回多行报错，700k数据关联查询优化求助

错误原因 #

你遇到的more than one row returned by a subquery used as an expression错误，是因为用=运算符匹配返回多行结果的子查询。=只能用于单个值的匹配，而你的子查询会返回70多万个trip_id，因此触发报错。

操作错误修正 #

1. 字段名错误：原查询中where dap_iot_trip_id = (...)里的dap_iot_trip_id，根据你描述的表结构，应该是trip_id（dap_iot_stdd_details表的字段是trip_id），这会导致查询逻辑错误。
2. 运算符使用错误：用=匹配多行子查询结果，应该替换为IN或改用JOIN语法。

正确查询写法 #

写法一：使用IN子查询

select dap_iot_stdd_id, added_on, "timestamp"
from public.dap_iot_stdd_details
where trip_id IN (
  select trip_id
  from public.trip_history
  where start_time between '1680307200000' and '1680912000000'
);

注：去掉了子查询里的order by start_time asc，因为IN子查询不需要排序，多余排序会增加性能开销。

写法二：使用JOIN（推荐大数据量场景）

JOIN通常比IN子查询在处理大量数据时性能更优，PostgreSQL的查询优化器对JOIN的执行计划优化更充分：

select d.dap_iot_stdd_id, d.added_on, d."timestamp"
from public.dap_iot_stdd_details d
inner join public.trip_history t 
  on d.trip_id = t.trip_id
where t.start_time between '1680307200000' and '1680912000000';

大数据量优化建议 #

针对70多万条trip_id的查询场景，建议做以下优化：

• 建立索引：
  • 给trip_history表的start_time和trip_id建联合索引，加速筛选和关联：

    CREATE INDEX idx_trip_history_start_trip ON public.trip_history(start_time, trip_id);
    

  • 给dap_iot_stdd_details表的trip_id建单独索引，加速关联查询：

    CREATE INDEX idx_dap_stdd_trip_id ON public.dap_iot_stdd_details(trip_id);
    

• 分批查询：如果一次性返回结果集过大，可通过LIMIT和OFFSET分批获取，或者按时间分段拆分查询，避免内存占用过高。
• 避免SELECT *：只查询你需要的字段（你已经做到了），减少数据传输和内存开销。

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