Re: Optimize Query

SELECT concat(company, ' ', customer_name_first, ' ', customer_name_last) AS customer,      sum(revenue) AS revenue,      sum(i.quantity) AS quantity,      sum(i.cost) AS cost
FROM ( SELECT account.id,          job.customerid,          job.title,          job.gps_lat,          job.gps_long,          status.label AS status,          status.status_type_id,          job.status_label_id,          client."position",          bill_item.quantity,          client.businesstype,          account.id AS clientid,          client.name_first AS customer_name_first,          client.name_last AS customer_name_last,          job.id AS jobid,          note.mobiuserid,          bill_item.for_invoicing AS invoice,          COALESCE(bill_item.unit_price, billable.unit_price, 0) AS unit_price,          note.n_quote_status,          COALESCE(bill_item.unit_cost, billable.unit_cost, 0) AS unit_cost,          job.time_job,          "user".name_first,          "user".name_last,          role.id AS roleid,          role.name AS role_name,          billable.billable_id AS taskid,          COALESCE(labs.tag, billable.code) AS task_name,          note.time_start,          client.company,          job.refnum,          (COALESCE(bill_item.unit_cost, billable.unit_cost, 0) * bill_item.quantity) AS cost,          (COALESCE(bill_item.unit_price, billable.unit_price, 0) * bill_item.quantity) AS revenue,          bill_item.for_invoicing AS invoiceable,          COALESCE(extract('epoch'                           FROM bill.ts_creation AT TIME ZONE 'UTC'), bill_item.invoice_id, NULL) IS NOT NULL AS invoiced  FROM ja_clients AS account  JOIN ja_customers AS client ON client.clientid = account.id  JOIN ja_jobs AS job ON client.id=job.customerid  JOIN ja_notes AS note ON note.jobid = job.id  JOIN dm.bill_items AS bill_item ON bill_item.bill_item_id=note.bill_item_id  LEFT JOIN dm.bills AS bill ON bill.bill_id=bill_item.bill_id  LEFT JOIN dm.invoices AS invoice ON invoice.invoice_id=bill.invoice_id  OR invoice.invoice_id=bill_item.invoice_id  LEFT JOIN dm.billables AS billable ON billable.billable_id=note.billable_id  LEFT JOIN dm.labors AS labs ON labs.billable_id = billable.billable_id  JOIN ja_mobiusers AS "user" ON "user".id = note.mobiuserid  JOIN ja_status AS status ON status.id = job.status_label_id  JOIN ja_role AS ROLE ON ROLE.id="user".user_type  WHERE note.note_type::text = ANY (ARRAY[ ('time'::CHARACTER VARYING)::text,                                           ('part'::CHARACTER VARYING)::text ])    AND NOT job.templated    AND NOT job.deleted    AND job.clientid = 6239    AND time_job >= 1438351200    AND time_job <= 1448888340    AND bill_item.for_invoicing = TRUE) AS i
LEFT JOIN (SELECT customerid,         SUM(cost) AS cost,         SUM(quantity) AS quantity  FROM    (SELECT account.id,            job.customerid,            job.title,            job.gps_lat,            job.gps_long,            status.label AS status,            status.status_type_id,            job.status_label_id,            client."position",            bill_item.quantity,            client.businesstype,            account.id AS clientid,            client.name_first AS customer_name_first,            client.name_last AS customer_name_last,            job.id AS jobid,            note.mobiuserid,            bill_item.for_invoicing AS invoice,            COALESCE(bill_item.unit_price, billable.unit_price, 0) AS unit_price,            note.n_quote_status,            COALESCE(bill_item.unit_cost, billable.unit_cost, 0) AS unit_cost,            job.time_job,            "user".name_first,            "user".name_last,            ROLE.id AS roleid,                 ROLE.name AS role_name,                      billable.billable_id AS taskid,                      COALESCE(labs.tag, billable.code) AS task_name,                      note.time_start,                      client.company,                      job.refnum,                      (COALESCE(bill_item.unit_cost, billable.unit_cost, 0) * bill_item.quantity) AS cost,                      (COALESCE(bill_item.unit_price, billable.unit_price, 0) * bill_item.quantity) AS revenue,                      bill_item.for_invoicing AS invoiceable,                      COALESCE(extract('epoch'                                       FROM bill.ts_creation AT TIME ZONE 'UTC'), bill_item.invoice_id, NULL) IS NOT NULL AS invoiced     FROM ja_clients AS account     JOIN ja_customers AS client ON client.clientid = account.id     JOIN ja_jobs AS job ON client.id=job.customerid     JOIN ja_notes AS note ON note.jobid = job.id     JOIN dm.bill_items AS bill_item ON bill_item.bill_item_id=note.bill_item_id     LEFT JOIN dm.bills AS bill ON bill.bill_id=bill_item.bill_id     LEFT JOIN dm.invoices AS invoice ON invoice.invoice_id=bill.invoice_id     OR invoice.invoice_id=bill_item.invoice_id     LEFT JOIN dm.billables AS billable ON billable.billable_id=note.billable_id     LEFT JOIN dm.labors AS labs ON labs.billable_id = billable.billable_id     JOIN ja_mobiusers AS "user" ON "user".id = note.mobiuserid     JOIN ja_status AS status ON status.id = job.status_label_id     JOIN ja_role AS ROLE ON ROLE.id="user".user_type     WHERE note.note_type::text = ANY (ARRAY[ ('time'::CHARACTER VARYING)::text,                                              ('part'::CHARACTER VARYING)::text ])       AND NOT job.templated       AND NOT job.deleted       AND job.clientid = 6239       AND time_job >= 1438351200       AND time_job <= 1448888340       AND n_quote_status = 0 ) AS note_detail_report_view  WHERE 1=1    AND clientid = 6239    AND time_job >= 1438351200    AND time_job <= 1448888340    AND n_quote_status = 0  GROUP BY customerid) AS a ON a.customerid = i.customerid
WHERE 1=1 AND clientid = 6239 AND time_job >= 1438351200 AND time_job <= 1448888340 AND invoice = TRUE
GROUP BY customer,        a.cost,        a.quantity
ORDER BY revenue DESC