Slow Query Postgres 9.2

Question

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

Answer 1

It looks like the join between ja_feedlog and ja_jobs is the culprit (it appears to be taking most of the time doing a filtered indexed scan on ja_jobs for each ja_feedlog resulting row). In cases like this where there are two joined heavily filtered large tables, I find it useful identifying which set of filter conditions will render the least rows on one of the tables, and separate that/those one/s into an indexed temporary table(s) for joining later.

I have assumed that the table ja_jobs, with those filter conditions, will produce a reasonably small rowset in a reasonably small time. If that is the case, you may want to try the following:

CREATE TEMP TABLE myjobs AS
  SELECT *
  FROM public.ja_jobs
  WHERE NOT j.deleted AND NOT j.templated AND ((((j.time_job <= 1461240000) AND (j.time_arrival >= 1460462400))));

CREATE UNIQUE INDEX ON myjobs (id);

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_feedlog AS fl
JOIN myjobs AS j ON (j.id = fl.jobid)
FROM public.ja_status AS s ON (j.status_label_id = s.id)
JOIN public.ja_customers AS c ON (c.id = j.customerid)
LEFT JOIN public.ja_customers AS bc ON (bc.id = j.bill_customer)
LEFT JOIN public.ja_mobiusers AS u ON (u.id = fl.requestorid)

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

ORDER BY j.id, gtime
LIMIT 50000

Also, for inner joins, it is clearer if filter conditions that are not strictly part of the joining condition are specified in the where clause rather than the join, for clarity and for making debugging easier. Finally, even if it doesn't affect the final plan, for readability and clarity I place in the FROM whatever table it will be the "carrier" table, i.e. the one that will take the biggest filtering effort, and "hang" from it all the dependent ones, like small master tables, since I find it easier commenting these out for debugging.