PostgreSQL初始化Recipe对象远慢于SQLite的性能问题求助

问题场景与代码现状 #

现有逻辑是从数据库查询食材二进制向量，并为每个向量创建Recipe对象，核心代码如下：

...
try:
    start_time = time.time()
    recipes = list(
        select(r for r in RECIPE_INGREDIENT_VECTOR if r.valid == 1))
    elapsed_time = time.time() - start_time
    print(f"Time taken to query recipe vectors: {elapsed_time} seconds")
    start_time = time.time()
    for r in recipes:
        ingredient_vector_int = [int(s)
                                 for s in r.ingredient_vector.split(',')]
        get_recipe_name(r.recipe_id)
        get_ingredients_by_recipe_id(r.recipe_id)
        recipe_obj = Recipe(
            r.recipe_id, get_recipe_name(r.recipe_id),get_ingredients_by_recipe_id(r.recipe_id))
...

其中用于查询菜谱名称和食材的ORM函数为：

@db_session
def get_recipe_name(recipe_id):
    p = RAW_RECIPES[recipe_id]
    return p.name

@db_session
def get_ingredients_by_recipe_id(recipe_id):
    recipe_ingredients_str = select(
        r.ingredients for r in RAW_RECIPES if r.id == recipe_id).first().strip("[]")
    result = [s for s in recipe_ingredients_str.split(',')]
    return result

测试结果 #

在raw_recipes表的id列已设为索引和主键的前提下：

• 循环调用上述两个ORM函数时，SQLite耗时4秒，PostgreSQL需7秒甚至更久
• 若将这两个ORM函数放入Recipe对象初始化逻辑中，SQLite耗时约1分钟，PostgreSQL耗时是SQLite的3-4倍
• 更换PostgreSQL的ORM为SQLAlchemy后，性能问题仍未改善

排查思路 #

• 网络延迟排查：若PostgreSQL为远程部署，循环中单条查询的网络往返开销会持续累加，可通过psql执行单条SELECT命令，对比本地SQLite和远程PostgreSQL的单次查询延迟
• ORM会话开销检查：每个@db_session装饰器可能会创建新的会话或事务，PostgreSQL的事务开销远高于SQLite，需确认会话是否在循环中重复创建销毁
• SQL执行日志分析：开启ORM的SQL日志，查看生成的SQL语句是否最优，是否存在不必要的字段查询或无意义的锁操作
• PostgreSQL执行计划与配置检查：用EXPLAIN ANALYZE执行查询，确认索引是否被正确使用；检查shared_buffers、work_mem等配置项是否合理，是否存在内存不足导致的磁盘IO

优化建议 #

• 批量查询替代循环单查
  先收集所有需要的recipe_id，一次性批量查询所有对应数据，避免多次数据库往返：

  # 收集所有待查询的recipe_id
  recipe_ids = [r.recipe_id for r in recipes]
  
  # 批量获取所有菜谱的名称和食材数据
  with db_session:
      recipe_data_map = {
          r.id: (r.name, r.ingredients) 
          for r in RAW_RECIPES.select(lambda x: x.id in recipe_ids)
      }
  
  # 循环使用预查询的数据创建对象
  for r in recipes:
      name, ingredients_str = recipe_data_map[r.recipe_id]
      ingredients = [s.strip() for s in ingredients_str.strip("[]").split(',')]
      recipe_obj = Recipe(r.recipe_id, name, ingredients)
  

• 合并关联查询
  将RECIPE_INGREDIENT_VECTOR与RAW_RECIPES表做JOIN查询，一次性获取所有需要的字段，彻底避免多次查询：

  with db_session:
      # 关联查询获取所有必要数据
      combined_query = select(
          rv.recipe_id, rv.ingredient_vector, rr.name, rr.ingredients
          for rv in RECIPE_INGREDIENT_VECTOR
          if rv.valid == 1
          for rr in RAW_RECIPES if rr.id == rv.recipe_id
      )
      combined_results = list(combined_query)
  
  # 循环处理结果创建对象
  for recipe_id, vec_str, name, ingredients_str in combined_results:
      ingredient_vector_int = [int(s) for s in vec_str.split(',')]
      ingredients = [s.strip() for s in ingredients_str.strip("[]").split(',')]
      recipe_obj = Recipe(recipe_id, name, ingredients)
  

• 统一会话管理
  移除函数上的@db_session装饰器，在外层循环统一开启一个会话，减少会话创建与销毁的开销：

  with db_session:
      recipes = list(select(r for r in RECIPE_INGREDIENT_VECTOR if r.valid == 1))
      for r in recipes:
          ingredient_vector_int = [int(s) for s in r.ingredient_vector.split(',')]
          p = RAW_RECIPES[r.recipe_id]
          name = p.name
          ingredients_str = p.ingredients.strip("[]")
          ingredients = [s.strip() for s in ingredients_str.split(',')]
          recipe_obj = Recipe(r.recipe_id, name, ingredients)
  

• 优化数据存储格式
  将ingredients字段从字符串格式改为JSON/JSONB类型（PostgreSQL和SQLite均支持），ORM可直接解析为列表，省去手动字符串拆分的开销，同时提升查询效率。

• PostgreSQL特定优化

  • 启用连接池：复用数据库连接，避免每次查询新建连接的开销
  • 调整work_mem配置：针对批量查询场景适当调大，让PostgreSQL在内存中处理排序等操作
  • 开启预编译语句：减少SQL重复解析的开销

内容的提问来源于stack exchange，提问作者VU VIET QUANG