sql - postgresql UPDATE 查询永远占用 300 万行

Question

这是表架构:

CREATE TABLE public.page_by_category
(
    id integer NOT NULL DEFAULT nextval('page_by_category_id_seq'::regclass),
    page_id bigint NOT NULL,
    category_id bigint NOT NULL,
    weight integer NOT NULL,
    CONSTRAINT id_pk PRIMARY KEY (id),
    CONSTRAINT category_id_fkey FOREIGN KEY (category_id)
    CONSTRAINT page_id_fkey FOREIGN KEY (page_id)
)

这是需要很长时间的查询: 更新 page_by_category SET weight=0 在 3m 行上。
查看 pg_stat_activity 这是结果:

30366   "2 days 18:32:12.141453"    "user"  "UPDATE page_by_category SET weight=0"

如何检查查询是否卡住？由于没有 IO，CPU 使用率很高……在我的 Centos 上，我正在使用 top，iotop 来查看是否使用了 cpu 或磁盘，但仅使用 5% 以上……
PostgreSQL 版本:""x86_64-pc-linux-gnu 上的 PostgreSQL 10.7"

Answer 1

由于您的挂起更新的进程 ID 是 30366，您应该查找持有阻塞语句的锁的打开事务:

SELECT pg_blocking_pids(30366);

找出这些连接有什么问题以及为什么它们保持锁定这么长时间。
要杀死他们，请运行

SELECT pg_terminate_backend(?????);

哪里 ?????是通过上述查询找到的阻塞进程 ID 之一。
如果不是锁阻止了您的查询，则以下可能性仍然存在:

你的存储速度非常慢

你有昂贵的行级触发器

你有很多索引