Content-Type: multipart/alternative;
 boundary="------------v15PYiPt4UQ0hVpa0FSCKCje"
Message-ID: <71277259-264e-4983-a201-938b404049d7@gmail.com>
Date: Mon, 20 Apr 2026 22:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM
 on-access)
To: Melanie Plageman <melanieplageman@gmail.com>,
 Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, David Rowley <dgrowleyml@gmail.com>,
 Kirill Reshke <reshkekirill@gmail.com>, Chao Li <li.evan.chao@gmail.com>,
 Andrey Borodin <x4mmm@yandex-team.ru>, Xuneng Zhou <xunengzhou@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Heikki Linnakangas <hlinnaka@iki.fi>
References: <2be31f17-5405-4de9-8d73-90ebc322f7d8@vondra.me>
 <CAAKRu_Ypa7-JGVR+fstDxU5Cfitk_rf5ijdaqwtoPkztursufA@mail.gmail.com>
 <CAAKRu_ZrDadxmGepBwPZ03yAKnMxwsHYn8SK9Gg7VqigLLVUWg@mail.gmail.com>
 <CAApHDvqAOeOwCKh9g0gfxWa040=Hyc7_oA=C59rjod8kXJDWyw@mail.gmail.com>
 <CAAKRu_Yt76_HdfR6DtK_wtkSNSj9=VxSV_npt+6T2R=zTzp1Pg@mail.gmail.com>
 <CAAKRu_atv6zA274m8Ysgbfn49c0NbdvHT7nXvd9kroZKnFq8Dg@mail.gmail.com>
 <CAApHDvq_R-gNXu+06GQW6w_HaEMh1pezsyiCh7GNhgh+h0UqMw@mail.gmail.com>
 <CAAKRu_YfoGTHNn0XxA+dCPj9hyO96vO4Eb+awRR6T8m22qC6ww@mail.gmail.com>
 <97529f5a-ec10-46b1-ab50-4653126c6889@gmail.com>
 <46733d68-aec0-4d09-8120-4c66b87047a4@gmail.com>
 <jfkklcxtlddx45vgx7rr27wndhkrh5umm4d2f2nhuz46lhw5ys@ohru3zfkeuww>
 <e71cf1ab-92cc-479a-b1e9-39f663867b90@gmail.com>
 <CAAKRu_aoK6824z_JtJUWMZk2ST2dXN005Gkt05iitTLD2i4OCw@mail.gmail.com>
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: 
 <CAAKRu_aoK6824z_JtJUWMZk2ST2dXN005Gkt05iitTLD2i4OCw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/71277259-264e-4983-a201-938b404049d7%40gmail.com>
Precedence: bulk

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

Hello Melanie and Andres,

20.04.2026 19:18, Melanie Plageman wrote:
>> but I wonder if there are other queries,
>> which plans can change due to the same reason.
> I think we'll have to take this on a case-by-case basis when we see
> failures. While it is certainly possible other tests just rely on
> autovacuum not having run and set the page all-visible, many of them
> probably have already had to account for that.

Thank you for paying attention to this!

I think, I found another test which suffers from autoanalyze with the new
behavior: [1], [2].

Initially I reproduced this diff on a slow armv7 device after many
iterations of `make check` with:
autovacuum_naptime = 1
autovacuum_analyze_threshold = 1
debug_parallel_query = 'regress'

But now I see that it can be reproduced on an ordinary machine with just:
--- a/src/test/regress/sql/plancache.sql
+++ b/src/test/regress/sql/plancache.sql
@@ -208,2 +208,3 @@ execute test_mode_pp(1); -- 2x
  execute test_mode_pp(1); -- 3x
+analyze test_mode;
  execute test_mode_pp(1); -- 4x
(and expected/plancache.out updated)

and `make check` running in a loop. It failed for me on iterations 5, 4,
10 (as far as I can see, analyze updates relallvisible not every time):
# parallel group (18 tests):  prepare xml conversion plancache limit returning copy2 polymorphism sequence rowtypes 
largeobject temp rangefuncs with truncate domain plpgsql alter_table
# diff -U3 .../src/test/regress/expected/plancache.out .../src/test/regress/results/plancache.out
# --- .../src/test/regress/expected/plancache.out    2026-04-20 21:35:30.677775398 +0300
# +++ .../src/test/regress/results/plancache.out     2026-04-20 21:43:49.324492302 +0300
# @@ -374,11 +374,11 @@
#
#  -- we should now get a really bad plan
#  explain (costs off) execute test_mode_pp(2);
# -         QUERY PLAN
# ------------------------------
# +                        QUERY PLAN
# +----------------------------------------------------------
#   Aggregate
# -   ->  Seq Scan on test_mode
# -         Filter: (a = $1)
# +   ->  Index Only Scan using test_mode_a_idx on test_mode
# +         Index Cond: (a = $1)
#  (3 rows)
#
#  -- but we can force a custom plan

