Re: [GENERAL] 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@gmail.com
> <mailto:melvin6925@gmail.com>> wrote:
>
>
>
>     On Wed, Jan 18, 2017 at 3:06 PM, Merlin Moncure <mmoncure@gmail.com
>     <mailto:mmoncure@gmail.com>> wrote:
>
>         On Wed, Jan 18, 2017 at 1:04 PM, Ravi Tammineni
>         <rtammineni@partner.aligntech.com
>         <mailto:rtammineni@partner.aligntech.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@postgresql.org <mailto:pgsql-general@postgresql.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.klaver@aklaver.com