| From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
|---|---|
| To: | Peter Geoghegan <pg(at)bowt(dot)ie>, "Daniel Westermann (DWE)" <daniel(dot)westermann(at)dbi-services(dot)com> |
| Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Behavior change in PostgreSQL 14Beta3 or bug? |
| Date: | 2021-09-06 15:48:42 |
| Message-ID: | 6fb7ffe9f584cb76e5c7aadc83d7562009224cb8.camel@cybertec.at |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Mon, 2021-09-06 at 07:46 -0700, Peter Geoghegan wrote:
> It's a non-hot update, and so there is a single dead index tuple. You're seeing
> the new optimization that makes vacuum skip indexes in marginal cases.
>
> Try running vacuum with index cleanup = on.
It occurs to me that this new default "auto" setting for "index_cleanup"
may cause a performance regression for people who VACUUM tables frequently
in order to get fast index-only scans.
That is not a bug, but it would be good to alert the users.
It is not an incompatibility that warrants a mention in the release notes,
but perhaps somthing in
https://www.postgresql.org/docs/14/indexes-index-only-scans.html
and/or
https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP
could be added that recommends that people should consider frequent
VACUUM with "index_cleanup = on" for best performance with index-only scans.
Suggested patch attached, should be backpatched to v14.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Document-VACUUM-tips-for-index-only-scans.patch | text/x-patch | 2.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2021-09-06 15:59:49 | Re: Behavior change in PostgreSQL 14Beta3 or bug? |
| Previous Message | Daniel Westermann (DWE) | 2021-09-06 14:52:10 | Re: Behavior change in PostgreSQL 14Beta3 or bug? |