Re: To what extent should tests rely on VACUUM ANALYZE?

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);

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.

Thanks
Richard