Postgresql – Optimize performance for query with CTE using a multicolumn index

optimizationperformancepostgresqlpostgresql-9.2query-performance

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;

Explain analyze link: http://explain.depesz.com/s/kjcp

Is there any way to improve it?

UPDATE 1: table 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")

UPDATE 2: After the index on ja_feedlog:

Total runtime: 800.000 ms (The total runtime on the explain analyze link is wrong – I think is because of the cache

Explain analyze link: http://explain.depesz.com/s/yyr

It is a really significant improvement.. But there's a lot of more to do… Any idea?

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

UPDATE 3: Ran explain analyze buffers

Link: http://explain.depesz.com/s/tHSG

By creating the index it really helped, but still is not good enough.

Any idea about how improve that Total Time ?

Best Answer

1 - Query was introduced to this website

2 - An index has been created to improve the Query:

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

3 - The query was fast, but not enough. So the index was re-created:

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

By doing that, the Total time is: 80 ms.

Related Question