首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >PostgreSQL删除查询挂起(解释分析!)

问PostgreSQL删除查询挂起(解释分析!)
EN

Database Administration用户
提问于 2017-03-09 22:35:54
回答 2查看 19.1K关注 0票数 5

我试图从匹配查询的表中删除一组行。我的查询的一般形式是:

DELETE FROM mytable WHERE _id IN (SELECT _id FROM mytable WHERE ...);

_id列是一个SERIAL PRIMARY KEY。

如果我单独运行内部SELECT查询,它将在大约1秒内运行,并返回大约100,000行。但是,当我将DELETE调用添加到它上时,它似乎只是坐以待毙。然后滚开。我让它运行了大约一分钟,然后取消了它,假设没有任何进展。

我把EXPLAIN ANALYZE添加到了它的前面,看看我是否可以检查什么花费了这么长时间,但是这个调用也挂了很长一段时间。

有人能告诉我发生了什么事吗?我想要删除的行很快就能识别出来,但删除它们却需要不确定的时间吗?

更新:完整的查询如下。

代码语言:javascript
复制
DELETE FROM cards 
WHERE _id IN (
    SELECT _id
    FROM cards
    LEFT JOIN game_results ON game_results.card_id = cards._id 
    WHERE NOT available
    AND game_id IS NULL
)

这些表格的简化版本如下:

代码语言:javascript
复制
CREATE TABLE cards (_id INTEGER PRIMARY KEY, available BOOLEAN);
CREATE TABLE games (_id INTEGER PRIMARY KEY);
CREATE TABLE game_results (game_id INTEGER REFERENCES games, card_id INTEGER REFERENCES cards);

我确信这个查询最终将完成运行,但考虑到检索所有要删除的is的速度如此之快,我对它花费了多长时间感到惊讶。

解决方案:

问题是三倍!

  1. 我以前吃过一个(很慢!)运行由中止脚本触发的查询。即使脚本已被中止,查询仍在运行。因此,尝试删除索引之类的操作被锁定,等待查询完成。使用pg_cancel_backend手动取消该查询允许我尝试删除索引和外键约束。
  2. 外键限制似乎是减缓一切的问题。事实上,game_results表引用了cards,这使得删除永远需要时间。有趣的是,我只是删除了game_results中显式未使用的卡片,但是当然这并没有阻止外键检查在删除时发生!这使得事情发展得很慢。在运行delete之前,我删除了外键约束,这加快了我认为合适的级别。
  3. 无论出于什么原因,接受答案中的第二个delete查询运行得比第一个快得多。

通过合并这三件事,我可以在几秒钟内完成整个删除,而在我将这三个因素组合在一起之前,我的查询运行到5-10分钟(在我取消它们之前)。

感谢大家的帮助!

EN

回答 2

Database Administration用户

发布于 2017-03-09 23:34:19

nothing nothing is 阻塞 the DELETE:

查看pg_locks,您将看到哪些锁是授予的,哪些进程正在等待获得锁。一个好的查询开始寻找锁问题:选择relation::regclass,*从pg_locks获得;

票数 5
EN

Database Administration用户

发布于 2017-03-09 23:02:11

这不太理想。

代码语言:javascript
复制
DELETE FROM cards 
WHERE _id IN (
    SELECT _id
    FROM cards
    LEFT JOIN game_results ON game_results.card_id = cards._id 
    WHERE NOT available
    AND game_id IS NULL
);

相反,尝试DELETE ... USING

代码语言:javascript
复制
DELETE FROM cards
USING game_results AS gr
WHERE _id = gr.card_id
  AND NOT gr.available
  AND gr.game_id IS NULL;

是的,我也写了一个类似的评论。我试图通过对游戏结果做左连接,然后检查空行,找出任何在游戏中从未使用过的“卡片”。- aardvarkk 1分钟前

如果这就是你想要做的,那就用NOT EXISTS吧。

代码语言:javascript
复制
DELETE FROM cards
WHERE NOT available
AND NOT EXISTS (
    SELECT *
    FROM game_results
    WHERE game_results.card_id = _id 
);
票数 2
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/166747

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档