Tomas Vondra <tomas.vondra@enterprisedb.com> writes: > Yeah. I think it's good to design the data/queries in such a way that > the behavior does not flip due to minor noise like in this case.
+1
Agreed. The query in problem is:
-- we can pull up the sublink into the inner JoinExpr. explain (costs off) SELECT * FROM tenk1 A INNER JOIN tenk2 B ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
For this query, the RHS of the semijoin can be unique-ified, allowing it to be joined to anything else by unique-ifying the RHS. Hence, both join orders 'A/C/B' (as in the answer file) and 'B/C/A' (as in the reported plan diff) are legal.
So I'm wondering if we can make this test case more stable by using 'c.odd > b.odd' instead of 'c.odd = b.odd' in the subquery, as attached. As a result, the RHS of the semijoin cannot be unique-ified any more, so that the only legal join order is 'A/B/C'. We would not have different join orders due to noises in the estimates, while still testing what we intend to test.