Richard Guo <guofenglinux@gmail.com> writes:
> On Fri, Mar 29, 2024 at 1:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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);
> 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