使用多列索引通過 CTE 優化查詢性能

我有這個慢查詢：

WITH flks AS
 (SELECT id
  FROM public.ja_feedlog
  WHERE gtime BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2016-01-24')::BIGINT
                  AND EXTRACT(EPOCH FROM TIMESTAMP '2016-03-25')::BIGINT
    AND clientid=14635
    AND log_type NOT IN (2, 15,16)
  ORDER BY gtime DESC, log_type, id
  LIMIT 21
  OFFSET 0)
SELECT fls.id AS feedid,
      fls.description AS status_desc,
      fls.invoiceid AS feedinvoice,
      fls.gtime,
      fls.log_Type,
      fls.serialised_data,
      j.id AS jobid,
      j.title,
      j.refnum,
      j.job_number,
      j.time_job,
      j.priority,
      j.address AS j_address,
      j.suburb AS j_suburb,
      j.city AS j_city,
      j.postcode AS j_postcode,
      j.recurrenceid,
      n.id AS noteid,
      n.description AS note_desc,
      j.text_notes_count AS note_count,
      j.charges_count + j.parts_count AS pl_count,
      m.id AS mobileuserid,
      concat(m.name_first, ' ', m.name_last) AS mobiiname,
      m.staff_colour,
      c.id AS custid,
      concat(c.name_first, ' ', c.name_last) AS customername,
      c.company AS custcompany,
      c.address AS cust_address,
      st.label AS customstatuslabel,
      st.status_type_id,
      st.status_type_id,
      fls.requestorid
FROM flks
JOIN ja_feedlog AS   fls ON flks.id = fls.id
LEFT JOIN ja_mobiusers m ON m.id = fls.mobiuserid
LEFT JOIN ja_jobs      j ON j.id = fls.jobid
LEFT JOIN ja_status   st ON st.id = j.status_label_id
LEFT JOIN ja_notes     n ON n.id = fls.invoiceid
LEFT JOIN ja_customers c ON c.id = fls.customerid;

解釋分析連結：http ://explain.depesz.com/s/kjcp

有什麼辦法可以改善嗎？

**更新 1：**表 ja_feedlog：

   CREATE TABLE public.ja_feedlog (
     id integer NOT NULL DEFAULT "nextval"('"ja_feedlog_id_seq"'::"regclass"),
     clientid bigint,
     mobiuserid bigint,
     customerid bigint,
     invoiceid bigint,
     description character varying(1024),
     gtime bigint,
     jobid bigint,
     log_type smallint,
     serialised_data "text",
     push_status smallint DEFAULT 0,
     requestorid bigint,
     the_geom "geometry",
     admin_read smallint NOT NULL DEFAULT 0,
     visitid bigint,
     CONSTRAINT pk_feedlog PRIMARY KEY ("id")
   );

Indexes:
   "pk_feedlog" PRIMARY KEY, "btree" ("id")
   "ix-gtime" "btree" ("gtime")
   "ix_client" "btree" ("clientid")
   "ix_client_time" "btree" ("clientid", "gtime")
   "ix_customer_job" "btree" ("customerid")
   "ix_feedlog_client_list_ordered" "btree" ("id", "clientid")
   "ix_feedlog_client_time_notif" "btree" ("clientid", "gtime", "log_type") WHERE "admin_read" <> 1
   "ix_feedlog_client_time_notif2" "btree" ("clientid", "gtime" DESC) WHERE 
CASE
   WHEN "log_type" = ANY (ARRAY[104, 56, 103, 55]) THEN "description"::"text" ~~* '%user accept%'::"text" OR "description"::"text" ~~* '%user reject%'::"text"
   ELSE true
END AND ("log_type" = ANY (ARRAY[104, 56, 103, 55, 130, 90])) AND "admin_read" <> 1
   "ix_feedlog_client_time_type" "btree" ("clientid", "gtime", "log_type")
   "ix_feedlog_customer_time_type" "btree" ("customerid", "gtime", "log_type")
   "ix_feedlog_gtimes_desc" "btree" ("gtime" DESC)
   "ix_feedlog_job_time_type" "btree" ("jobid", "gtime", "log_type")
   "ix_feedlog_mobiuserids" "btree" ("mobiuserid")
   "ix_feedlog_requestorid_most_recent" "btree" ("requestorid", "gtime" DESC)
   "ix_feedlog_requestorids" "btree" ("requestorid")
   "ix_feedlog_user_time_type" "btree" ("mobiuserid", "gtime", "log_type")
   "ix_ja_feedlog_gsdi_pk" "btree" (("id"::"text"))
   "ix_ja_feedlog_visitids" "btree" ("visitid")
   "ix_job" "btree" ("jobid")
   "ix_job_log_type" "btree" ("jobid", "log_type")
   "ix_log_type_feedlog" "btree" ("log_type")
   "ix_push_status" "btree" ("push_status")

**更新2：**在索引之後ja_feedlog：

總執行時間：800.000 毫秒（解釋分析連結上的總執行時間是錯誤的 - 我認為是因為記憶體

解釋分析連結：http ://explain.depesz.com/s/yyr

這是一個非常顯著的改進。但是還有很多事情要做…知道嗎？

CREATE INDEX ix_feedlog_client_time_notif_id
ON public.ja_feedlog (clientid, gtime DESC, log_type, id);

更新 3： Ran 解釋分析緩衝區

連結：http ://explain.depesz.com/s/tHSG

通過創建索引它確實有所幫助，但仍然不夠好。

關於如何提高總時間的任何想法？

**1 -**該網站引入了查詢

**2 -**已創建索引以改進查詢：

CREATE INDEX CONCURRENTLY ix_feedlog_client_time_notif_id ON public.ja_feedlog USING BTREE ("clientid","gtime" DESC, "log_type", "id");

• 索引前總時間：346507.823 ms
• 索引後總時間：625.375 ms

**3 -**查詢速度很快，但還不夠。所以重新創建了索引：

CREATE INDEX CONCURRENTLY ix_feedlog_client_time_notif_id ON public.ja_feedlog USING BTREE ("clientid","gtime" DESC, "log_type", "id") WHERE "log_type" <> ALL ('{2,15,16}'::integer[]);

通過這樣做，總時間為：80 ms。

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