主页 / dba / 问题 / 228213
Accepted
Hassan Baig
Asked: 2019-01-28 23:34:31 +0800 CST

加速缓慢的 SELECT 查询（Postgresql 9.6）

  • 772

在名为Links的应用程序中，用户可以发布他们最近发现的有趣内容的照片（其他人可以对这些照片进行投票或评论）。

这些张贴的照片保存在links_photo我的 postgresql 9.6.5 数据库的一个表中。

每个用户的个人资料显示他们发布的照片​​ - 按10每个对象分页 - 排序upload time。

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

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

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

SELECT "links_photo"."id",
       "links_photo"."owner_id",
       "links_photo"."image_file",
       "links_photo"."upload_time",
       "links_photo"."comment_count",
       "links_photo"."vote_score",
       "links_photo"."caption",
       "links_photo"."category",
       "auth_user"."id",
       "auth_user"."username",
       "auth_user"."date_joined",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar"
FROM   "links_photo"
       INNER JOIN "auth_user"
               ON ( "links_photo"."owner_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  ( "links_photo"."owner_id" = 78689
         AND "links_photo"."category" = '1' )
ORDER  BY "links_photo"."upload_time" DESC
LIMIT  10 offset 10

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

据此，向后索引扫描最终过滤了 1,196,188 行，这是缓慢的根源。

我认为我应该尝试的是：

作为某种偶然的 DBA，我正在寻找有关该主题的一些快速专家指导。我原以为有一个索引upload_time就足够了，但事实并非如此。那么也许是复合的(owner_id, upload_time DESC)？

添加：

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

                                           Table "public.links_photo"
          Column          |           Type           |                        Modifiers                         
--------------------------+--------------------------+----------------------------------------------------------
 id                       | integer                  | not null default nextval('links_photo_id_seq'::regclass)
 owner_id                 | integer                  | not null
 image_file               | character varying(100)   | not null
 upload_time              | timestamp with time zone | not null
 comment_count            | integer                  | not null
 is_public                | boolean                  | not null
 vote_score               | integer                  | not null
 visible_score            | integer                  | not null
 invisible_score          | double precision         | not null
 caption                  | character varying(100)   | 
 avg_hash                 | character varying(16)    | not null
 latest_comment_id        | integer                  | 
 second_latest_comment_id | integer                  | 
 category                 | character varying(11)    | not null
 device                   | character varying(10)    | not null
Indexes:
    "links_photo_pkey" PRIMARY KEY, btree (id)
    "links_photo_latest_comment_id" btree (latest_comment_id)
    "links_photo_owner_id" btree (owner_id)
    "links_photo_second_latest_comment_id" btree (second_latest_comment_id)
    "links_photo_upload_time" btree (upload_time)
Foreign-key constraints:
    "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    "links_photo_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_photostream" CONSTRAINT "cover_id_refs_id_d62b783f" FOREIGN KEY (cover_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photocomment" CONSTRAINT "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "links_photoobjectsubscription_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photovote" CONSTRAINT "links_photovote_photo_id_fkey" FOREIGN KEY (photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photo_which_stream" CONSTRAINT "photo_id_refs_id_916b4355" FOREIGN KEY (photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
postgresql performance

1 个回答

  1. Best Answer

    我有和你一样的情况。问题是Rows Removed by Filter，查询需要时间通过其过滤器删除行。根据要删除的行数，时间将是相等的。

    想法 1

    你的方法很好。小事是查询需要删除category. 但是，如果你的基数category不多，这不是一个大问题。

    想法 2

    我建议在owner_id, category, upload_time DESC. 它会避免the filter。但是，在大表的情况下，您应该考虑索引的大小。

    假设我们有表（db<>fiddle）

    CREATE TABLE links_photo 
(
  id serial, 
  owner_id int, 
  category int, 
  upload_time timestamp without time zone
);

    和查询

    SELECT id
FROM links_photo 
WHERE owner_id = 100 and category = 2 
ORDER BY upload_time desc 
LIMIT 10 
OFFSET 0;

    使用说明upload_time DESC

    使用说明owner_id, upload_time DESC

    使用说明owner_id, category, upload_time DESC

    • 2

相关问题