From: "Tefft, Michael J" <Michael.J.Tefft@snapon.com>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Autovacuum and visibility maps
Thread-Topic: Autovacuum and visibility maps
Thread-Index: AdtFnZrPZttyZEAwRIqDe+1fxv6vqQ==
Date: Tue, 3 Dec 2024 16:32:22 +0000
Message-ID:
 <BN8PR04MB6289F7099F7B38E5B08D85B7D0362@BN8PR04MB6289.namprd04.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?93qKGzNwZwF7y8+T1D3Pm8z1cE8+HWWpJqQpwLy7OBcGsBGFA+sRWAgiPWrU?=
 =?us-ascii?Q?rV4gWDY/buDZx42Zh6/RvVVMePhrpRWV2IlSIe2sjtXzxXD0yApwfBJzqei5?=
 =?us-ascii?Q?tC7mHKvhJ3R1V3Z6qN3PqQVRL3dHqGwvOWE22bFIUxp2VYAZjItjfIEtWDVt?=
 =?us-ascii?Q?fWHFFY2uOQzshXmmaJb+j8rdrNnYKTjM4AYpfngOtSZMmOR1FaP3jPj3Wjwr?=
 =?us-ascii?Q?6hj0jJwxbpmd95hhfmJ2KOjMJMchcWorZAreVi72d8XNYfaJk3FkntfpbUfK?=
 =?us-ascii?Q?PPtNs0kdoCFgqnPYqqIxInL7e2ROvH2pUXacGUW/EtIEqmDxaFaso3mR7Ook?=
 =?us-ascii?Q?yV/JsjGJ0VEMsRdKqe9vAnugxv4uEGPnXGAoMfkj68otpnfQa/rKBdMFpwBq?=
 =?us-ascii?Q?lO7JHIs8xns59z4hY18lpB5gBnF29hE9LR647WW9bcNRxHoOpKPhAh9hHpvs?=
 =?us-ascii?Q?clTVKRUReYLNcKrqve/oBaqp94L//25GjQKvbZIYY1sfn5/CjQVOAVV2v+Up?=
 =?us-ascii?Q?87SUZnLTNWod/tgDSrk5UgaEkx/IZ24L9/cT0wPjQcuqQclVQ2TaKMJBjlLD?=
 =?us-ascii?Q?Rva3u/fSmXGi6xpAMh0VyQ8f05zWdt5Rkjyzd3nuEzZoPcO2N0p/QUxIwWw4?=
 =?us-ascii?Q?I/C6d2jbDvxRgTfnSnrlwmsHV2vm3VTtoK5YUKaigYw/BGQ6ybiltFoyj8B7?=
 =?us-ascii?Q?6ccANI7BX25st/X9oKPlg9pwnwwfFGPnTp0ugfEoc7xkg10Owln8oIIdsecE?=
 =?us-ascii?Q?h2TuFlX/0FpnXM4cf7YQHjsskxJtlnSZxfs9fOuTeQU5zJtX9hIkfVp+ANcx?=
 =?us-ascii?Q?rVRgyDg7AfocPJY5bjt1IsEByUOIEH95VjJv9m472IP2cL9TPBZTEE2w1vDz?=
 =?us-ascii?Q?rKQ2/owtnZ6kGh89kkc+/UqDdHXK8tEhQ9xKmPTnRdiPhawmQ+5etAE962NC?=
 =?us-ascii?Q?Pv72UydZuRwxFs5GcQcBMh2s/EVJQwulTSQuIM6kQTmaPW4Iv4HzkzEsidnZ?=
 =?us-ascii?Q?V0u2G9/YAzSwmybJvAZ1iObLwIU83wiMzouzg6GKfigfMOYQVXg0G1VHvqk/?=
 =?us-ascii?Q?91/XZsr7ufM362FdImXnh8irppWcv61v/JimbLJKFiPYp2KBoYNlOIzPYx6l?=
 =?us-ascii?Q?Yfnx0rhGxrIQ8M0AxMxh+AWg/Q7Oti6ZahAUXrGXMklTQ1NpAbLaka05oy5c?=
 =?us-ascii?Q?sSDXodYExYR6dvmORf0urcCFfK/ousLBgyVtIS0A+qY/rhIHmVbNctISD9Rv?=
 =?us-ascii?Q?sPGijTDQxV3FCYKyO+oWC+/3c2Yxz8xZjG2u8iqjUtr8aiDK1w9W+Z/VRFA+?=
 =?us-ascii?Q?M/VGRYBKAhyz1SjNEuSkS7PUIqUiMldDcib1/9moarhSWeugyspF3oh5bEnl?=
 =?us-ascii?Q?PKkVHyBotKHn5YMotW2CFFmVeag/pC7K065Zd6S+V1lzGKsv17R7RVwsJyFy?=
 =?us-ascii?Q?VNq6a8E8sOViuQ8iPT/e28gB06GVv1r31Ik15eV7WlORKBdVChr9kLGuV8sN?=
 =?us-ascii?Q?llgLzMImZQgQhYZQfHlJyjNRSdgPBXwtMoXhFAu/oDHGNBbG/jtob3O5JM4T?=
 =?us-ascii?Q?SpE+dVJN+lkJYfhjZccFpIqFtasab7qS7peZgGDH?=
