From: "Zechman, Derek S" <Derek.S.Zechman@snapon.com>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: analyze-in-stages post upgrade questions
Thread-Topic: analyze-in-stages post upgrade questions
Thread-Index: AdvnaFRpmH7qjjvNR0OvGcPiFVITxg==
Date: Fri, 27 Jun 2025 13:35:27 +0000
Message-ID:
 <CO1PR04MB8281387B9AD9DE30976966BBC045A@CO1PR04MB8281.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?UsPphOWs12qTGrgK9bntTnrD4DLAMszulA8KacRCFYd8tlasueAG11Rk9Sso?=
 =?us-ascii?Q?kPKKbbjnSpZIFIFRoB0+f2rIchBh7Xsf6IcmGUzQO3Vmz7llNAdXRh7XZr45?=
 =?us-ascii?Q?Ro7SRyU/LjSF1lvXbwm81c92T7O5QiIBb1kM+dwtFdb1YAmYJAUIDWj/FcvG?=
 =?us-ascii?Q?kzt1V0e9dkZEgAi7z8PbvzkWw4te5TyAV5/Wmw6oH5lVfRgOQ+Ptf4aE4+k0?=
 =?us-ascii?Q?QhCRXr/5d6QrCRJ0xQbWrOHAJf+eVRFWG14X7u7Vab4ukS38N9u8LKZtd5d5?=
 =?us-ascii?Q?1z32aaIiMTp4xib7rF8GIbBgmJ4uYDalSJa5E3nqqeZ5XEdkCnRmiykfCxHj?=
 =?us-ascii?Q?0RruVXcAx2fUzD5c8yYS0ntHVsKjozR1OB2EEqKyhIv/xkMuv0TouX+8i+b2?=
 =?us-ascii?Q?zyDQPvJozYkkaELF2Id9+DU/INYyYbROJlm6Z9PNeQTdNe/QulTp4buLqUhh?=
 =?us-ascii?Q?P5eyl1qHlbyMadMdh4qE6p37OwxhPZvUasC3y1tTlrfmIw6veaGhtwiTaYII?=
 =?us-ascii?Q?+6Rq9VxyViXCel/jCj0H28007WTyODknf4AB+a4Ou8Ttx6+tukTkf2dVQGGY?=
 =?us-ascii?Q?bHTCjpgSh6AyR7M7WXcViCgPAq5lxo/0sxyd5GKqnv+xUXwRMMAJP1fneWKH?=
 =?us-ascii?Q?Y8mEuVgB28ScVkLFf/q9FI3EAeALMyt60O4uBqRiYOBPeu2EmWUhI95JyR8v?=
 =?us-ascii?Q?G2HbxYceL6DBc5YOjocu6cXEck22d/qmow5Gd4X9muwxKi8h/1YDp3pBh5Ru?=
 =?us-ascii?Q?ObSL1KFL+9Z4f4w2yxBrMlxbZbCf3q7gYuqQVL6LQ2fBG5HrsO8urbSYBHnw?=
 =?us-ascii?Q?ZA5Bp63/+SsR/EmGwEZ66Pvf0wsIII3LWk2jh8LIsABqGwyXYXAC/DMTy0AG?=
 =?us-ascii?Q?7XlA26dl85OVVN5YonCW2adZtGL9gf1IgC0jEQXkhsr1PzKglHoWvvBcquDX?=
 =?us-ascii?Q?/ZmTLNnRiNMOZqUmcVQEXofJKCgZK2jzwDHCdurdntvQyEtQrztmWG3tVGb+?=
 =?us-ascii?Q?hwreXexyEzc5EM+YlpyFD1ategdKmqNgQHe2x1BELBQgQyKWV4wpipXs9YaX?=
 =?us-ascii?Q?wjb+qRDEKOhYdpDB8eijx+0xwgq8Mj+JPW5zbAzJ7xpmaEYvfU5uSM5+iMUK?=
 =?us-ascii?Q?no0YdnPMIf2zok37VjyIxpMPFbj8culnbeEdBE8Q4D49quZ26BcVd3nOcNnk?=
 =?us-ascii?Q?r+SyHjL7rEnrPrPF/1y6nh3JR4S7elccaORSPkh91SKNpXEVB/LsdbiEnAz1?=
 =?us-ascii?Q?5/cwLjcqzIJIX/mdDH75bC66nF/3QMlUdUdogMrl5yGceXePEMw2Nq/u1MjP?=
 =?us-ascii?Q?MOO64Hj/AU/xBu/bnqg9RKCV/P/NsDOILIAqmjbMKJoAqdo6AL2bQuPA9FhN?=
 =?us-ascii?Q?XnvSBhUH5+v0ASB+/onUx295AzXIfA/XTiB/JxTckNIcInNR3PI6vA2TvN8t?=
 =?us-ascii?Q?L7Pr8Qz3ZHayE8iPQ6bU2pehAsDxJ52DDGwjmqLBI+6WXTc1IDR5nVhLoLiM?=
 =?us-ascii?Q?yhXFoEmWgwoIywdGH5jZOB3tz+bF2FgZTUmvzHLbW9Y4pUdBDM/ugeiWXAbA?=
 =?us-ascii?Q?Fq/EunbCc+DzcLU1848VaeeTTth8ZrItzBvYey6Q?=
