主页 / dba / 问题 / 228244
Accepted
Hassan Baig
Asked: 2019-01-29 04:51:41 +0800 CST

优化慢速 SELECT 查询（postgresql 9.6.5）

  • 772

在一个名为 的应用程序Links中，用户在类似论坛的界面中发布有趣的内容，而其他人则在这个公开发布的内容下发表回复或评论。

这些发布的回复保存在名为links_publicreply(postgresql 9.6.5 DB) 的 postgresql 表中。

对表运行的一个查询不断出现slow_log（大于500ms）。这涉及显示在给定的共享内容下累积的最近 25 条公共回复。

这是慢速日志中的示例：

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

 SELECT "links_publicreply"."id",
       "links_publicreply"."submitted_by_id",
       "links_publicreply"."answer_to_id",
       "links_publicreply"."submitted_on",
       "links_publicreply"."description",
       "links_publicreply"."abuse",
       "auth_user"."id",
       "auth_user"."username",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar",
       "links_link"."id",
       "links_link"."description",
       "links_link"."submitter_id",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."latest_reply_id"
FROM   "links_publicreply"
       INNER JOIN "links_link"
               ON ( "links_publicreply"."answer_to_id" = "links_link"."id" )
       INNER JOIN "auth_user"
               ON ( "links_publicreply"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_publicreply"."answer_to_id" = 8936203
ORDER  BY "links_publicreply"."id" DESC
LIMIT  25

以下是explain analyze上述查询的结果：https ://explain.depesz.com/s/pVZ5

据此，索引扫描似乎占用了大约 70% 的查询时间。但是对于像我这样的临时 DBA 来说，我可以做些什么优化来提高性能并不是很明显。也许是一个综合指数links_publicreply.answer_to_id, links_publicreply.id？

如果领域专家可以提供解决此类问题的指导+直觉，这将极大地帮助我学习。

附言\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
postgresql performance

1 个回答

  1. Best Answer

    首先，尽量减少调用索引的次数。此过滤器answer_to_id = 8936203返回14740用于检查其他表的行。但是你只需要 top 25of id。如果您LIMIT 25然后JOIN其他表怎么办。

    WITH tmp_links_publicreply AS (
   SELECT ...
   FROM links_publicreply
   WHERE  "links_publicreply"."answer_to_id" = 8936203
   ORDER  BY "links_publicreply"."id" DESC
   LIMIT  25  
)
SELECT 
FROM tmp_links_publicreply t 
JOIN ... 
JOIN ...

    links_publicreply如果您在和 2 个表links_link之间有约束，则上面的查询将正常工作auth_user。为什么？假设，你LIMIT 25，然后什么都找不到，JOIN因为没有links_link相关的行answer_to_id = 8936203。

    然后，在 上创建新索引answer_to_id, id DESC。

    注意：（上面WITH的查询）称为Common Table Expressions

    • 2

相关问题