Content-Type: multipart/alternative;
	boundary="_000_BN8PR04MB6289F7099F7B38E5B08D85B7D0362BN8PR04MB6289namp_"
MIME-Version: 1.0
X-OriginatorOrg: snapon.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: BN8PR04MB6289.namprd04.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 33c4a609-81e8-4858-dda0-08dd13b81016
X-MS-Exchange-CrossTenant-originalarrivaltime: 03 Dec 2024 16:32:22.0970
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 9cbb18c7-7b64-4d6a-953b-09ec023c8c08
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 48JYj6o2AHWE2BeKRPi1bgbKQb/tssusdBSQhmvp9i0/7jht9+y00Ai3/JxHL/DY6A8yTGPsT8oNQCBjZQS5wYMgkI0Q4BbqeL6xC6ZhPv0=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN0PR04MB8062
X-Proofpoint-GUID: AoeL4Vexwr_hHq5HwEksQNPgNMiD2Vvf
X-Proofpoint-ORIG-GUID: AoeL4Vexwr_hHq5HwEksQNPgNMiD2Vvf
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.60.29
 definitions=2024-09-06_09,2024-09-06_01,2024-09-02_01
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 bulkscore=0 suspectscore=0
 impostorscore=0 clxscore=1031 mlxlogscore=999 spamscore=0
 lowpriorityscore=0 mlxscore=0 phishscore=0 malwarescore=0 adultscore=0
 priorityscore=1501 classifier=spam adjust=0 reason=mlx scancount=1
 engine=8.19.0-2411120000 definitions=main-2412030139
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/BN8PR04MB6289F7099F7B38E5B08D85B7D0362%40BN8PR04MB6289.namprd04.prod.outlook.com>
Precedence: bulk

--_000_BN8PR04MB6289F7099F7B38E5B08D85B7D0362BN8PR04MB6289namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

We have some batch queries that had occasionally having degraded runtimes: =
from 2 hours degrading to 16 hours, etc.

Comparing plans from good and bad runs, we saw that the good plans used ind=
ex-only scans on table "x", while the bad plans used index scans.

Using the pg_visibility utility, we found that all of the 83 partitions of =
table "x" were showing zero blocks where all tuples were visible. We ran a =
VACUUM on the table; the visibility maps are now clean and the good plans c=
ame back.

Our question is: why did autovacuum not spare us from this?

We are using default autovacuum parameters for all except log_autovacuum_mi=
n_duration=3D5000. These partitions are populated by processes that do a tr=
uncate + a single insert-select.

We see autovacuum failure (failed to get lock) messages, followed by a succ=
ess message, in the log for one of these partitions (the biggest one) but e=
ven that partition showed zero blocks with all tuples visible.

Are we wrong to expect autovacuum to clean up the visibility map?

postgres=3D# select version();
                                                 version
---------------------------------------------------------------------------=
-------------------------------
PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 202105=
14 (Red Hat 8.5.0-22), 64-bit

Thank you,
Mike Tefft

--_000_BN8PR04MB6289F7099F7B38E5B08D85B7D0362BN8PR04MB6289namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:11.0pt;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">We have some batch queries that had occasionally hav=
ing degraded runtimes: from 2 hours degrading to 16 hours, etc.<o:p></o:p><=
/p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Comparing plans from good and bad runs, we saw that =
the good plans used index-only scans on table &#8220;x&#8221;, while the ba=
d plans used index scans.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Using the pg_visibility utility, we found that all o=
f the 83 partitions of table &#8220;x&#8221; were showing zero blocks where=
 all tuples were visible. We ran a VACUUM on the table; the visibility maps=
 are now clean and the good plans came back.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Our question is: why did autovacuum not spare us fro=
m this? <o:p>
</o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">We are using default autovacuum parameters for all e=
xcept log_autovacuum_min_duration=3D5000. These partitions are populated by=
 processes that do a truncate + a single insert-select.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">We see autovacuum failure (failed to get lock) messa=
ges, followed by a success message, in the log for one of these partitions =
(the biggest one) but even that partition showed zero blocks with all tuple=
s visible.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Are we wrong to expect autovacuum to clean up the vi=
sibility map?
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">postgres=3D# select version();<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp; version<o:p></o:p></p>
<p class=3D"MsoNormal">----------------------------------------------------=
------------------------------------------------------<o:p></o:p></p>
<p class=3D"MsoNormal">PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by=
 gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Thank you,<o:p></o:p></p>
<p class=3D"MsoNormal">Mike Tefft<o:p></o:p></p>
</div>
</body>
</html>

--_000_BN8PR04MB6289F7099F7B38E5B08D85B7D0362BN8PR04MB6289namp_--