Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uV9L4-00EJKz-JU for pgsql-general@arkaria.postgresql.org; Fri, 27 Jun 2025 13:41:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uV9L1-0025gp-Mh for pgsql-general@arkaria.postgresql.org; Fri, 27 Jun 2025 13:41:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uV9L1-0025gg-BP for pgsql-general@lists.postgresql.org; Fri, 27 Jun 2025 13:41:55 +0000 Received: from mail-oa1-x2f.google.com ([2001:4860:4864:20::2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uV9Kz-004PcD-2k for pgsql-general@lists.postgresql.org; Fri, 27 Jun 2025 13:41:55 +0000 Received: by mail-oa1-x2f.google.com with SMTP id 586e51a60fabf-2ef891cd058so1586302fac.1 for ; Fri, 27 Jun 2025 06:41:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1751031711; x=1751636511; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=1fVR6sXIy2lMbkbvUnxdyUrCcg/2uF6NllhpQfzBxAM=; b=i2rgXu8Go3+qpvfQ0n9oPfNzSrmymLov/pUt839hDLff6R7rB0rdAeE2fCUqhxK/8v sExHCmBZU3uCNp4wlrWVEHXBvDrvLODHvXVnNz+DiLsFbDYYVcxWNz3lOa3m5S2gI97k z39be0HETDR+kGjkwFogPHjVjxBC4lYPi1PNkHSheZCXREhRdrVWmaT+B6uKwaypVaWY GvSJ2G5NkwpmzFOh3apISYJx5YevJmOgW7ReJo7klLYKFZ2xsK20CINEjMqtAflgnc8o cFxJMiRVPVyGnZBoPiqHBh/iJSqtrmP4HZGoCWhIPNufZCshaGIitG5uvDP90rorr6UB oqfg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1751031711; x=1751636511; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=1fVR6sXIy2lMbkbvUnxdyUrCcg/2uF6NllhpQfzBxAM=; b=NmFR00UBhpwUwUs5+Xk2MrOrc5AWNWTy2odC8Atd9vLI0jMSjbiduPy76Ja57iCJt5 f/xzYyHVN+DbxOW9KQZ51lEQRgOcX9QY40V7U/fdH/CG//JLM8s/qGELQG3UMcmlotWH mwohw5XyewAladcjlqfh6OJEPeipmrsfJ2vMnc+tMsclp11bG43IYIHpMSFIEy5NzS5v XwllpBfPaG6w3cMTEuB/sTfaLdmklvSpnQfdF/4DMeBEIGQOvSRNW109fOi7sH+UMhUa LBp1XUOPx/WifBkGLLCP/laxw7nYLXIju2p4KHvycO6sTg2nwOhrIh208YhNjkBhJECJ RatQ== X-Gm-Message-State: AOJu0YyaOLrOB2FcrVLbmSXvQc1Lb6XMC8S14YsaSNis3KoGTq5rEHea QOzu5z89vd/TbaYrzk70XGPUXGLx/Y/c/A0G+vs8SZN58KF4qFWew31h4BOZHt9onVbPsM3p4s5 7/b/9M6Xd+u8whDTnAvjB8nCM+txhWycqEQ== X-Gm-Gg: ASbGncuDdMx4Vx0wjlHpXywD8K+P27iG2C1Kd6JiKJ/6/+Y6zbQ0awGrRsjpjZokpJb pYPvMkL7hziB5MiQ0LA2ukGnqIJV32zVjF7vCGbFnXVgLCTqcaeJxUKqvxXW4/c5gOQ3eT9sukV opQ0QnKQhWOeWtxP/k6xpSkdVhYz0vdwjraLXzJuKkt4hu X-Google-Smtp-Source: AGHT+IHYrs+32lKs0XtBfR+4hoDeBmhoQCHSpQPbQcBAQCht0vzaMKR0yc7UUuPYALrRvpCYlBUGa6aZIwn3ZmcefF8= X-Received: by 2002:a05:6871:b26:b0:2d5:2955:aa58 with SMTP id 586e51a60fabf-2efed114d93mr1829319fac.0.1751031711161; Fri, 27 Jun 2025 06:41:51 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Fri, 27 Jun 2025 09:41:39 -0400 X-Gm-Features: Ac12FXy_iC0L72agV9JM9ZqiPYsVVMZ3OkJvZRQK_SLsSnJVPzQRUZ1cmVIrC6k Message-ID: Subject: Re: analyze-in-stages post upgrade questions To: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000022625b06388dd7cd" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000022625b06388dd7cd Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Jun 27, 2025 at 9:35=E2=80=AFAM 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 =E2=80=93 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=3D$mumble your_db" take? --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000022625b06388dd7cd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Jun 27, 2025 at 9:35=E2=80=AFAM Z= echman, Derek S <Derek.S.Z= echman@snapon.com> wrote:

=C2=A0

We recently performed an upgrade from pg14 (14.18) to pg16 (16.9= ) and performed the analyze-in-stages post upgrade.=C2=A0 It has been notic= ed that some plans changed to use hash joins instead of nested loops.=C2=A0 Further investigation found it was because = the parent table of partitioned tables did not have stats.=C2=A0 After runn= ing an ANALYZE on the parent tables we got similar plan an execution times = as before.

=C2=A0

I have two questions

1 - Why does analyze-in-stages not analyze the parent tables?=C2= =A0

2 =E2=80=93 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 an= y=C2=A0statistics on a given table until the ANALYZE on that table comp= letes.

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

--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--00000000000022625b06388dd7cd--