Re: Test tidscan,sql is not immune to autovacuum in v14

On Thu, 20 Aug 2026 at 08:00, Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
> I spotted a very rare test failure (a single one in two years, at least)
> generated by basilisk:
> tidscan ... FAILED 34 ms
> ...
> diff -U3 /mnt/build/REL_14_STABLE/pgsql.build/../pgsql/src/test/regress/expected/tidscan.out
> /mnt/build/REL_14_STABLE/pgsql.build/src/test/regress/results/tidscan.out
> --- /mnt/build/REL_14_STABLE/pgsql.build/../pgsql/src/test/regress/expected/tidscan.out
> +++ /mnt/build/REL_14_STABLE/pgsql.build/src/test/regress/results/tidscan.out
> @@ -242,10 +242,10 @@
> ----------------------------------------
> Aggregate
> -> Hash Join
> - Hash Cond: (t1.ctid = t2.ctid)
> - -> Seq Scan on tenk1 t1
> + Hash Cond: (t2.ctid = t1.ctid)
> + -> Seq Scan on tenk1 t2
> -> Hash
> - -> Seq Scan on tenk1 t2
> + -> Seq Scan on tenk1 t1
> (6 rows)
>
> SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;

I experimented, and I see the costs come out quite different if that
were changed to:

SELECT count(*) FROM tenk1 WHERE ctid IN (SELECT DISTINCT ctid FROM tenk1);

The winning plan is;

QUERY PLAN
-------------------------------------------------------------------------------------------
Aggregate (cost=1191.26..1191.27 rows=1 width=8)
-> Hash Join (cost=695.00..1166.26 rows=10000 width=0)
Hash Cond: (tenk1.ctid = tenk1_1.ctid)
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=6)
-> Hash (cost=570.00..570.00 rows=10000 width=6)
-> HashAggregate (cost=470.00..570.00 rows=10000 width=6)
Group Key: tenk1_1.ctid
-> Seq Scan on tenk1 tenk1_1 (cost=0.00..445.00
rows=10000 width=6)
(8 rows)

and if I force the other Hash Join option via the debugger, I get:

QUERY PLAN
-------------------------------------------------------------------------------------
Aggregate (cost=1302.50..1302.51 rows=1 width=8)
-> Hash Join (cost=1040.00..1277.50 rows=10000 width=0)
Hash Cond: (tenk1_1.ctid = tenk1.ctid)
-> HashAggregate (cost=470.00..570.00 rows=10000 width=6)
Group Key: tenk1_1.ctid
-> Seq Scan on tenk1 tenk1_1 (cost=0.00..445.00
rows=10000 width=6)
-> Hash (cost=445.00..445.00 rows=10000 width=6)
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=6)
(8 rows)

There's probably also some argument to the distinct semi-join query
giving the code a bit more exercise due to the Hash Agg.

Naturally, the current INNER JOIN query produces the same cost for
each join order, under normal circumstances.

This makes me wonder how significant the estimates varied in the two
calls to estimate_rel_size() in the problem case you saw?

I tried to figure this out by recreating this for myself using your
code on v14, but didn't manage to get the correct timing for a failure
to occur. I can get the plan to flip by adjusting the rel->tuples
after the estimate_rel_size() call by adding 1.0 to rel on the hash
side of the join. That's due to the two Paths comparing fuzzily the
same and the tie-break ending up deferring to:

else if (new_path->rows < old_path->rows)
remove_old = true; /* new dominates old */
else if (new_path->rows > old_path->rows)
accept_new = false; /* old dominates new */

So, I suspect you're seeing a very small variation in tuple estimates
and the DISTINCT semi-join query I proposed above would be enough to
solve it.

> Given the current statistics, we won't see failures of this ilk anymore,
> because it is not reproduced in REL_15_STABLE..master, due to 74388a1ac +
> 4496020e6, which resulted in a different reltuples value returned for tenk1
> during sanity_check/VACUUM and that indirectly affected the plan change.

I had also thought that it might not be worth troubling over given
that v14 has less than 3 months to live, but I believe it's generally
bad practice to have queries in tests where multiple plans are so
close together in cost. They're just too prone to very subtle changes
that can result in rare plan changes (as per what you're reporting).

David