優化 SELECT 查詢始終顯示在慢日誌中

在一個名為“連結”的應用程序中，使用者發布他們最近發現的有趣內容的連結和照片（以及其他人對所述文章的評論）。

照片下的這些張貼評論保存在links_photocomment我的 postgresql 9.6.5 數據庫中的一個表中。

表上的一個SELECT查詢links_photocomment始終顯示在slow_log. 它花費的時間超過 500 毫秒，並且比我在大多數其他 postgresql 操作中遇到的慢約 10 倍。

這是我的慢日誌中相應 SQL 的範例：

日誌：持續時間：5071.112 毫秒聲明：

SELECT "links_photocomment"."abuse",
      "links_photocomment"."text",
      "links_photocomment"."id",
      "links_photocomment"."submitted_by_id",
      "links_photocomment"."submitted_on",
      "auth_user"."username",
      "links_userprofile"."score"
FROM   "links_photocomment"
      INNER JOIN "auth_user"
              ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
      LEFT OUTER JOIN "links_userprofile"
                   ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_photocomment"."which_photo_id" = 3115087
ORDER  BY "links_photocomment"."id" DESC
LIMIT  25;

查看explain analyze結果：https ://explain.depesz.com/s/UuCk

該查詢最終根據該過濾了 19,100,179 行！

我試過的：

我的直覺是 Postgres 這個查詢計劃基於誤導性的統計數據。因此，我在這張VACUUM ANALYZE桌子上奔跑。然而，這並沒有改變任何東西。

作為一個偶然的 DBA，我正在尋找有關該主題的一些快速專家指導。提前感謝並為菜鳥問題道歉（如果是的話）。

附錄：

以下是 的完整輸出\d links_photocomment：

                                     Table "public.links_photocomment"
    Column      |           Type           |                            Modifiers                            
-----------------+--------------------------+-----------------------------------------------------------------
id              | integer                  | not null default nextval('links_photocomment_id_seq'::regclass)
which_photo_id  | integer                  | not null
text            | text                     | not null
device          | character varying(10)    | not null
submitted_by_id | integer                  | not null
submitted_on    | timestamp with time zone | not null
image_comment   | character varying(100)   | not null
has_image       | boolean                  | not null
abuse           | boolean                  | default false
Indexes:
   "links_photocomment_pkey" PRIMARY KEY, btree (id)
   "links_photocomment_submitted_by_id" btree (submitted_by_id)
   "links_photocomment_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
   "links_photocomment_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
   "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
   TABLE "links_photo" CONSTRAINT "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
   TABLE "links_report" CONSTRAINT "links_report_which_photocomment_id_fkey" FOREIGN KEY (which_photocomment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
   TABLE "links_photo" CONSTRAINT "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED

該計劃不使用索引，(which_photo_id)而是使用 PK(id)索引，因此它必須讀取索引的很大一部分（如果匹配過濾器的行少於 25 行，則全部讀取）。這在具體執行中大約需要 4.4 秒（並在讀取和拒絕 19M 行後找到這 25 行）：

-> Index Scan Backward using links_photocomment_pkey on links_photocomment  
   (cost=0.57..2,819,246.22 rows=7,195 width=41)
   (actual time=555.830..4,929.154 rows=25 loops=1)

   Filter: (which_photo_id = 3115087)
   Rows Removed by Filter: 19100179

我會嘗試這些：

• 用索引替換(which_photo_id)索引 on (which_photo_id, id)。
• INNER將連接重寫為LEFT連接（有一個FOREIGN KEY約束確保兩個查詢將產生相同的結果。）
• 用子查詢（派生表或 CTE）重寫，將WHERE過濾器移到內部），以便首先獲取 25 個 id（希望僅使用索引掃描），然後加入其他 2 個表。

查詢（帶派生表）：

SELECT "links_photocomment"."abuse",
      "links_photocomment"."text",
      "links_photocomment"."id",
      "links_photocomment"."submitted_by_id",
      "links_photocomment"."submitted_on",
      "auth_user"."username",
      "links_userprofile"."score"
FROM   
      (
        SELECT id
        FROM links_photocomment
        WHERE which_photo_id = 3115087
        ORDER BY id DESC
        LIMIT 25
      ) AS lim
      INNER JOIN "links_photocomment"
          ON ( "links_photocomment"."id" = lim.id )
      LEFT OUTER JOIN "auth_user"
          ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
      LEFT OUTER JOIN "links_userprofile"
          ON ( "auth_user"."id" = "links_userprofile"."user_id" )
ORDER  BY lim.id DESC
LIMIT  25;

查詢（使用 CTE）：

WITH lim AS
      (
        SELECT id
        FROM links_photocomment
        WHERE which_photo_id = 3115087
        ORDER BY id DESC
        LIMIT 25
      )
SELECT "links_photocomment"."abuse",
      "links_photocomment"."text",
      "links_photocomment"."id",
      "links_photocomment"."submitted_by_id",
      "links_photocomment"."submitted_on",
      "auth_user"."username",
      "links_userprofile"."score"
FROM   
      lim
      INNER JOIN "links_photocomment"
          ON ( "links_photocomment"."id" = lim.id )
      LEFT OUTER JOIN "auth_user"
          ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
      LEFT OUTER JOIN "links_userprofile"
          ON ( "auth_user"."id" = "links_userprofile"."user_id" )
ORDER  BY lim.id DESC
LIMIT  25;

引用自：https://dba.stackexchange.com/questions/228485