Adding an index to a cte

Feedback


Question:

I’ve got this slow query:

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;

Provide an explanation, analysis, and reference by visiting http://explain.depesz.com/s/kjcp.

Is there any way to improve it?

Firstly, there is an update to report. Secondly, there is a table named “ja_feedlog” that requires attention.

    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")

Following the modification made to

ja_feedlog

index, as stated in UPDATE 2:

The overall execution time, which seems to be inaccurate due to caching, is reported as 800,000 ms on the explain analyze link.

Provide an explanation and analysis of the link found at http://explain.depesz.com/s/yyr.

The improvement is quite significant, but there is still a lot more work to be done. Any suggestions on how to proceed?

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

In the third update, an explanation was provided by analyzing buffers.

Follow this link to access an explanation of the data presented: http://explain.depesz.com/s/tHSG.

Despite the helpfulness of the index, it is still inadequate.

Do you have any suggestions on how to enhance the overall duration?



Solution:

1- The website was updated with the introduction of a query.

A Query enhancement index has been established.

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

    before

    the index: 346507.823 ms

  • Total time

    after

    the index: 625.375 ms

The query was speedy, however, it was insufficient, prompting the recreation of the index.

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[]);

Upon completion of the task, the cumulative duration amounts to 80 milliseconds.

Frequently Asked Questions