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