Re: Queries are taking way longer in 9.6 than 9.5

On 01/18/2017 08:58 PM, Merlin Moncure wrote:
> On Wed, Jan 18, 2017 at 2:12 PM, Melvin Davidson <melvin6925(at)gmail(dot)com
> <mailto:melvin6925(at)gmail(dot)com>> wrote:
>
>
>
> On Wed, Jan 18, 2017 at 3:06 PM, Merlin Moncure <mmoncure(at)gmail(dot)com
> <mailto:mmoncure(at)gmail(dot)com>> wrote:
>
> On Wed, Jan 18, 2017 at 1:04 PM, Ravi Tammineni
> <rtammineni(at)partner(dot)aligntech(dot)com
> <mailto:rtammineni(at)partner(dot)aligntech(dot)com>> wrote:
> > Hi Chris,
> >
> > Here is the query and execution plan in 9.5 and 9.6.
>
> Can you verify tblpuorderstatus and tblpuorderstatushistory have all
> indexes accounted for on both servers? It seems incredible server
> would prefer wading through 11M records to 1298 nestloop. I'm
> curious
> what plans you get if you try playing around with:
>
> set enable_seqscan=false;
> set enable_hashjoin=false;
>
> ...but I think we have two possibilities here:
> 1. schema mismatch
> 2. planner bug
>
> merlin
>
>
> --
> Sent via pgsql-general mailing list
> (pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> <http://www.postgresql.org/mailpref/pgsql-general>
>
>
> *I never got an answer to my question.
> *
> *Have you verified that postgresql.conf is the same of both 9.5 & 9.6?*
>
>
> This is not verified, but I can't think of an influential planner
> variable that would push planner cost from 2600 to millions; abrupt
> increase in plan cost roles out a knife edge plan choice and the
> statistic look relatively correct on rows. Unless planner choices are
> disabled in postgresql.conf, this suggests something is preventing
> planner from choosing a particular kind of plan for this query, which is
> suggesting bug to me.

I am still working out the parallel query feature in 9.6 but I am seeing
the below in the 9.6 EXPLAIN ANALYZE:

-> Gather (cost=1000.00..3011004.71 rows=529690 width=4) (actual
time=2.713..368445.460 rows=595653 loops=1)
Workers Planned: 2
Workers Launched: 2

Does that not indicate parallel query has been turned on?

Would not turning it off be a better apple-to-apple comparison to the
9.5 plan?

>
> OP, if you want to contribute to the investigation of fix, "git bisect"
> is the way to proceed...is that feasible?
>
> merlin

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com