Re: remove_useless_joins vs. bug #19560

Thom Brown <thom(at)linux(dot)com> writes:
> Thanks. I've re-tested against these and I'm no longer able to crash it.

Thanks for testing!

> And it still reports the same previous planner regression, although
> thinks it may be considered acceptable. It also mentioned adding
> jointype assertions because the hoisting code doesn't have any.

Yeah, it occurred to me right after sending out the v3 patchset that
fixup_selfjoin_jointree should have an Assert that it doesn't hoist
anything above an outer join. Revised 0004 attached has that, and
adds a test case based on this example. 0001-0003 still the same.

As for the performance issue, I attach an 0005 that changes the
self-join logic to perform as many removals as it can. I don't
have a huge amount of faith in that, but I can't immediately see
any reason why it's not okay. It seems almost certainly okay to
perform SJE within groups of rels with different common OIDs, and
even within a single group, an earlier removal doesn't look like
it'd trash anything we are looking at. But it'd be worth running
a fuzzer to see if any cases turn up where the patchset fails or
gives different answers with/without 0005.

regards, tom lane

#text/x-diff; name="v4-0001-Postpone-initialization-of-all_result_relids-leaf.patch" [v4-0001-Postpone-initialization-of-all_result_relids-leaf.patch] /home/tgl/pgsql/v4-0001-Postpone-initialization-of-all_result_relids-leaf.patch
#text/x-diff; name="v4-0002-Perform-join-removal-by-editing-the-query-s-joint.patch" [v4-0002-Perform-join-removal-by-editing-the-query-s-joint.patch] /home/tgl/pgsql/v4-0002-Perform-join-removal-by-editing-the-query-s-joint.patch
#text/x-diff; name="v4-0003-Remove-dead-code-that-s-no-longer-needed-for-join.patch" [v4-0003-Remove-dead-code-that-s-no-longer-needed-for-join.patch] /home/tgl/pgsql/v4-0003-Remove-dead-code-that-s-no-longer-needed-for-join.patch
#text/x-diff; name="v4-0004-Ensure-we-hoist-modified-quals-to-the-right-join-.patch" [v4-0004-Ensure-we-hoist-modified-quals-to-the-right-join-.patch] /home/tgl/pgsql/v4-0004-Ensure-we-hoist-modified-quals-to-the-right-join-.patch
#text/x-diff; name="v4-0005-Allow-remove_useless_self_joins-to-remove-multipl.patch" [v4-0005-Allow-remove_useless_self_joins-to-remove-multipl.patch] /home/tgl/pgsql/v4-0005-Allow-remove_useless_self_joins-to-remove-multipl.patch