On Wed, Jan 18, 2017 at 11:10 PM, Adrian Klaver
<adrian(dot)klaver(at)aklaver(dot)com> wrote:
> 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?
yes. Either way, I would like to very much understand how server is
preferring 3m cost plan to 2.6k cost plan or is otherwise unable to
access the cheap plan.
merlin