public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Lakhin <[email protected]>
To: Melanie Plageman <[email protected]>
To: Andres Freund <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: Chao Li <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: Xuneng Zhou <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)
Date: Mon, 20 Apr 2026 22:00:00 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAAKRu_aoK6824z_JtJUWMZk2ST2dXN005Gkt05iitTLD2i4OCw@mail.gmail.com>
References: <[email protected]>
<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>
<[email protected]>
<[email protected]>
<jfkklcxtlddx45vgx7rr27wndhkrh5umm4d2f2nhuz46lhw5ys@ohru3zfkeuww>
<[email protected]>
<CAAKRu_aoK6824z_JtJUWMZk2ST2dXN005Gkt05iitTLD2i4OCw@mail.gmail.com>
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
view thread (143+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox