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?
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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
