From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-general@postgresql.org
Subject: Re: Queries are taking way longer in 9.6 than 9.5
Date: Tue, 17 Jan 2017 21:47:48 +0100
Message-ID: <c9f5f16a-ea18-3c10-ca3e-8a9557179d8b@2ndquadrant.com> (raw)
In-Reply-To: <MWHPR02MB2655591A4E27B425FABD5E93807C0@MWHPR02MB2655.namprd02.prod.outlook.com>
References: <MWHPR02MB2655591A4E27B425FABD5E93807C0@MWHPR02MB2655.namprd02.prod.outlook.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
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
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: pgsql-admin@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, pgsql-general@postgresql.org
Subject: Re: Queries are taking way longer in 9.6 than 9.5
In-Reply-To: <c9f5f16a-ea18-3c10-ca3e-8a9557179d8b@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox