Content-Type: multipart/alternative;
 boundary="------------JBurEjviai5Z56cxyFQYJeeg"
Message-ID: <a58fdc63-4c21-4877-b5e9-b59f9b8ca13e@gmail.com>
Date: Thu, 20 Aug 2026 12:00:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Test tidscan,sql is not immune to autovacuum in v14
To: David Rowley <dgrowleyml@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
References: <f5d1f4c2-6224-4797-be17-c86e77f96c9c@gmail.com>
 <CAApHDvq_4uurbrP30Yc1VJ297+XUT6RAPx9meM8R8g67h5tJdQ@mail.gmail.com>
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: 
 <CAApHDvq_4uurbrP30Yc1VJ297+XUT6RAPx9meM8R8g67h5tJdQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/a58fdc63-4c21-4877-b5e9-b59f9b8ca13e%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------JBurEjviai5Z56cxyFQYJeeg
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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
--------------JBurEjviai5Z56cxyFQYJeeg
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">Hello David and Tom,<br>
      <br>
      20.08.2026 03:32, David Rowley wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAApHDvq_4uurbrP30Yc1VJ297+XUT6RAPx9meM8R8g67h5tJdQ@mail.gmail.com">
      <pre wrap="" class="moz-quote-pre">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)
   -&gt;  Hash Join  (cost=695.00..1166.26 rows=10000 width=0)
         Hash Cond: (tenk1.ctid = tenk1_1.ctid)
         -&gt;  Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=6)
         -&gt;  Hash  (cost=570.00..570.00 rows=10000 width=6)
               -&gt;  HashAggregate  (cost=470.00..570.00 rows=10000 width=6)
                     Group Key: tenk1_1.ctid
                     -&gt;  Seq Scan on tenk1 tenk1_1  (cost=0.00..445.00
rows=10000 width=6)
(8 rows)</pre>
    </blockquote>
    <br>
    My "good" plan in REL_14_STABLE is:<br>
    EXPLAIN (VERBOSE)<br>
    SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;<br>
                                          QUERY PLAN<br>
--------------------------------------------------------------------------------------<br>
     Aggregate  (cost=1177.26..1177.27 rows=1 width=8)<br>
       Output: count(*)<br>
       -&gt;  Hash Join  (cost=569.89..1152.27 rows=9995 width=0)<br>
             Hash Cond: (t1.ctid = t2.ctid)<br>
             -&gt;  Seq Scan on public.tenk1 t1  (cost=0.00..444.95
    rows=9995 width=6)<br>
                   Output: t1.ctid<br>
             -&gt;  Hash  (cost=444.95..444.95 rows=9995 width=6)<br>
                   Output: t2.ctid<br>
                   -&gt;  Seq Scan on public.tenk1 t2 
    (cost=0.00..444.95 rows=9995 width=6)<br>
                         Output: t2.ctid<br>
    (10 rows)<br>
    <br>
    versus "bad":<br>
    EXPLAIN (VERBOSE)<br>
    SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid;<br>
                                          QUERY PLAN<br>
--------------------------------------------------------------------------------------<br>
     Aggregate  (cost=1177.33..1177.34 rows=1 width=8)<br>
       Output: count(*)<br>
       -&gt;  Hash Join  (cost=569.89..1152.34 rows=9995 width=0)<br>
             Hash Cond: (t2.ctid = t1.ctid)<br>
             -&gt;  Seq Scan on public.tenk1 t2  (cost=0.00..445.00
    rows=10000 width=6)<br>
                   Output: t2.ctid<br>
             -&gt;  Hash  (cost=444.95..444.95 rows=9995 width=6)<br>
                   Output: t1.ctid<br>
                   -&gt;  Seq Scan on public.tenk1 t1 
    (cost=0.00..444.95 rows=9995 width=6)<br>
                         Output: t1.ctid<br>
    (10 rows)<br>
    <br>
    <blockquote type="cite"
