PostgreSQL关联中setCreator方法执行缓慢的原因与优化咨询

代码示例 #

Lists.belongsTo(Users, { foreignKey: "creator_id", as: "creator" });    
let payload= { ...newTask };   
res = await Lists.create(payload);    
await res.setCreator(userId);

生成的SQL查询 #

Executing (default):
INSERT INTO "public"."lists" ("id","title","date","status","t_shirt_size","priority","category","in_focus","created_at","updated_at","project_id") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) RETURNING "id","title","description","date","status","meta","t_shirt_size","priority","additional_comment","category","bug_source","in_focus","created_at","updated_at","project_id","goal_id","category_id","type_id","creator_id","owner_id","reviewer_id","parent_id","story_id","department_id","marketing_tasks_id";

Executing (default): UPDATE "public"."lists" SET "creator_id"=$1,"updated_at"=$2 WHERE "id" = $3

问题描述 #

创建lists行后，setCreator函数执行耗时可达20-30秒，尝试过原生查询更新该行、直接在payload中传入creator_id，执行速度依旧缓慢。

可能的原因 #

• 索引缺失：lists表的id字段未设为主键或无索引，导致UPDATE语句执行时全表扫描；creator_id外键未关联Users表主键，或Users.id无索引，外键约束检查耗时过长。
• 数据库锁阻塞：目标lists记录被其他长时间运行的事务锁住，UPDATE操作需等待锁释放。
• 触发器影响：lists表存在UPDATE触发器，触发器内执行复杂逻辑（如关联查询、批量操作）导致整体耗时增加。
• 数据库资源瓶颈：数据库服务器CPU、内存、磁盘IO不足，导致查询排队等待资源。
• 模型配置问题：creator_id字段未在Lists模型的允许字段中定义，直接传入payload时未被写入，仍需后续UPDATE操作。

优化方案 #

1. 检查并修复索引

   • 确认lists.id是主键（自动生成主键索引），若不是则添加主键约束。
   • 确认Users.id为主键，同时给lists.creator_id添加普通索引（部分数据库中外键会自动创建索引，需手动验证）。

2. 排查数据库锁情况

   • 使用数据库工具查看锁状态，例如PostgreSQL执行：

     SELECT * FROM pg_locks WHERE relation = 'lists'::regclass;
     

   • 终止长时间持锁的无响应事务，避免阻塞后续操作。

3. 检查触发器逻辑

   • 查询lists表的触发器列表，例如PostgreSQL执行：

     SELECT * FROM pg_trigger WHERE tgrelid = 'lists'::regclass;
     

   • 简化触发器内部逻辑或移除不必要的触发器。

4. 优化创建逻辑，避免二次更新

   • 确保Lists模型中已定义creator_id字段，直接在创建时传入该值，减少一次UPDATE操作：

     let payload = { ...newTask, creator_id: userId };
     res = await Lists.create(payload);
     

5. 排查数据库资源状态

   • 查看数据库服务器的CPU、内存、磁盘IO使用率，确认是否存在资源瓶颈，必要时升级服务器配置。

6. 分析查询执行计划

   • 使用数据库的查询分析工具（如PostgreSQL的EXPLAIN ANALYZE）查看UPDATE语句的执行计划，定位全表扫描、慢查询等问题：

     EXPLAIN ANALYZE UPDATE "public"."lists" SET "creator_id"=$1,"updated_at"=$2 WHERE "id" = $3;
     

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