Re: Optimize Query

On Wed, Feb 10, 2016 at 8:25 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
wrote:

> Hi all,
>
> I've got a slow query and I'm trying to make it faster.
>
> *New Query:*
>
> 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 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 job.time_job >= 1438351200
>> AND job.time_job <= 1448888340
>> AND bill_item.for_invoicing = TRUE
>> GROUP BY customer
>> ORDER BY revenue DESC;
>
>
> *The original query has:*
>
> SELECT $cols $ec , sum(revenue) as revenue, $cost_cols
>> FROM (".note_detail_report_view(). ") AS i
>> LEFT JOIN (
>> SELECT $join_col , SUM(cost) AS cost, SUM(quantity) AS quantity
>> FROM (".note_detail_report_view(). ") AS note_detail_report_view
>> $whereClause AND *n_quote_status = 0*
>> GROUP BY $join_col
>> ) AS a
>> ON $joiner
>> $whereClause AND invoice = true $limit_inv
>> GROUP BY $group_by $ec, a.cost , a.quantity
>> ORDER BY $order_by
>
>
> I just need the a-case. i and a look very similar, except A with an
> additional filter: *n_quote_status = 0*
>
> How can I re-write that using the A case?
>
> Thanks
>

FYI, it is always helpful (and polite) to state the PostgreSQL VERSION and
O/S for archive documentation purposes!

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;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.