Richard Guo <guofengli...@gmail.com> writes: > On Fri, Mar 29, 2024 at 1:33 AM Tom Lane <t...@sss.pgh.pa.us> wrote: >> Tomas Vondra <tomas.von...@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); > 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. I'm not sure that that is testing the same thing (since it's no longer an equijoin), or that it would fix the issue. The problem really is that all three baserels have identical statistics so there's no difference in cost between different join orders, and then it's mostly a matter of unspecified implementation details which order we will choose, and even the smallest change in one rel's statistics can flip it. The way we have fixed similar issues elsewhere is to add a scan-level WHERE restriction that makes one of the baserels smaller, breaking the symmetry. So I'd try something like 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); +ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd) +WHERE a.thousand < 750; (I first tried reducing the size of B, but that caused the join order to change; restricting A makes it keep the existing plan.) Might or might not need to mess with the size of C, but since that one needs uniquification it's different from the others already. regards, tom lane