Re: Removing unneeded self joins

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Zhihong Yu <zyu(at)yugabyte(dot)com>, Ronan Dunklau <ronan(dot)dunklau(at)aiven(dot)io>
Cc: Greg Stark <stark(at)mit(dot)edu>, Andres Freund <andres(at)anarazel(dot)de>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Hywel Carver <hywel(at)skillerwhale(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Removing unneeded self joins
Date: 2022-08-29 05:05:29
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 8/29/22 04:39, Zhihong Yu wrote:
> On Fri, Aug 26, 2022 at 3:02 PM Zhihong Yu <zyu(at)yugabyte(dot)com
> <mailto:zyu(at)yugabyte(dot)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
> 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

Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
v37-0001-Remove-self-joins.patch text/x-patch 89.3 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message John Naylor 2022-08-29 05:18:05 Re: Insertion Sort Improvements
Previous Message Noah Misch 2022-08-29 04:37:52 Re: privileges for ALTER ROLE/DATABASE SET