FYI, it is always helpful (and polite) to state the PostgreSQL VERSION and O/S for archive documentation purposes!
That's right. My mistake... I'm using PostgreSQL 9.2.
Note that various postgresql.conf options, system memory & hardware also play a factor here, in addition to current table statistics.
That being said, try the following:
WITH jobs AS ( SELECT id, customerid, status_label_id FROM ja_jobs WHERE NOT templated AND NOT deleted AND clientid = 6239 AND time_job >= 1438351200 AND time_job <= 1448888340 ) SELECT concat(client.company, ' ', client.name_first, ' ', client.name_last) AS customer, sum(COALESCE(bill_item.unit_price, billable.unit_price, 0) * bill_item.quantity) AS revenue, sum(bill_item.quantity) AS quantity, sum(COALESCE(bill_item.unit_cost, billable.unit_cost, 0) * bill_item.quantity) AS cost FROM ja_clients AS account JOIN ja_customers AS client ON client.clientid = account.id JOIN jobs AS job ON job.customerid = client.id 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 bill_item.for_invoicing = TRUE GROUP BY customer ORDER BY revenue DESC;
Thank you Melvin.
Sorry but I was unable to see the n_quote_status = 0