Re: Removing unneeded self joins

On 26/2/2025 13:14, Alexander Korotkov wrote:
> On Mon, Feb 24, 2025 at 2:22 PM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>> On 24/2/2025 11:57, Alexander Korotkov wrote:
>>> Could you, please, elaborate more on what you mean by "new technique
>>> of query tree reduction"?
>> I mean any transformations and optimisations that reduce search space
>> for optimisation. Right now, I see the features reduce_unique_semijoins,
>> remove_useless_joins, and remove_useless_self_joins.
>> In practice, I see at least a join on a foreign key, where some cases
>> potentially allow the removal of the JOIN operator.
>
> Do you mean some generic facility, which generalizes all the
> transformations you mentioned? If so, it would be cool. But how
> could it look like?
I think we may realise what it may look like by attempting to implement
more RelOptInfo-removal features. Right now, I have only vague ideas on
that subject. Just for reference, there were other discussions on join
removal [1 - 5] and some blog posts explaining Oracle techniques in this
area - see, for example, [6].

[1] inner join removal
https://www.postgresql.org/message-id/flat/AANLkTinS_MlZ2F3Siwgcje--qf5nTJTpuHFnZdcA45bU%40mail.gmail.com
[2] Patch to support SEMI and ANTI join removal
https://www.postgresql.org/message-id/flat/CAApHDvpCBEfuc5tD%3DvniepAv0pU5m%3Dq%3DfOQZcOdMHeei7OQPgQ%40mail.gmail.com
[3] Removing INNER JOINs
https://www.postgresql.org/message-id/flat/CAApHDvocUEYdt1uT+DLDPs2xEu=v3qJGT6HeXKonQM4rY_OsSA(at)mail(dot)gmail(dot)com#CAApHDvocUEYdt1uT+DLDPs2xEu=v3qJGT6HeXKonQM4rY_OsSA@mail.gmail.com
[4] WIP Join Removal
https://www.postgresql.org/message-id/flat/1220176372.4371.118.camel%40ebony.2ndQuadrant
[5] Join Removal/ Vertical Partitioning
https://www.postgresql.org/message-id/flat/1214477827.3845.87.camel%40ebony.site
[6] Oracle Join Elimination
https://oracle-base.com/articles/misc/join-elimination

--
regards, Andrei Lepikhov