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
