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@sigaev.ru WWW:
http://www.sigaev.ru/