Re: Optimize Query

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Wed, Feb 10, 2016 at 8:25 PM, drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> 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.