Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)

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