On Wed, Jan 18, 2017 at 11:10 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > On 01/18/2017 08:58 PM, Merlin Moncure wrote: >> >> On Wed, Jan 18, 2017 at 2:12 PM, Melvin Davidson <melvin6925@xxxxxxxxx >> <mailto:melvin6925@xxxxxxxxx>> wrote: >> >> >> >> On Wed, Jan 18, 2017 at 3:06 PM, Merlin Moncure <mmoncure@xxxxxxxxx >> <mailto:mmoncure@xxxxxxxxx>> wrote: >> >> On Wed, Jan 18, 2017 at 1:04 PM, Ravi Tammineni >> <rtammineni@xxxxxxxxxxxxxxxxxxxxx >> <mailto:rtammineni@xxxxxxxxxxxxxxxxxxxxx>> 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@xxxxxxxxxxxxxx >> <mailto:pgsql-general@xxxxxxxxxxxxxx>) >> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
