Subject: Re: Queries are taking way longer in 9.6 than 9.5
To: pgsql-general@postgresql.org
References: 
 <MWHPR02MB2655591A4E27B425FABD5E93807C0@MWHPR02MB2655.namprd02.prod.outlook.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <c9f5f16a-ea18-3c10-ca3e-8a9557179d8b@2ndquadrant.com>
Date: Tue, 17 Jan 2017 21:47:48 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
In-Reply-To: 
 <MWHPR02MB2655591A4E27B425FABD5E93807C0@MWHPR02MB2655.namprd02.prod.outlook.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

On 01/17/2017 08:31 PM, Ravi Tammineni wrote:
> Hi,
>
> We have recently upgraded to 9.6 and few queries are performing very
> poorly. Query execution time has increased more 4 or 5 times in 9.6.
> Qeruy execution plan is also completely changed. I am not sure whether
> its because of Parallel feature or any bug in 9.6. There are few similar
> kind of queries and all of them are taking more time in 9.6.
>
> Following query is taking 70ms in 9.5 and the same query is taking 2
> minutes in 9.6. Execution plan is totally different in 9.6 and seems
> like there is a major flaw while generating the execution plan. Instead
> of filtering the low cardinality, its filtering from biggest table.
> Somehow nested loop joins are screwed up.
>
> I really appreciate your help.
>
> explain analyze
> SELECT count(*)
> ...

You say "explain analyze" but have only provided EXPLAIN output. We need 
to see the EXPLAIN ANALYZE to see how accurate the estimates are.

Others already recommended doing ANALYZE to collect statistics on the 
upgraded database - if Heroku doesn't do that automatically, I'd 
recommend reporting that as a bug to them.

If that does not resolve the issue, run this on both machines and send 
the results to the mailing list:

     select name, setting from pg_settings where source != 'default';

That'll tell us how was the configuration modified.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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