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