public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Sudden drastic change in performance
Date: Thu, 15 Jun 2017 11:09:50 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <BN4PR15MB05477716E1B8B8D211AADC5285C00@BN4PR15MB0547.namprd15.prod.outlook.com>
References: <BN4PR15MB05477716E1B8B8D211AADC5285C00@BN4PR15MB0547.namprd15.prod.outlook.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

"[email protected]" <[email protected]> writes:
> I have a query with many joins, something like:

> Select c1, c2, c3, sum(c5)
>   From V1
>        Join V2 on ...
>        Left join V3 on ...
>        Left join T4 on ...
>        Join T5 on ...
>        Join T6 on ...
>        Left join T7 on ...
>        Join T8 on ...
>        Left join T9 on ...
> Where ...
> Group by c1, c2, c3

> The join clauses are fairly innocuous and work directly on foreign key relationships, so there is no voodoo there. Same for the where clause. The views are similar and also join 3-4 tables each. All in all, there are 3 of all the tables involved that have millions of rows and all the other tables have thousands of rows. In particular, T9 is totally empty.

> If I remove T9 from the query, it takes 9s to run. If I keep T9, the query takes over 30mn to run! If I switch the order of T8/T9, then the same happens with T8. So I don't think this has to do with the tables themselves. I have updated all the statistics and reindexed all involved tables.

You need to raise join_collapse_limit to keep the planner from operating
with its stupid cap on.  Usually people also increase from_collapse_limit
if they have to touch either, but I think for this specific query syntax
only the former matters.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Sudden drastic change in performance
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox