主页 / dba / 问题 / 228197
Accepted
Hassan Baig
Asked: 2019-01-28 13:01:19 +0800 CST

简单 SELECT 查询的查询性能较慢 (Postgresql 9.6)

  • 772

在一个名为“链接”的应用程序中，用户发布他们最近发现的有趣内容的链接（以及对上述帖子的其他投票）。

这些发布的链接保存在links_link我的 postgresql 9.6.5 数据库的一个表中。

表中一个看似无关紧要的SELECT查询links_link始终出现在slow_log. 它花费的时间超过 500 毫秒，并且比我在大多数其他 postgresql 操作中遇到的慢 10 倍。

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

日志：持续时间：8648.676 毫秒语句：

SELECT "links_link"."id",
       "links_link"."description",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."net_votes"
FROM   "links_link"
WHERE  "links_link"."submitter_id" = 811645
ORDER  BY "links_link"."id" DESC
LIMIT  1

查看explain analyze结果：https ://explain.depesz.com/s/Xp5v

查询最终根据那个过滤了 14,331,127 行！

我试过的：

我的直觉是 Postgres 将此查询计划基于误导性统计数据。因此，我VACUUM ANALYZE在上述桌子上跑步。然而这并没有改变任何东西。

作为某种偶然的 DBA，我正在寻找有关该主题的一些快速专家指导。在此先感谢并为 noob 问题（如果是）道歉。

编辑：

以下是 的完整输出\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_latest_reply_id_idx" btree (latest_reply_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 performance

1 个回答

  1. Best Answer

    我可以想象一个索引在links_link (submitter_id, id DESC)这里很有帮助。

    CREATE INDEX CONCURRENTLY link_id_submitter_id
                          ON links_link (submitter_id, id DESC);

    可以遍历快速找到 的子树submitter_id = 811645。然后可以立即选择第一个节点以获得按 排序的第一条记录id DESC。

    • 2

相关问题