On Sun, Feb 16, 2025 at 8:13 AM Y_Bharani_mbsv <mailbsv@xxxxxxxxx> wrote:
TeamGood Morning.As part of DB upgrade from EC2 - PGS - community Edn Ver 13.X to 14.XI followed steps of "pg_upgrade" and had executed the last step (post successful db migration)vacuumdb --analyze-in-stagesand later noticed an caveat
--analyze-in-stagesOnly 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.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 dida) 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 wiseAny guidance
You wasted much time and effort. Best to have just waited until the --analyze-in-stages had completed.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