Content-Type: multipart/alternative;
	boundary="_000_CO1PR04MB8281387B9AD9DE30976966BBC045ACO1PR04MB8281namp_"
MIME-Version: 1.0
X-OriginatorOrg: snapon.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: CO1PR04MB8281.namprd04.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 292d5ebd-33a2-4b2e-81e4-08ddb57f7a77
X-MS-Exchange-CrossTenant-originalarrivaltime: 27 Jun 2025 13:35:27.5471
 (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: RLVfwC+wTjiFFBuKS3Qxr4hGSnjbCTnSwaU19iUsdVlDtGectG0iNTRFva1uGovEQf5qcgLHRZA7rEilC68M3TfE2xS+6IEEgbQWAoI3V7I=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SA0PR04MB7340
X-Authority-Analysis: v=2.4 cv=BKGzrEQG c=1 sm=1 tr=0 ts=685e9e22 cx=c_pps
 a=kojefl/8vcI7rOD5WQpBNw==:117 a=lCpzRmAYbLLaTzLvsPZ7Mbvzbb8=:19
 a=wKuvFiaSGQ0qltdbU6+NXLB8nM8=:19 a=Ol13hO9ccFRV9qXi2t6ftBPywas=:19
 a=xqWC_Br6kY4A:10 a=6IFa9wvqVegA:10 a=Fe0VWxUp2Idrb-E6MNcA:9
 a=CjuIK1q_8ugA:10 a=yMhMjlubAAAA:8 a=SSmOFEACAAAA:8 a=nkDfODXXF6BDXKieU1wA:9
 a=G9vu6r3WBryJG3u6:21 a=gKO2Hq4RSVkA:10 a=UiCQ7L4-1S4A:10 a=hTZeC7Yk6K0A:10
 a=frz4AuCg-hUA:10
X-Proofpoint-ORIG-GUID: ZDzcScCWp0roHAdxpDcRbW4e60CRv3xw
X-Proofpoint-GUID: ZDzcScCWp0roHAdxpDcRbW4e60CRv3xw
X-Proofpoint-Spam-Details-Enc: AW1haW4tMjUwNjI3MDExMiBTYWx0ZWRfX7CvQiuKcR5ls
 s/4qtOdHtB6sfL6vr/JVSUn2GXJsZH2iLmkPDliwo8W49epyE2Y1JExY61ahfkYatx9mq5tgTcx
 0KIPdmo6eRx67QT6Ix7qQidSSbYdsRAOpSkyXjCdF9o26m8u+/xJd0dSY9Nhxj5UDFGy47zilR5
 O3SxwyWxSS3JjRNp3omKElozan7K7bmuG4ivfdEz7N4woV5wYGD5R63KsTPhR8icHXofKZ60Os/
 r3W37OV6n1tdInd/fj+X9fjRHDtSDdVJoMeT88RewVKvK4138UsKO18onh32OFb6fl1Bpg+Glwp
 0XlVLgztPlrs5VPPUhnQPALik48//vnFUJxJIgNnyNPgI4Jwgf9XnPsQeAeL97pbHMOIzB0zaaI
 hdQ7vuGdXxTAQJX8ZUVQzaxQq13D5Rd6WdbLzgk7LcqCVCfDjkSkT6FZSsQIyED5KK80mrNH
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1099,Hydra:6.1.7,FMLib:17.12.80.40
 definitions=2025-06-27_04,2025-06-26_05,2025-03-28_01
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0
 impostorscore=0 spamscore=0 clxscore=1011 adultscore=0 lowpriorityscore=0
 mlxscore=0 mlxlogscore=392 malwarescore=0 priorityscore=1501 phishscore=0
 bulkscore=0 suspectscore=0 classifier=spam authscore=0 authtc=n/a authcc=
 route=outbound adjust=0 reason=mlx scancount=1 engine=8.19.0-2505280000
 definitions=main-2506270112
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/CO1PR04MB8281387B9AD9DE30976966BBC045A%40CO1PR04MB8281.namprd04.prod.outlook.com>
Precedence: bulk

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


We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and perfo=
rmed the analyze-in-stages post upgrade.  It has been noticed that some pla=
ns changed to use hash joins instead of nested loops.  Further investigatio=
n found it was because the parent table of partitioned tables did not have =
stats.  After running an ANALYZE on the parent tables we got similar plan a=
n execution times as before.

I have two questions
1 - Why does analyze-in-stages not analyze the parent tables?
2 - What happens if we do not run analyze-in-stages post upgrade and just r=
un an analyze?

Thanks,
Sean

--_000_CO1PR04MB8281387B9AD9DE30976966BBC045ACO1PR04MB8281namp_
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:12.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;}
@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"><font size=3D"3" face=3D"Aptos"><span style=3D"font-=
size:12.0pt"><o:p>&nbsp;</o:p></span></font></p>
<p class=3D"MsoNormal"><font size=3D"3" face=3D"Aptos"><span style=3D"font-=
size:12.0pt">We recently performed an upgrade from pg14 (14.18) to pg16 (16=
.9) and performed the analyze-in-stages post upgrade.&nbsp; It has been not=
iced that some plans changed to use hash joins
 instead of nested loops.&nbsp; Further investigation found it was because =
