Horton
Menu

Re: analyze-in-stages post upgrade questions

Ron Johnson <ronljohnsonjr@gmail.com>

View thread
From: Ron Johnson <ronljohnsonjr@gmail.com>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Date: 2025-06-27T13:41:39Z
Lists: pgsql-hackers, pgsql-general 
On Fri, Jun 27, 2025 at 9:35 AM Zechman, Derek S <Derek.S.Zechman@snapon.com>
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 takes more time, and you don't have *any* statistics on a given table
until the ANALYZE on that table completes.

How long did "vacuumdb --analyze-only --jobs=$mumble your_db" take?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. vacuumdb: Make vacuumdb --analyze-only process partitioned tables.

  2. Integrate FullTransactionIds deeper into two-phase code