public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Lakhin <[email protected]>
To: Andres Freund <[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 21:00:00 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <jfkklcxtlddx45vgx7rr27wndhkrh5umm4d2f2nhuz46lhw5ys@ohru3zfkeuww>
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>
18.04.2026 19:25, Andres Freund wrote:
> 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.
Indeed, with c.relallvisible added, I can see:
--- .../contrib/btree_gist/expected/enum.out 2026-04-18 19:37:51.041565543 +0300
+++ .../contrib/btree_gist/results/enum.out 2026-04-18 19:40:59.077264981 +0300
@@ -88,18 +88,16 @@
where c.relname in ('enumtmp', 'enumidx');
relname | relpages | reltuples | autovacuum_count | autoanalyze_count | relallvisible
---------+----------+-----------+------------------+-------------------+---------------
- enumtmp | 3 | 595 | 0 | 0 | 0
+ enumtmp | 3 | 595 | 0 | 0 | 2
enumidx | 4 | 595 | | | 0
(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)
At 378a21618~1, it stays zero.
> 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.
Yes, with VACUUM enumtmp; instead of ANALYZE enumtmp; the plan change is
reproduced at 378a21618~1:
@@ -88,18 +88,16 @@
where c.relname in ('enumtmp', 'enumidx');
relname | relpages | reltuples | autovacuum_count | autoanalyze_count | relallvisible
---------+----------+-----------+------------------+-------------------+---------------
- enumtmp | 3 | 595 | 0 | 0 | 0
+ enumtmp | 3 | 595 | 0 | 0 | 3
enumidx | 4 | 595 | | | 0
(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)
And this diff is produced even at f7946a92 (from 2017-03-21), which added
the test case.
So, given that this is the only failure of btree_gist in two last years
at least, it looks like the probability of vacuuming the table there is
much lower than of analyzing.
>> 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?
Sure, I didn't mean the new behavior is wrong. Probably changing that
table to temporary would work, but I wonder if there are other queries,
which plans can change due to the same reason.
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