Re: Optimize Query

Hi Alban! Sorry.. that was my mistake

Original 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 costFROM
( 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 iLEFT 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.customeridWHERE 1=1
AND clientid = 6239
AND time_job >= 1438351200
AND time_job <= 1448888340
AND invoice = TRUEGROUP BY customer,
a.cost,
a.quantityORDER BY revenue DESC

Explain analyze link:
http://explain.depesz.com/s/5WJy