Message-ID: <f2ebeef82fddaf8a8152383f8ff62e75f20aac3c.camel@cybertec.at>
Subject: Re: analyze-in-stages post upgrade questions
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Zechman, Derek S" <Derek.S.Zechman@snapon.com>, Adrian Klaver
	 <adrian.klaver@aklaver.com>, "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Sat, 28 Jun 2025 07:25:30 +0200
In-Reply-To: <PH0PR04MB82943D67CD179002714AA503C044A@PH0PR04MB8294.namprd04.prod.outlook.com>
References: 
	<CO1PR04MB8281387B9AD9DE30976966BBC045A@CO1PR04MB8281.namprd04.prod.outlook.com>
		 <6add2a9a-7cf2-4d1b-8f3e-2e26a7ebe883@aklaver.com>
	 <4d8122febd3007143504e4b6034b4253f7000761.camel@cybertec.at>
	 <PH0PR04MB82943D67CD179002714AA503C044A@PH0PR04MB8294.namprd04.prod.outlook.com>
Content-Type: multipart/mixed; boundary="=-8AaAbFHnBpyzL9ZYv56l"
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/f2ebeef82fddaf8a8152383f8ff62e75f20aac3c.camel%40cybertec.at>
Precedence: bulk

--=-8AaAbFHnBpyzL9ZYv56l
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, 2025-06-28 at 01:23 +0000, Zechman, Derek S wrote:
> > Well, that wouldn't explain why it doesn't work on partitioned tables.
> > I am under the impression that it should.
> >
> > Derek, can cou share the pg_stats entries for the partitioned table?
>=20
> There are no entries in pg_stats for the parent table until after I manua=
lly run an analyze on it =E2=80=93 Example below

You are right.  I looked at the code, and "vacuumdb" does not process
partitiond tables, even if --analyze-only is specified.  I find that
surprising, aince the SQL command ANALYZE (without a table name) will
also collect statistics for partitioned tables.

I think that it would be a good idea to change that behavior.
In particular, it makes a lot of sense to collect statistics for
partitioned tables after a "pg_upgrade".

Attached is a patch to make "vacuumdb --analyze-only" consider
partitioned tables as well.

Yours,
Laurenz Albe

--=-8AaAbFHnBpyzL9ZYv56l
Content-Disposition: attachment;
	filename="v1-0001-Make-vacuumdb-Z-process-partitioned-tables.patch"
Content-Transfer-Encoding: base64
Content-Type: text/x-patch;
	name="v1-0001-Make-vacuumdb-Z-process-partitioned-tables.patch";
	charset="UTF-8"

