Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tjfkZ-00CMW9-Dh for pgsql-general@arkaria.postgresql.org; Sun, 16 Feb 2025 14:36:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tjfkW-008tQb-0f for pgsql-general@arkaria.postgresql.org; Sun, 16 Feb 2025 14:36:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tjfkV-008tQT-Ih for pgsql-general@lists.postgresql.org; Sun, 16 Feb 2025 14:35:59 +0000 Received: from mail-oa1-x2d.google.com ([2001:4860:4864:20::2d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tjfkT-001AnN-0O for pgsql-general@postgresql.org; Sun, 16 Feb 2025 14:35:59 +0000 Received: by mail-oa1-x2d.google.com with SMTP id 586e51a60fabf-2bcceee7b41so20790fac.2 for ; Sun, 16 Feb 2025 06:35:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1739716553; x=1740321353; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=7ufUbKcBva8zu8PCg8mAuWRSSKyZLCgyVP5plQXVAgA=; b=GWrJvxI7SITja0LeNFrN8NB4GBN0o6LBYdtSPwfdsM6YZCv+7HoYjs7s6AzNUVpLdr oARC/TNq+xLMzHAKnLon88ZDB9Xkddt/6M1ma5sfGCiJrKmfOlpTdAscYqm5KsGwJcgk lI9GJu9SZDJ04dMk+JtRSatolQJWF6i7gy0JtEGH+5rz0y4XqV/51T7xO4UKvL7ldf2R 27VacmI8WGUg7Xep8nHmR/8dqn6dpG/enYu7uJ6ilbrAJrLfyzZZdXO6XacEbZFMgs0X dfNWFMTrWQO6jNi4tyPEYZF7sI+Rh26TVgoV5az4eJK3mup5/N9xmqCujUYvE+Wsk/dK lmqw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1739716553; x=1740321353; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=7ufUbKcBva8zu8PCg8mAuWRSSKyZLCgyVP5plQXVAgA=; b=kw6a990Y8MH7n7KczfkFAChId/BpHazMjLIqmXiihd5p6jaH5SNlUo0uhQyDZknWkH bFzjqRszBRDeQVEKkCiuvDqQ8GwJIFiKtKACaRABTAWmgHFQFtJroAzFMCCUaCJ8Ln/+ eNKUFeEyvH1W2WzDjWZ0/3TD4xSvv6klJd2ngINDoVVnz6qjuuCrRiFCk4oDuYpKspyM ynYeTpN/Yl1StcZObpIYDhIYs70rZxaFnsSnh38GCgJxTy1bovjNr35PQ8iNuN7a+3HN /9K8YOe2ilAyvOapznz4x9GTTMqU5zsEixPwi51ENZnIOzhxXR+XLC4IabnV0uYsIas/ 3Z4Q== X-Gm-Message-State: AOJu0Yznj4mFTb4mDZyKUMU3JCBND/jxgfB/Ve+cFgdbJDkBRtKqHuiS 6NxDRGTIgXQclLCttt9kzhp3sJZYTMWNn85cRTbkGlsN2+eS6UdFyhshZCJGexEUAdYrTtoTzVV qcuymwPqsPucQoKbLBCe6ycl5S5f/lKoi X-Gm-Gg: ASbGncvIs8iSziVZs+BsNvgOcBlhi+uIjbEXRB85LM67waIV5SvMpIdyqupEd1jnI9s eiZS+qHSKd0SFAC4CZe+dzOQYyX5jSMafLa9aBBGWnU+yQosMvyUg0SLJoCOI2ViDgVkrzP8lKQ == X-Google-Smtp-Source: AGHT+IFOHWZ5reDPSwo6YWy9ICuGFVYOv+A/Rk/jE3sZ1m0mZNWlMdXOQ1D37cv+5YwBtI4h0018p2JLVhuvqE6Qkfk= X-Received: by 2002:a05:6870:d90:b0:29e:62a6:de9 with SMTP id 586e51a60fabf-2bc99ab3295mr2998848fac.17.1739716552851; Sun, 16 Feb 2025 06:35:52 -0800 (PST) MIME-Version: 1.0 References: <0558ddd4d71641bdb41fa49b2425f73c@safrangroup.com> <07ab2d83-ffe5-4bec-9626-22a68f732579@aklaver.com> <273a88dc-4134-47d5-bc19-30ff5f97926c@aklaver.com> <498dfb34-4dd7-4f48-8188-355e1488d7e6@aklaver.com> <1061066336.5835157.1733316137292@mail.yahoo.com> <1482982714.8486017.1735661703839@mail.yahoo.com> <1763130721.4001842.1737733841628@mail.yahoo.com> <132487461.4068668.1737741687606@mail.yahoo.com> <0dc06cb7-33cc-43ba-a95f-535fdf0a0439@aklaver.com> <1751608443.5432365.1738081421269@mail.yahoo.com> <74599d1d-c8a2-4e59-a50d-019dcc973de8@aklaver.com> <200665967.5560583.1738095230696@mail.yahoo.com> <21b5d62a-19d1-413f-9d5e-d681cd2bb91b@aklaver.com> <47454513.6047834.1738179914107@mail.yahoo.com> <1841861276.9581730.1738888679871@mail.yahoo.com> <56243553.9616888.1738893835649@mail.yahoo.com> <940531722.732202.1739711614045@mail.yahoo.com> In-Reply-To: <940531722.732202.1739711614045@mail.yahoo.com> From: Ron Johnson Date: Sun, 16 Feb 2025 09:35:41 -0500 X-Gm-Features: AWEUYZmxnwnBUIU-k8F4ypfOhH3qz68riNaUaJsYv4sISlL9-qOwrqUZZn5I144 Message-ID: Subject: Re: Help in vetting outcome of "vacuumdb --analyze-in-stages" - during DB Upgrade from EC2- PGS - Community Edn ver 13.X to 14.X To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000248cad062e43530c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000248cad062e43530c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Feb 16, 2025 at 8:13=E2=80=AFAM Y_Bharani_mbsv = wrote: > Team > Good Morning. > As part of DB upgrade from EC2 - PGS - community Edn Ver 13.X to 14.X > I followed steps of "pg_upgrade" and had executed the last step (post > successful db migration) > > vacuumdb --analyze-in-stages > > and later noticed an caveat > url =3D https://www.postgresql.org/docs/current/app-vacuumdb.html > > > --analyze-in-stages > > Only calculate statistics for use by the optimizer (no vacuum), like > --analyze-only. Run three stages of analyze; the first stage uses the > lowest possible statistics target (see default_statistics_target > ) > to produce usable statistics faster, and subsequent stages build the full > statistics. > > This option is only useful to analyze a database that currently has no > statistics or has wholly incorrect ones, such as if it is newly populated > from a restored dump or by pg_upgrade. *Be aware that running with this > option in a database with existing statistics may cause the query optimiz= er > choices to become transiently worse due to the low statistics targets of > the early stages.* > > How to overcome the issue to avoid "transiently worse" > "Transiently" means "temporarily". And since pg_upgrade does not carry over optimizer statistics, query optimizer choices would be transiently worse *anyway* until the ANALYZE completes. > Later, I too did > a) vacuum(full,verbose,skip_locked) ... each table wise > Why? It certainly didn't do what you think it did. (This is why giving "rewrite the whole table" the name VACUUM FULL was a horrible idea.) > b) analyze (verbose,skip_locked) .. each table wise > Any guidance > You wasted much time and effort. Best to have just waited until the --analyze-in-stages had completed. -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000248cad062e43530c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sun, Feb 16, 2025 at 8:13=E2=80=AFAM Y= _Bharani_mbsv <mailbsv@yahoo.com> wrote:
Team
Good Morning.
As part of DB upgrade from EC2 - PGS - community Edn Ver 13.X = to 14.X=C2=A0
I followed steps of "pg_upgrade&qu= ot; and had executed the last step (post successful db migration)

vacuumdb --analyze-in-stages<= /span>

and later noticed an caveat


--analyze-in-stages=

Only calculate statistics for use by the optimizer (no vacuum), like=C2= =A0--analyze-only. Run three stages of analyze; = the first stage uses the lowest possible statistics target (see=C2=A0default_statistics_target) to= produce usable statistics faster, and subsequent stages build the full sta= tistics.

This option is only useful to ana= lyze a database that currently has no statistics or has wholly incorrect on= es, such as if it is newly populated from a restored dump or by=C2=A0pg_upgrade. Be aware that running with this option in a database with existing sta= tistics may cause the query optimizer choices to become transiently worse d= ue to the low statistics targets of the early stages.


How to overcome the issue to avoid "trans= iently worse"

"= Transiently" means "temporarily".

A= nd since pg_upgrade does not carry over optimizer statistics, query optimiz= er choices would be transiently worse anyway=C2=A0until the ANALYZE = completes.
=C2=A0
=C2=A0 Later, I too did=C2=A0=
a) vacuum(full,verbose,skip_locked) ... each= table wise=C2=A0

Why?=C2=A0 It certainly didn't do what you think it did.
(This is why giving "rewrite the whole table" the nam= e VACUUM FULL was a horrible idea.)
=C2=A0
b) a= nalyze (verbose,skip_locked) .. each table wise
=
=C2=A0Any gui= dance

You wasted much time and effort.=C2=A0 Best to have just waited un= til the=C2=A0--analyze-in-stages had completed.

--=
Death to = <Redacted>, and butter sauce.
Don't boil me, I'm still al= ive.
<Redacted> lobster!
--000000000000248cad062e43530c--