I've got the following query:
SELECT
j.id,
concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client,
c.email AS client_email,
concat(bc.company,' ', bc.name_first, ' ', bc.name_last) AS bclient,
j.title,
j.refnum,
j.job_number,
j.time_job,
j.time_arrival,
j.address,
j.suburb,
j.city,
j.stpr,
j.postcode,
j.priority,
j.status_label_id,
j.description,
u.name_first AS staff_first,
u.name_last AS staff_last,
fl.serialised_data,
fl.gtime,
j.created_date
FROM
public.ja_status AS s
JOIN
public.ja_jobs AS j
ON
j.status_label_id = s.id
AND NOT j.deleted
AND j.templated = false
AND ((((j.time_job <= 1461240000) AND (j.time_arrival >= 1460462400))))
JOIN
public.ja_customers AS c
ON
c.id = j.customerid
LEFT JOIN
public.ja_customers AS bc
ON
bc.id = j.bill_customer
JOIN
public.ja_feedlog AS fl
ON
fl.jobid = j.id
AND fl.clientid = 19233
AND fl.log_type IN (5,101,6,102)
AND (
fl.log_type IN (5,101)
OR (
fl.description LIKE '%status_change%'
AND fl.log_type IN (6,102)
)
)
LEFT JOIN
public.ja_mobiusers AS u
ON
u.id = fl.requestorid
ORDER BY
j.id, gtime
LIMIT
50000
Explain analyze: http://explain.depesz.com/s/25Wj
How can I improve it? The total time is absolutely huge!
It seems the major problem is on ja_feedlog table:
-> Bitmap Heap Scan on "ja_feedlog" "fl" (cost=230474.49..397722.58 rows=33256 width=952)
(actual time=307.690..290532.037 rows=323605 loops=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")
)