Home > mailing lists

Thread: Optimize Query

Optimize Query

From

"drum.lucas@gmail.com"

Date:

11 February 2016, 01:26:21

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

Re: Optimize Query

From

Melvin Davidson

Date:

11 February 2016, 02:23:24

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

Re: Optimize Query

From

"drum.lucas@gmail.com"

Date:

11 February 2016, 02:39:22

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

Did you use it?

Re: Optimize Query

From

John R Pierce

Date:

11 February 2016, 04:09:51

On 2/10/2016 6:38 PM, drum.lucas@gmail.com wrote:

Sorry but I was unable to see the n_quote_status = 0

I'm unable to see this variable anywhere in your two original queries, the SQL one, and the other ?? abbreviated thing, nor did you give any table definitions, so I'm not even sure what you mean by n_quote_status

-- 
john r pierce, recycling bits in santa cruz

Re: Optimize Query

From

"drum.lucas@gmail.com"

Date:

13 February 2016, 10:21:29

Anyone can help with that please?

Cheers

On Thursday, 11 February 2016, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:

oh ok!

thanks

Lucas Possamai

- kinghost.co.nz
- DigitalOcean

On 11 February 2016 at 15:41, Melvin Davidson <melvin6925@gmail.com> wrote:

Thank you Melvin.
Sorry but I was unable to see the n_quote_status = 0

Did you use it?

No, I just revised you "new" query to be more efficient.

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

Lucas Possamai

- kinghost.co.nz

- DigitalOcean

Re: Optimize Query

From

Alban Hertroys

Date:

14 February 2016, 09:56:44

> On 13 Feb 2016, at 11:21, drum.lucas@gmail.com wrote:
>
> Anyone can help with that please?
>
> Cheers

What would help is:
1. to post an actual query that you need optimised and
2. an explain analyze of that query.

What you posted in your original message was some kind of query-template with enough placeholders and views that there
isno way to predict how that's going to perform without at least knowing what goes into the placeholders and how the
viewsare built up. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Optimize Query

From

"drum.lucas@gmail.com"

Date:

14 February 2016, 19:41:16

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 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

Explain analyze link:

http://explain.depesz.com/s/5WJy

Re: Optimize Query

From

Alban Hertroys

Date:

14 February 2016, 23:43:29

> On 14 Feb 2016, at 20:40, drum.lucas@gmail.com wrote:
>
> Hi Alban! Sorry.. that was my mistake

Okay, first advice on that query: Trim it down to something that people can wrap their minds around.

You have a silly amount of code repetition in there, much of which doesn't even seem to serve a purpose. This is some
kindof generated query, I gather? 
For example, you don't use most of the fields from your first subquery.

Another example is the nested subquery in your left join, which can be reduced to a single subquery with just the
fieldssummed that you actually need (and the customer_id, obviously). 

The same goes for most of the joins inside that left join, definitely the left joins - but that depends a bit on your
tabledefinitions and contents. 
For example, the fields you're summing come from account (but you can use customer instead, since you only use the
account_id,which equals client_id anyway) and bill_item. Some fields in your where-clause come from job, some others I
can'ttell where they're from. 

Trim, trim, trim, until you're left with a more readable query that gives you the same results and then put it through
explainanalyze again. It wouldn't surprise me if that query is already significantly faster. 

If you're still having problems at that point, post that query and the analysis again.

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

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.