Optimize Query

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.

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?

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

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
<http://forum.kinghost.co.nz/memberlist.php?mode=viewprofile&amp;u=2&amp;sid=e999f8370385657a65d41d5ff60b0b38&gt;
- DigitalOcean <https://m.do.co/c/a0d31c597a03&gt;

On 11 February 2016 at 15:41, Melvin Davidson <melvin6925@gmail.com
<javascript:_e(%7B%7D,'cvml','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
<http://forum.kinghost.co.nz/memberlist.php?mode=viewprofile&amp;u=2&amp;sid=e999f8370385657a65d41d5ff60b0b38&gt;
- DigitalOcean <https://m.do.co/c/a0d31c597a03&gt;

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 is no way to predict how that's going to perform without at least knowing what goes into the placeholders and how the views are 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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 kind of 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 fields summed 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 table definitions 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't tell 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 explain analyze 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general