The same modified test survived 50 iterations at 378a21618~1.

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dodo&dt=2026-04-07%2012%3A45%3A07
[2] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dodo&dt=2026-04-12%2022%3A45%3A06

Best regards,
Alexander
--------------v15PYiPt4UQ0hVpa0FSCKCje
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 Melanie and Andres,<br>
      <br>
      20.04.2026 19:18, Melanie Plageman wrote:<span
      style="white-space: pre-wrap">
</span><span style="white-space: pre-wrap">
</span></div>
    <blockquote type="cite"
cite="mid:CAAKRu_aoK6824z_JtJUWMZk2ST2dXN005Gkt05iitTLD2i4OCw@mail.gmail.com">
      <blockquote type="cite">
        <pre wrap="" class="moz-quote-pre">but I wonder if there are other queries,
which plans can change due to the same reason.
</pre>
      </blockquote>
      <pre wrap="" class="moz-quote-pre">
I think we'll have to take this on a case-by-case basis when we see
failures. While it is certainly possible other tests just rely on
autovacuum not having run and set the page all-visible, many of them
probably have already had to account for that.
</pre>
    </blockquote>
    <br>
    Thank you for paying attention to this!<br>
    <br>
    I think, I found another test which suffers from autoanalyze with
    the new<br>
    behavior: [1], [2].<br>
    <br>
    Initially I reproduced this diff on a slow armv7 device after many<br>
    iterations of `make check` with:<br>
    autovacuum_naptime = 1<br>
    autovacuum_analyze_threshold = 1<br>
    debug_parallel_query = 'regress'<br>
    <br>
    But now I see that it can be reproduced on an ordinary machine with
    just:<br>
    --- a/src/test/regress/sql/plancache.sql<br>
    +++ b/src/test/regress/sql/plancache.sql<br>
    @@ -208,2 +208,3 @@ execute test_mode_pp(1); -- 2x<br>
     execute test_mode_pp(1); -- 3x<br>
    +analyze test_mode;<br>
     execute test_mode_pp(1); -- 4x<br>
    (and expected/plancache.out updated)<br>
    <br>
    and `make check` running in a loop. It failed for me on iterations
    5, 4,<br>
    10 (as far as I can see, analyze updates relallvisible not every
    time):<br>
    # parallel group (18 tests):  prepare xml conversion plancache limit
    returning copy2 polymorphism sequence rowtypes largeobject temp
    rangefuncs with truncate domain plpgsql alter_table<br>
    # diff -U3
.../src/test/regress/expected/plancache.out .../src/test/regress/results/plancache.out<br>
    # --- .../src/test/regress/expected/plancache.out    2026-04-20
    21:35:30.677775398 +0300<br>
    # +++ .../src/test/regress/results/plancache.out     2026-04-20
    21:43:49.324492302 +0300<br>
    # @@ -374,11 +374,11 @@<br>
    #  <br>
    #  -- we should now get a really bad plan<br>
    #  explain (costs off) execute test_mode_pp(2);<br>
    # -         QUERY PLAN          <br>
    # ------------------------------<br>
    # +                        QUERY PLAN                        <br>
    # +----------------------------------------------------------<br>
    #   Aggregate<br>
    # -   -&gt;  Seq Scan on test_mode<br>
    # -         Filter: (a = $1)<br>
    # +   -&gt;  Index Only Scan using test_mode_a_idx on test_mode<br>
    # +         Index Cond: (a = $1)<br>
    #  (3 rows)<br>
    #  <br>
    #  -- but we can force a custom plan<br>
    <br>
    The same modified test survived 50 iterations at 378a21618~1.<br>
    <br>
    [1]
<a class="moz-txt-link-freetext" href="https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dodo&amp;dt=2026-04-07%2012%3A45%3A07">https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dodo&amp;dt=2026-04-07%2012%3A45%3A07</a><br>
    [2]
<a class="moz-txt-link-freetext" href="https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dodo&amp;dt=2026-04-12%2022%3A45%3A06">https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dodo&amp;dt=2026-04-12%2022%3A45%3A06</a><br>
    <br>
    Best regards,<br>
    Alexander<br>
  </body>
</html>

--------------v15PYiPt4UQ0hVpa0FSCKCje--