主页 / dba / 问题 / 186937
Accepted
Hassan Baig
Asked: 2017-09-27 08:18:46 +0800 CST

postgres 9.3.10 表上的简单维护/删除查询性能缓慢

  • 772

我有一个名为“链接”的 reddit 类型的应用程序，其中 1) 用户发布有趣的超链接，以及 2) 其他用户公开回应此类帖子。前者links_link在我的 postgresql 9.3.10 DB 中定义为表，后者是links_publicreply.

我注意到这个 Web 应用程序的数据库中的大多数表的删除速度都非常快。

然而，links_publicreply是有问题的。例如，我刚刚从该表中删除了 238 行，并等待约 20 分钟以完成。这也不例外。一年多以来，它更像是规则。

现在坦白说，这是一张大桌子（约 7500 万行）。它运行的硬件有 16 个内核和 120 GB 内存。我一直在监控服务器的性能——那里没有瓶颈，远非如此。

在这里查看解释分析结果：https ://explain.depesz.com/s/ATwE似乎时间被顺序扫描消耗掉了。

此外，这里的输出\d links_publicreply：

                                      Table "public.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
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "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_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    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

有什么方法可以让我以不同的方式执行这样的查询吗？我需要对这张桌子进行维护——但时间太长了。请就我在这里的所有选项（如果有）提供建议。

如果它很重要，还可以在此处添加输出\d links_link：

                                         Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 rank_score           | double precision         | not null
 url                  | character varying(250)   | not null
 cagtegory            | character varying(25)    | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 device               | character varying(10)    | default '1'::character varying
 latest_reply_id      | integer                  | 
 which_photostream_id | integer                  | 
 is_visible           | boolean                  | default true
 net_votes            | integer                  | default 0
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "link_whichphotostreamid_fkey" FOREIGN KEY (which_photostream_id) REFERENCES links_photostream(id) ON UPDATE CASCADE ON DELETE CASCADE
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_link_id_fkey" FOREIGN KEY (which_link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE
postgresql postgresql-9.3

1 个回答

  1. Best Answer

    顺序扫描仅需 23 秒。关键是看计划全文，里面显示了缺失的时间：

    Trigger for constraint publicreplyposter_link_fkey: time=766392.766 calls=238

    （explain.depesz.com 在标记来自非选择查询的计划方面做得并不好）

    所以，看起来你缺少一个create index on links_link (latest_reply_id).

    这意味着您从中删除的每一行都links_publicreply必须进行全表扫描，links_link以确保它没有引用该行。

    • 1

相关问题