the parent table of partitioned tables did not have stats.&nbsp; After runn=
ing an ANALYZE on the parent tables we got similar plan an execution times =
as before.<o:p></o:p></span></font></p>
<p class=3D"MsoNormal"><font size=3D"3" face=3D"Aptos"><span style=3D"font-=
size:12.0pt"><o:p>&nbsp;</o:p></span></font></p>
<p class=3D"MsoNormal"><font size=3D"3" face=3D"Aptos"><span style=3D"font-=
size:12.0pt">I have two questions
<o:p></o:p></span></font></p>
<p class=3D"MsoNormal"><font size=3D"3" face=3D"Aptos"><span style=3D"font-=
size:12.0pt">1 - Why does analyze-in-stages not analyze the parent tables?&=
nbsp;
<o:p></o:p></span></font></p>
<p class=3D"MsoNormal"><font size=3D"3" face=3D"Aptos"><span style=3D"font-=
size:12.0pt">2 &#8211; What happens if we do not run analyze-in-stages post=
 upgrade and just run an analyze?<o:p></o:p></span></font></p>
<p class=3D"MsoNormal"><font size=3D"3" face=3D"Aptos"><span style=3D"font-=
size:12.0pt"><o:p>&nbsp;</o:p></span></font></p>
<p class=3D"MsoNormal"><font size=3D"3" face=3D"Aptos"><span style=3D"font-=
size:12.0pt">Thanks,<o:p></o:p></span></font></p>
<p class=3D"MsoNormal"><font size=3D"3" face=3D"Aptos"><span style=3D"font-=
size:12.0pt">Sean<o:p></o:p></span></font></p>
</div>
</body>
</html>

--_000_CO1PR04MB8281387B9AD9DE30976966BBC045ACO1PR04MB8281namp_--