cite="mid:CAApHDvq_4uurbrP30Yc1VJ297+XUT6RAPx9meM8R8g67h5tJdQ@mail.gmail.com">
      <pre wrap="" class="moz-quote-pre">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)
   -&gt;  Hash Join  (cost=1040.00..1277.50 rows=10000 width=0)
         Hash Cond: (tenk1_1.ctid = tenk1.ctid)
         -&gt;  HashAggregate  (cost=470.00..570.00 rows=10000 width=6)
               Group Key: tenk1_1.ctid
               -&gt;  Seq Scan on tenk1 tenk1_1  (cost=0.00..445.00
rows=10000 width=6)
         -&gt;  Hash  (cost=445.00..445.00 rows=10000 width=6)
               -&gt;  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?</pre>
    </blockquote>
    <br>
    At the commit 74388a1ac, with this logging added:<br>
    --- a/src/backend/commands/vacuum.c<br>
    +++ b/src/backend/commands/vacuum.c<br>
    @@ -60,2 +60,3 @@<br>
     #include "utils/syscache.h"<br>
    +#include "utils/lsyscache.h"<br>
     <br>
    @@ -1255,5 +1256,9 @@ vac_estimate_reltuples(Relation relation,<br>
             */<br>
    +char *rel_name = get_rel_name(relation-&gt;rd_id);<br>
            if (old_rel_pages == total_pages &amp;&amp;<br>
                    scanned_pages &lt; (double) total_pages * 0.02)<br>
    +{<br>
    +elog(LOG, "!!!vac_estimate_reltuples| rel_name: %s, old_rel_tuples:
    %lf", rel_name, old_rel_tuples);<br>
                    return old_rel_tuples;<br>
    +}<br>
     <br>
    @@ -1275,2 +1280,3 @@ vac_estimate_reltuples(Relation relation,<br>
            total_tuples = old_density * unscanned_pages +
    scanned_tuples;<br>
    +elog(LOG, "!!!vac_estimate_reltuples| rel_name: %s,
    floor(total_tuples + 0.5): %lf", rel_name, floor(total_tuples +
    0.5));<br>
            return floor(total_tuples + 0.5);<br>
    <br>
    I can see the following:<br>
    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<br>
    2026-08-20 04:23:48.978 EDT client backend[672080]
    pg_regress/sanity_check CONTEXT:  while scanning relation
    "public.tenk1"<br>
    2026-08-20 04:23:48.978 EDT client backend[672080]
    pg_regress/sanity_check STATEMENT:  VACUUM;<br>
    <br>
    when the plan is expected, versus<br>
    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<br>
    2026-08-20 04:27:53.033 EDT client backend[674543]
    pg_regress/sanity_check CONTEXT:  while scanning relation
    "public.tenk1"<br>
    2026-08-20 04:27:53.033 EDT client backend[674543]
    pg_regress/sanity_check STATEMENT:  VACUUM;<br>
    <br>
    at 74388a1ac~1 (with the "bad" plan).<br>
    <br>
    <blockquote type="cite"
cite="mid:CAApHDvq_4uurbrP30Yc1VJ297+XUT6RAPx9meM8R8g67h5tJdQ@mail.gmail.com">
      <blockquote type="cite">
        <pre wrap="" class="moz-quote-pre">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.
</pre>
      </blockquote>
      <pre wrap="" class="moz-quote-pre">
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).
</pre>
    </blockquote>
    <br>
    I absolutely agree with your and Tom's points -- the worthwhile
    thing here<br>
    to me is the evidence how (auto)vacuum/analyze can affect the
    planner's<br>
    decisions transiently (in a very small window). I remember a similar
    story<br>
    when tenk2's reltuples drifted 10000 -&gt;9995 [1], but in that case
    it<br>
    affected sensitive queries that followed the drift.<br>
    <br>
    Thank you for your attention to this anomaly!<br>
    <br>
    [1]
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/66eb9a6e-fc67-a230-c5b1-2a741e8b88c6%40gmail.com">https://www.postgresql.org/message-id/66eb9a6e-fc67-a230-c5b1-2a741e8b88c6%40gmail.com</a><br>
    <br>
    Best regards,<br>
    Alexander<br>
  </body>
</html>

--------------JBurEjviai5Z56cxyFQYJeeg--