Feedback-ID: i76984098:Fastmail
Message-ID: <6add2a9a-7cf2-4d1b-8f3e-2e26a7ebe883@aklaver.com>
Date: Fri, 27 Jun 2025 08:31:08 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: analyze-in-stages post upgrade questions
To: "Zechman, Derek S" <Derek.S.Zechman@snapon.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <CO1PR04MB8281387B9AD9DE30976966BBC045A@CO1PR04MB8281.namprd04.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CO1PR04MB8281387B9AD9DE30976966BBC045A@CO1PR04MB8281.namprd04.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/6add2a9a-7cf2-4d1b-8f3e-2e26a7ebe883%40aklaver.com>
Precedence: bulk

On 6/27/25 06:35, Zechman, Derek S wrote:
> We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and 
> performed the analyze-in-stages post upgrade.  It has been noticed that 
> some plans changed to use hash joins instead of nested loops.  Further 
> investigation 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 an 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 run an analyze?

It is spelled out in the docs:

https://www.postgresql.org/docs/current/pgupgrade.html

Emphasis added

"Using vacuumdb --all --analyze-only can efficiently generate such 
statistics, and the use of --jobs can speed it up. Option 
--analyze-in-stages can be used to generate **minimal statistics** 
quickly. If vacuum_cost_delay is set to a non-zero value, this can be 
overridden to speed up statistics generation using PGOPTIONS, e.g., 
PGOPTIONS='-c vacuum_cost_delay=0' vacuumdb ...."

and from here:

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 optimizer choices to become transiently worse due to the low 
statistics targets of the early stages.
"

> 
> Thanks,
> 
> Sean
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com