public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andres Freund <[email protected]>
To: Alexander Lakhin <[email protected]>
Cc: Melanie Plageman <[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: Sat, 18 Apr 2026 12:25:48 -0400
Message-ID: <jfkklcxtlddx45vgx7rr27wndhkrh5umm4d2f2nhuz46lhw5ys@ohru3zfkeuww> (raw)
In-Reply-To: <[email protected]>
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]>
Hi,
On 2026-04-18 18:00:00 +0300, Alexander Lakhin wrote:
> Hello Melanie and Andres,
>
> 03.04.2026 08:00, Alexander Lakhin wrote:
> >
> > 31.03.2026 19:19, Melanie Plageman wrote:
> > > Thanks for the reply! I have committed the patches in this thread and
> > > marked the CF entry accordingly.
> >
> > I've come across an interesting failure produced starting from 378a21618:
> > ...
>
> I've discovered one more behaviour change introduced in 378a21618. I
> investigated a yesterday's skink failure [1]:
> # --- /home/bf/bf-build/skink-master/HEAD/pgsql/contrib/btree_gist/expected/enum.out 2025-06-23 20:17:56.295775456 +0200
> # +++ /home/bf/bf-build/skink-master/HEAD/pgsql.build/testrun/btree_gist/regress/results/enum.out
> 2026-04-17 22:35:37.212061309 +0200
> # @@ -83,12 +83,10 @@
> #
> # EXPLAIN (COSTS OFF)
> # SELECT count(*) FROM enumtmp WHERE a >= 'g'::rainbow;
Random: I wonder if the author if this intended this to be a temp table, based
on the name? That'd prevent any concurrent autovacuums/analyzes from changing
anything.
> # --- /home/vagrant/postgres/contrib/btree_gist/expected/enum.out 2026-04-18 11:41:17.224063241 +0000
> # +++ /home/vagrant/postgres/contrib/btree_gist/results/enum.out 2026-04-18 11:52:43.870049782 +0000
> # @@ -91,18 +91,16 @@
> # where c.relname in ('enumtmp', 'enumidx');
> # relname | relpages | reltuples | autovacuum_count | autoanalyze_count
> # ---------+----------+-----------+------------------+-------------------
> # - enumtmp | 3 | 595 | 0 | 0
> # + enumtmp | 3 | 595 | 0 | 1
> # enumidx | 4 | 595 | |
> # (2 rows)
> #
> # EXPLAIN (COSTS OFF)
> # SELECT count(*) FROM enumtmp WHERE a >= 'g'::rainbow;
> # - QUERY PLAN
> # ------------------------------------------------
> # + QUERY PLAN
> # +------------------------------------------------
> # Aggregate
> # - -> Bitmap Heap Scan on enumtmp
> # - Recheck Cond: (a >= 'g'::rainbow)
> # - -> Bitmap Index Scan on enumidx
> # - Index Cond: (a >= 'g'::rainbow)
> # -(5 rows)
> # + -> Index Only Scan using enumidx on enumtmp
> # + Index Cond: (a >= 'g'::rainbow)
> # +(3 rows)
> #
> not ok 48 - enum 10596 ms
The interesting column to show here would presumably be relallvisible.
What I assume is happening is that occasionally analyze now sees enough all
visible pages (due to on-access pruning marking the pages all visible) to
consider the index only scan worthwhile, whereas before that wasn't (or only
very rarely) happened.
Maybe I'm daft, but what would prevent this from happening before? The path
for it would be a bit more complicated, you'd have to have an autovacuum
instead of just an analyze - but that seems possible. It might require running
against a pre-existing install to be likely enough.
> It's not reproduced at 378a21618~1, though.
>
> Could you please look if this can be fixed?
When you say fix, I assume you mean address the test instability, rather than
actual code changes?
Greetings,
Andres Freund
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: <jfkklcxtlddx45vgx7rr27wndhkrh5umm4d2f2nhuz46lhw5ys@ohru3zfkeuww>
* 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