Message-ID: <4d8122febd3007143504e4b6034b4253f7000761.camel@cybertec.at>
Subject: Re: analyze-in-stages post upgrade questions
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Adrian Klaver <adrian.klaver@aklaver.com>, "Zechman, Derek S"
	 <Derek.S.Zechman@snapon.com>, "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Fri, 27 Jun 2025 23:39:17 +0200
In-Reply-To: <6add2a9a-7cf2-4d1b-8f3e-2e26a7ebe883@aklaver.com>
References: 
	<CO1PR04MB8281387B9AD9DE30976966BBC045A@CO1PR04MB8281.namprd04.prod.outlook.com>
	 <6add2a9a-7cf2-4d1b-8f3e-2e26a7ebe883@aklaver.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/4d8122febd3007143504e4b6034b4253f7000761.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-06-27 at 08:31 -0700, Adrian Klaver wrote:
> On 6/27/25 06:35, Zechman, Derek S wrote:
> > We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and=
=20
> > performed the analyze-in-stages post upgrade.=C2=A0 It has been noticed=
 that=20
> > some plans changed to use hash joins instead of nested loops.=C2=A0 Fur=
ther=20
> > investigation found it was because the parent table of partitioned=20
> > tables did not have stats.=C2=A0 After running an ANALYZE on the parent=
=20
> > tables we got similar plan an execution times as before.
> >=20
> > I have two questions
> >=20
> > 1 - Why does analyze-in-stages not analyze the parent tables?
> >=20
> > 2 =E2=80=93 What happens if we do not run analyze-in-stages post upgrad=
e and=20
> > just run an analyze?
>=20
> It is spelled out in the docs:
>=20
> https://www.postgresql.org/docs/current/pgupgrade.html
>=20
> Emphasis added
>=20
> "Using vacuumdb --all --analyze-only can efficiently generate such=20
> statistics, and the use of --jobs can speed it up. Option=20
> --analyze-in-stages can be used to generate **minimal statistics**=20
> quickly. If vacuum_cost_delay is set to a non-zero value, this can be=20
> overridden to speed up statistics generation using PGOPTIONS, e.g.,=20
> PGOPTIONS=3D'-c vacuum_cost_delay=3D0' vacuumdb ...."
>=20
> and from here:
>=20
> https://www.postgresql.org/docs/current/app-vacuumdb.html
>=20
> "--analyze-in-stages
>=20
>      Only calculate statistics for use by the optimizer (no vacuum),=20
> like --analyze-only. Run three stages of analyze; the first stage uses=
=20
> the lowest possible statistics target (see default_statistics_target) to=
=20
> produce usable statistics faster, and subsequent stages build the full=
=20
> statistics.
>=20
>      This option is only useful to analyze a database that currently has=
=20
> no statistics or has wholly incorrect ones, such as if it is newly=20
> populated from a restored dump or by pg_upgrade. Be aware that running=
=20
> with this option in a database with existing statistics may cause the=20
> query optimizer choices to become transiently worse due to the low=20
> statistics targets of the early stages.

Well, that wouldn't explain why it doesn't work on partitioned tables.
I am under the impression that it should.

Derek, can cou share the pg_stats entries for the partitioned table?

Yours,
Laurenz Albe