RnJvbSAwN2NiZDQ5MTAxMWZmNWRhMTI0M2IxMTdjMTExZDUxNTMxMjkzNzgyIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5h
dD4KRGF0ZTogU2F0LCAyOCBKdW4gMjAyNSAwNzoyMDowMiArMDIwMApTdWJqZWN0OiBbUEFUQ0gg
djFdIE1ha2UgdmFjdXVtZGIgLVogcHJvY2VzcyBwYXJ0aXRpb25lZCB0YWJsZXMKCkF1dG9hbmFs
eXplIHdvbid0IHByb2Nlc3MgcGFydGl0aW9uZWQgdGFibGVzLCBidXQgYSBtYW51YWwgQU5BTFla
RQpvZiB0aGUgd2hvbGUgZGF0YWJhc2UgZG9lcy4gIFNvIGl0IHdhcyBzdXJwcmlzaW5nIHRoYXQg
cGFydGl0aW9uZWQKdGFibGVzIHdlcmUgbm90IHByb2Nlc3NlZCBieSAidmFjdXVtZGIgLS1hbmFs
eXplLW9ubHkiLgoKSW4gYWRkaXRpb24sICJ2YWN1dW1kYiAtLWFuYWx5emUtb25seSIgaXMgd2hh
dCB5b3UgcnVuIHRvIGNvbGxlY3QKbWlzc2luZyBzdGF0aXN0aWNzIGFmdGVyIGEgInBnX3VwZ3Jh
ZGUiLCBhbmQgaXQgbWFrZXMgYSBsb3Qgb2Ygc2Vuc2UKdG8gY29sbGVjdCBzdGF0aXN0aWNzIGZv
ciBwYXJ0aXRpb25lZCB0YWJsZXMgYXQgdGhhdCBwb2ludC4KCkhvd2V2ZXIsIHJ1bm5pbmcgVkFD
VVVNIG9uIHBhcnRpdGlvbmVkIHRhYmxlcyBhZGRzIG5vIGJlbmVmaXQgb3Zlcgp2YWN1dW1pbmcg
dGhlIHBhcnRpdGlvbnMsIGFuZCBWQUNVVU0gaXMgbW9yZSBleHBlbnNpdmUgdGhhbiBBTkFMWVpF
LApzbyB3ZSB3b24ndCB0cmVhdCBwYXJ0aXRpb25lZCB0YWJsZXMgdW5sZXNzIC0tYW5hbHl6ZS1v
bmx5IGlzIGdpdmVuLgpPdGhlcndpc2UsIHdlJ2QgZW5kIHVwIHZhY3V1bWluZyB0aGUgcGFydGl0
aW9ucyB0d2ljZSwgd2hpY2ggd291bGQKYmUgYSB3YXN0ZSBvZiByZXNvdXJjZXMuCi0tLQogZG9j
L3NyYy9zZ21sL3JlZi92YWN1dW1kYi5zZ21sIHwgIDQgKysrKwogc3JjL2Jpbi9zY3JpcHRzL3Zh
Y3V1bWRiLmMgICAgIHwgMjMgKysrKysrKysrKysrKysrKysrKy0tLS0KIDIgZmlsZXMgY2hhbmdl
ZCwgMjMgaW5zZXJ0aW9ucygrKSwgNCBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9kb2Mvc3Jj
L3NnbWwvcmVmL3ZhY3V1bWRiLnNnbWwgYi9kb2Mvc3JjL3NnbWwvcmVmL3ZhY3V1bWRiLnNnbWwK
aW5kZXggYjA2ODBhNjE4MTQuLjZhNGM4ZDUxYjdmIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwv
cmVmL3ZhY3V1bWRiLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL3JlZi92YWN1dW1kYi5zZ21sCkBA
IC00NDAsNiArNDQwLDEwIEBAIFBvc3RncmVTUUwgZG9jdW1lbnRhdGlvbgogICAgICAgPGxpc3Rp
dGVtPgogICAgICAgIDxwYXJhPgogICAgICAgICBPbmx5IGNhbGN1bGF0ZSBzdGF0aXN0aWNzIGZv
ciB1c2UgYnkgdGhlIG9wdGltaXplciAobm8gdmFjdXVtKS4KKyAgICAgICAgSWYgdGhhdCBvcHRp
b24gaXMgc3BlY2lmaWVkLCA8Y29tbWFuZD52YWN1dW1kYjwvY29tbWFuZD4gd2lsbCBhbHNvCisg
ICAgICAgIHByb2Nlc3MgcGFydGl0aW9uZWQgdGFibGVzLiAgV2l0aG91dCB0aGF0IG9wdGlvbiwg
b25seSB0aGUgcGFydGl0aW9ucworICAgICAgICB3aWxsIGJlIGNvbnNpZGVyZWQsIHVubGVzcyBh
IHBhcnRpdGlvbmVkIHRhYmxlIGlzIGV4cGxpY2l0bHkgc3BlY2lmaWVkCisgICAgICAgIHdpdGgg
dGhlIDxvcHRpb24+LS10YWJsZTwvb3B0aW9uPiBvcHRpb24uCiAgICAgICAgPC9wYXJhPgogICAg
ICAgPC9saXN0aXRlbT4KICAgICAgPC92YXJsaXN0ZW50cnk+CmRpZmYgLS1naXQgYS9zcmMvYmlu
L3NjcmlwdHMvdmFjdXVtZGIuYyBiL3NyYy9iaW4vc2NyaXB0cy92YWN1dW1kYi5jCmluZGV4IDc5
YjEwOTZlYjA4Li5kN2NhODMwMGY0MyAxMDA2NDQKLS0tIGEvc3JjL2Jpbi9zY3JpcHRzL3ZhY3V1
bWRiLmMKKysrIGIvc3JjL2Jpbi9zY3JpcHRzL3ZhY3V1bWRiLmMKQEAgLTkxMSwxMCArOTExLDI1
IEBAIHJldHJpZXZlX29iamVjdHMoUEdjb25uICpjb25uLCB2YWN1dW1pbmdPcHRpb25zICp2YWNv
cHRzLAogCSAqLwogCWlmICgob2JqZmlsdGVyICYgT0JKRklMVEVSX1RBQkxFKSA9PSAwKQogCXsK
LQkJYXBwZW5kUFFFeHBCdWZmZXJTdHIoJmNhdGFsb2dfcXVlcnksCi0JCQkJCQkJICIgQU5EIGMu
cmVsa2luZCBPUEVSQVRPUihwZ19jYXRhbG9nLj0pIEFOWSAoYXJyYXlbIgotCQkJCQkJCSBDcHBB
c1N0cmluZzIoUkVMS0lORF9SRUxBVElPTikgIiwgIgotCQkJCQkJCSBDcHBBc1N0cmluZzIoUkVM
S0lORF9NQVRWSUVXKSAiXSlcbiIpOworCQkvKgorCQkgKiBWQUNVVU1pbmcgcGFydGl0aW9uZWQg
dGFibGVzIHdvdWxkIGJlIHVucmVhc29uYWJseSBleHBlbnNpdmUsIHNpbmNlCisJCSAqIHRoYXQg
ZW50YWlscyBwcm9jZXNzaW5nIHRoZSBwYXJ0aXRpb25zIHR3aWNlIChvbmNlIGFzIHBhcnQgb2Yg
dGhlCisJCSAqIHBhcnRpdGlvbmVkIHRhYmxlLCBvbmNlIGFzIHRhYmxlcyBpbiB0aGVpciBvd24g
cmlnaHQpIGZvciBubworCQkgKiBiZW5lZml0LiBCdXQgaWYgd2Ugb25seSBBTkFMWVpFLCBjb2xs
ZWN0aW5nIHN0YXRpc3RpY3MgZm9yCisJCSAqIHBhcnRpdGlvbmVkIHRhYmxlcyBpcyB3b3J0aCB0
aGUgZWZmb3J0LgorCQkgKi8KKwkJaWYgKHZhY29wdHMtPmFuYWx5emVfb25seSkKKwkJCWFwcGVu
ZFBRRXhwQnVmZmVyU3RyKCZjYXRhbG9nX3F1ZXJ5LAorCQkJCQkJCQkgIiBBTkQgYy5yZWxraW5k
IE9QRVJBVE9SKHBnX2NhdGFsb2cuPSkgQU5ZIChhcnJheVsiCisJCQkJCQkJCSBDcHBBc1N0cmlu
ZzIoUkVMS0lORF9SRUxBVElPTikgIiwgIgorCQkJCQkJCQkgQ3BwQXNTdHJpbmcyKFJFTEtJTkRf
TUFUVklFVykgIiwgIgorCQkJCQkJCQkgQ3BwQXNTdHJpbmcyKFJFTEtJTkRfUEFSVElUSU9ORURf
VEFCTEUpICJdKVxuIik7CisJCWVsc2UKKwkJCWFwcGVuZFBRRXhwQnVmZmVyU3RyKCZjYXRhbG9n
X3F1ZXJ5LAorCQkJCQkJCQkgIiBBTkQgYy5yZWxraW5kIE9QRVJBVE9SKHBnX2NhdGFsb2cuPSkg
QU5ZIChhcnJheVsiCisJCQkJCQkJCSBDcHBBc1N0cmluZzIoUkVMS0lORF9SRUxBVElPTikgIiwg
IgorCQkJCQkJCQkgQ3BwQXNTdHJpbmcyKFJFTEtJTkRfTUFUVklFVykgIl0pXG4iKTsKKwogCX0K
IAogCS8qCi0tIAoyLjUwLjAKCg==


--=-8AaAbFHnBpyzL9ZYv56l--