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
