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 ?
1 Answer 1
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.
lang-sql