Re: Removing unneeded self joins

On 4/4/2023 02:30, Gregory Stark (as CFM) wrote:
> On Mon, 6 Mar 2023 at 00:30, Michał Kłeczek <michal(at)kleczek(dot)org> wrote:
>>
>> Hi All,
>>
>> I just wanted to ask about the status and plans for this patch.
>> I can see it being stuck at “Waiting for Author” status in several commit tests.
>
> Sadly it seems to now be badly in need of a rebase. There are large
> hunks failing in the guts of analyzejoins.c as well as minor failures
> elsewhere and lots of offsets which need to be reviewed.
>
> I think given the lack of activity it's out of time for this release
> at this point. I'm moving it ahead to the next CF.
Hi,

Version 41 is heavily remade of the feature:

1. In previous versions, I tried to reuse remove_rel_from_query() for
both left and self-join removal. But for now, I realized that it is a
bit different procedures which treat different operations. In this
patch, only common stages of the PlannerInfo fixing process are united
in one function.
2. Transferring clauses from the removing table to keeping one is more
transparent now and contains comments.
3. Equivalence classes update procedure was changed according to David's
commit 3373c71. As I see, Tom has added remove_rel_from_eclass since the
last v.40 version, and it looks pretty similar to the update_eclass
routine in this patch.

It passes regression tests, but some questions are still open:
1. Should we look for duplicated or redundant clauses (the same for
eclasses) during the clause transfer procedure? On the one side, we
reduce the length of restrict lists that can impact planning or
executing time. Additionally, we improve the accuracy of cardinality
estimation. On the other side, it is one more place that can make
planning time much longer in specific cases. It would have been better
to avoid calling the equal() function here, but it's the only way to
detect duplicated inequality expressions.
2. Could we reuse ChangeVarNodes instead of sje_walker(), merge
remove_rel_from_restrictinfo with replace_varno?
3. Also, I still don't finish with the split_selfjoin_quals: some
improvements could be made.

--
regards,
Andrey Lepikhov
Postgres Professional