主页 / dba / 问题 / 252955
Accepted
Hassan Baig
Asked: 2019-11-12 04:02:06 +0800 CST

加速非常慢的 UPDATE 查询（Postgresql 9.6）

  • 772

在一个名为 的应用程序Links中，用户发布他们最近发现的有趣内容，其他人可以对这些帖子发表评论。

还有一项附加功能，允许用户对评论发表评论。

所有评论（和评论的评论）都保存links_publicreply在我的 postgresql 9.6.5 DB 中名为的表中。该表包含一个自引用外键来支持comments on comments。这是一张大桌子，有近 2 亿行。

表上的一个UPDATE查询links_publicreply始终显示在slow_log. 它花费的时间超过 5000 毫秒，并且比我在大多数其他 postgresql 操作中遇到的慢约 100 倍。

这是我的慢日志中相应 SQL 的示例：

UPDATE "links_publicreply"
SET "direct_reply_tgt_text_prefix"='',
    "direct_reply_tgt_text_postfix"=''
WHERE "links_publicreply"."direct_reply_id"=175054159;

查看解释分析结果：https ://explain.depesz.com/s/G8wX

据此，Seq Scan 最终过滤了 47,535,365 行，是缓慢的根源。

我该怎么做才能减少这种过高的执行时间？

作为一个偶然的 DBA，我不是这方面的专家。我的直觉是我正在过滤自引用外键，所以它应该已经是一个索引（因此针对查找进行了优化）？我在这里有点难过。

添加：

以下是 的完整输出\d links_publicreply：

            Column             |           Type           |                           Modifiers                            
-------------------------------+--------------------------+----------------------------------------------------------------
 id                            | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id               | integer                  | not null
 answer_to_id                  | integer                  | not null
 submitted_on                  | timestamp with time zone | not null
 description                   | text                     | not null
 category                      | character varying(20)    | not null
 seen                          | boolean                  | not null
 abuse                         | boolean                  | not null
 direct_reply_tgt_uname        | text                     | 
 direct_reply_tgt_text_prefix  | text                     | 
 direct_reply_tgt_text_postfix | text                     | 
 direct_reply_id               | integer                  | 
 level                         | integer                  | 
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "id_answer_to_id" btree (answer_to_id, id DESC)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
postgresql performance

1 个回答

  1. Best Answer

    您应该创建一个 B 树索引：

    CREATE INDEX ON links_publicreply (direct_reply_id);

    注意：您的索引links_publicreply_answer_to_id是无用的，应该删除。

    • 3

相关问题