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

Hello David and Tom,

20.08.2026 03:32, David Rowley wrote:
> 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)

My "good" plan in REL_14_STABLE is:
EXPLAIN (VERBOSE)
SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Aggregate  (cost=1177.26..1177.27 rows=1 width=8)
   Output: count(*)
   ->  Hash Join  (cost=569.89..1152.27 rows=9995 width=0)
         Hash Cond: (t1.ctid = t2.ctid)
         ->  Seq Scan on public.tenk1 t1  (cost=0.00..444.95 rows=9995 width=6)
               Output: t1.ctid
         ->  Hash  (cost=444.95..444.95 rows=9995 width=6)
               Output: t2.ctid
               ->  Seq Scan on public.tenk1 t2 (cost=0.00..444.95 rows=9995 width=6)
                     Output: t2.ctid
(10 rows)

versus "bad":
EXPLAIN (VERBOSE)
SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Aggregate  (cost=1177.33..1177.34 rows=1 width=8)
   Output: count(*)
   ->  Hash Join  (cost=569.89..1152.34 rows=9995 width=0)
         Hash Cond: (t2.ctid = t1.ctid)
         ->  Seq Scan on public.tenk1 t2  (cost=0.00..445.00 rows=10000 width=6)
               Output: t2.ctid
         ->  Hash  (cost=444.95..444.95 rows=9995 width=6)
               Output: t1.ctid
               ->  Seq Scan on public.tenk1 t1 (cost=0.00..444.95 rows=9995 width=6)
                     Output: t1.ctid
(10 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?

At the commit 74388a1ac, with this logging added:
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -60,2 +60,3 @@
 #include "utils/syscache.h"
+#include "utils/lsyscache.h"

@@ -1255,5 +1256,9 @@ vac_estimate_reltuples(Relation relation,
         */
+char *rel_name = get_rel_name(relation->rd_id);
        if (old_rel_pages == total_pages &&
                scanned_pages < (double) total_pages * 0.02)
+{
+elog(LOG, "!!!vac_estimate_reltuples| rel_name: %s, old_rel_tuples: %lf", rel_name, old_rel_tuples);
                return old_rel_tuples;
+}

@@ -1275,2 +1280,3 @@ vac_estimate_reltuples(Relation relation,
        total_tuples = old_density * unscanned_pages + scanned_tuples;
+elog(LOG, "!!!vac_estimate_reltuples| rel_name: %s, floor(total_tuples + 0.5): %lf", rel_name, floor(total_tuples + 0.5));
        return floor(total_tuples + 0.5);

I can see the following:
2026-08-20 04:23:48.978 EDT client backend[672080] pg_regress/sanity_check LOG:  !!!vac_estimate_reltuples| rel_name:
tenk1, old_rel_tuples: 10000.000000
2026-08-20 04:23:48.978 EDT client backend[672080] pg_regress/sanity_check CONTEXT:  while scanning relation "public.tenk1"
2026-08-20 04:23:48.978 EDT client backend[672080] pg_regress/sanity_check STATEMENT:  VACUUM;

when the plan is expected, versus
2026-08-20 04:27:53.033 EDT client backend[674543] pg_regress/sanity_check LOG:  !!!vac_estimate_reltuples| rel_name:
tenk1, floor(total_tuples + 0.5): 9995.000000
2026-08-20 04:27:53.033 EDT client backend[674543] pg_regress/sanity_check CONTEXT:  while scanning relation "public.tenk1"
2026-08-20 04:27:53.033 EDT client backend[674543] pg_regress/sanity_check STATEMENT:  VACUUM;

at 74388a1ac~1 (with the "bad" plan).

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

I absolutely agree with your and Tom's points -- the worthwhile thing here
to me is the evidence how (auto)vacuum/analyze can affect the planner's
decisions transiently (in a very small window). I remember a similar story
when tenk2's reltuples drifted 10000 ->9995 [1], but in that case it
affected sensitive queries that followed the drift.

Thank you for your attention to this anomaly!

[1] https://www.postgresql.org/message-id/66eb9a6e-fc67-a230-c5b1-2a741e8b88c6%40gmail.com

Best regards,
Alexander