为什么PostgreSQL执行UPDATE更新操作耗时异常缓慢？

1 首要排查：缺失author字段索引 #

• 没有为author字段建立索引时，UPDATE语句的WHERE条件会触发全表扫描，18万条记录即使平均行不大，但因为存在单条最高3MB的transcript大字段，全表扫描需要读取大量磁盘IO，耗时会指数级上升。
• 验证方法：执行EXPLAIN ANALYZE SELECT * FROM "table" WHERE author = '目标作者名';，如果输出中出现Seq Scan（顺序扫描/全表扫描）即可确认。
• 解决方法：给author字段加普通索引：

CREATE INDEX idx_table_author ON "table"(author);

注意：表名使用了SQL保留字table，实际操作需要加双引号包裹，否则会触发语法报错

2 第二排查：锁等待阻塞 #

• 如果有其他未提交的事务（比如Web应用的慢查询、未正常提交的写入事务）已经锁住了该author对应的行，你的UPDATE语句会一直等待锁释放才会执行，导致长时间无响应。
• 验证方法（以PostgreSQL为例，其他数据库语法类似）：

-- 查询正在运行的慢事务
SELECT pid, query, state, now() - xact_start AS duration 
FROM pg_stat_activity 
WHERE state = 'active' AND (query LIKE '%UPDATE%table%' OR query LIKE '%author%');
-- 查询锁等待
SELECT * FROM pg_locks WHERE granted = false;

• 解决方法：确认无业务影响的前提下，杀掉阻塞的事务PID，后续优化业务逻辑避免长事务，所有事务尽量短平快，执行完立刻提交。

3 其他优化点 #

3.1 替换字符串拼接为参数化查询 #

你当前的字符串拼接写法存在SQL注入风险，同时部分数据库无法复用执行计划，也会小幅影响性能，优化为参数化写法：

# 以psycopg2为例，其他数据库驱动语法类似
conn = connect()
cur = conn.cursor()
cur.execute("UPDATE \"table\" SET classification = %s WHERE author = %s", (classification, author))
conn.commit()

3.2 清理表膨胀 #

如果表经过大量更新删除操作，未及时清理死元组会导致表膨胀，扫描变慢，可执行VACUUM优化：

VACUUM ANALYZE "table";

3.3 大字段单独存储（可选长期优化） #

因为transcript字段非常大，平时查询更新很少用到的话，可以考虑把大字段拆分到单独的关联表，主表只存关联id，大幅降低主表的扫描成本，提升所有主表操作的性能。

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