Re: Optimize Query

> On 14 Feb 2016, at 20:40, drum(dot)lucas(at)gmail(dot)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.