Re: Removing unneeded self joins

On 8/29/22 04:39, Zhihong Yu wrote:
> 
> 
> On Fri, Aug 26, 2022 at 3:02 PM Zhihong Yu <zyu@yugabyte.com 
> <mailto:zyu@yugabyte.com>> wrote:
> 
>     Hi,
>     For v36-0001-Remove-self-joins.patch :
> 
>     bq removes inner join of plane table to itself
> 
>     plane table -> plain table
> 
>     For relation_has_unique_index_ext(), it seems when extra_clauses
>     is NULL, there is no need to compute `exprs`.
> 
>     Cheers
Done
> 
> 
> For remove_self_joins_recurse():
> 
> +                   if (bms_num_members(relids) > join_collapse_limit)
> +                       break;
> 
> The above line just comes out of the switch statement. This check should 
> be done again between foreach and switch.
> Otherwise the above check wouldn't achieve what you want.
> 
> Cheers
Thanks for highlighting the problem.
I guess, usage either of join_collapse_limit or from_collapse_limit 
isn't practical here.
That we really afraid here - many senseless search cycles of self-joins. 
And it may have higher limit than GUCs above. So I introduced a guc, 
called "self_join_search_limit" (so far undocumented) that is an 
explicit limit for a set of plain relations in FROM-list to search 
self-joins.

-- 
Regards
Andrey Lepikhov
Postgres Professional