Re: [COMMITTERS] pgsql: Further fixes for degenerate outer join clauses.

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 08/06/2015 03:36 PM, Tom Lane wrote:
>> Further fixes for degenerate outer join clauses.

> Looks like this might have upset brolga on 9.0 and 9.1 - it's coming up
> with a different plan from what's expected.

I looked into this, and while I can't be certain of the diagnosis without
looking at the assembly code brolga generates (no, I don't want to), I'm
pretty sure I know what's going on here. The two plans in question have
exactly the same estimated costs on my machine, which results in add_path
discarding the second one to arrive. Evidently, on brolga the second path
gets some fractionally cheaper estimate due to some compiler-specific
variation in the way the arithmetic is done, so it gets kept rather than
the first one.

In 9.2 and up we got rid of that class of problems with this patch:

Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Branch: master Release: REL9_2_BR [33e99153e] 2012-04-21 00:51:14 -0400

Use fuzzy not exact cost comparison for the final tie-breaker in add_path.

Instead of an exact cost comparison, use a fuzzy comparison with 1e-10
delta after all other path metrics have proved equal. This is to avoid
having platform-specific roundoff behaviors determine the choice when
two paths are really the same to our cost estimators. Adjust the
recently-added test case that made it obvious we had a problem here.

but 9.0 and 9.1 still have platform-dependent treatment of paths with
essentially equal cost estimates.

I'm not entirely sure what to do about this. We could back-patch that
patch into 9.0 and 9.1, but it's conceivable somebody would squawk about
planner behavioral changes. The only other idea that seems practical is
to remove regression test cases that have platform-specific results in
those branches. Probably that wouldn't result in a real reduction in the
quality of the test coverage for those branches (we could still execute
the query, just not EXPLAIN it). But it seems like a pretty ad-hoc
answer, and the next case might be one that hurts more not to test.

Thoughts?

regards, tom lane