From: Alexander Lakhin <exclusion@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Test tidscan,sql is not immune to autovacuum in v14
Date: Thu, 20 Aug 2026 12:00:01 +0300
Message-ID: <a58fdc63-4c21-4877-b5e9-b59f9b8ca13e@gmail.com> (raw)
In-Reply-To: <CAApHDvq_4uurbrP30Yc1VJ297+XUT6RAPx9meM8R8g67h5tJdQ@mail.gmail.com>
References: <f5d1f4c2-6224-4797-be17-c86e77f96c9c@gmail.com>
<CAApHDvq_4uurbrP30Yc1VJ297+XUT6RAPx9meM8R8g67h5tJdQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: exclusion@gmail.com, dgrowleyml@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Test tidscan,sql is not immune to autovacuum in v14
In-Reply-To: <a58fdc63-4c21-4877-b5e9-b59f9b8ca13e@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox