From: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
---|---|
To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Perfomance bug in v10 |
Date: | 2017-06-01 15:46:43 |
Message-ID: | 32d1504a-f0cc-fec1-5127-12389fd0e4d7@sigaev.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Thank you for the answer!
>
> This is all caused by get_variable_numdistinct() deciding that all
> values are distinct because ntuples < DEFAULT_NUM_DISTINCT. I see that
> if the example is increased to use 300 tuples instead of 32, then
> that's enough for the planner to estimate 2 rows instead of clamping
> to 1, and the bad plan does not look so good anymore since the planner
> predicts that those nested loops need to be executed more than once.
I miss here why could the presence of index influence on that? removing
index causes a good plan although it isn't used in both plans .
>
> I really think the planner is too inclined to take risks by nesting
> Nested loops like this, but I'm not all that sure the best solution to
> fix this, and certainly not for beta1.
>
> So, I'm a bit unsure exactly how best to deal with this. It seems
> like we'd better make some effort, as perhaps this could be a case
> that might occur when temp tables are used and not ANALYZED, but the
> only way I can think to deal with it is not to favour unique inner
> nested loops in the costing model. The unfortunate thing about not
> doing this is that the planner will no longer swap the join order of a
> 2-way join to put the unique rel on the inner side. This is evident by
> the regression test failures caused by patching with the attached,
> which changes the cost model for nested loops back to what it was
> before unique joins.
The patch, seems, works for this particular case, but loosing swap isn't
good thing, I suppose.
>
> My other line of thought is just not to bother doing anything about
> this. There's plenty more queries you could handcraft to trick the
> planner into generating a plan that'll blow up like this. Is this a
> realistic enough one to bother accounting for? Did it come from a real
> world case? else, how did you stumble upon it?
Unfortunately, it's taken from real application.
--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2017-06-01 15:50:42 | Re: [HACKERS] Channel binding support for SCRAM-SHA-256 |
Previous Message | Dilip Kumar | 2017-06-01 15:41:32 | Re: <> join selectivity estimate question |