Thom Brown <[email protected]> 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
