Message-ID: <768b2a237e892bf1334bdcbb066cdc1bd1368cb2.camel@j-davis.com>
Subject: Re: Statistics Import and Export
From: Jeff Davis <pgsql@j-davis.com>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Corey Huinker
	 <corey.huinker@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>, 
 Michael Paquier <michael@paquier.xyz>, Nathan Bossart
 <nathandbossart@gmail.com>, Bruce Momjian <bruce@momjian.us>,  Matthias van
 de Meent <boekewurm+postgres@gmail.com>, Magnus Hagander
 <magnus@hagander.net>, Stephen Frost <sfrost@snowman.net>,  Peter Smith
 <smithpb2250@gmail.com>, PostgreSQL Hackers
 <pgsql-hackers@lists.postgresql.org>,  alvherre@alvh.no-ip.org, jian he
 <jian.universality@gmail.com>
Date: Thu, 27 Feb 2025 18:32:20 -0800
In-Reply-To: <CAExHW5vf9D+8-a5_BEX3y=2y_xY9hiCxV1=C+FnxDvfprWvkng@mail.gmail.com>
References: 
	<CADkLM=f0a43aTd88xW4xCFayEF25g-7hTrHX_WhV40HyocsUGg@mail.gmail.com>
	 <97b451228227c555be1a4f79c4a62ddec9a74f06.camel@j-davis.com>
	 <CADkLM=ckXu5wzSQZ3Y_fvAaL8rZ+upZdSecBLf5FCTMj7M6T-A@mail.gmail.com>
	 <b227c4fca29dfb893bb414ce6d84fc2377489ecc.camel@j-davis.com>
	 <CADkLM=dRMC6t8gp9GVf6y6E_r5EChQjMAAh_vPyih_zMiq0zvA@mail.gmail.com>
	 <y2d36zf6pl3n6ni6e4ctblu3a5iazi2dycknq53h2mjvp7lto4@hhww3h2qgdrw>
	 <1457469.1740419458@sss.pgh.pa.us>
	 <CADkLM=eWSv2z_DVB=psfwHitq7DbFJQaP9A46R27eARC-4OYtQ@mail.gmail.com>
	 <cpdanvzykcb5o64rmapkx6n5gjypoce3y52hff7ocxupgpbxu4@53jmlyvukijo>
	 <CADkLM=dX67xer59tVYMCQZ+pHAT2sCyyrUDzeDCdW_TdxqwOgA@mail.gmail.com>
	 <curtrasm6ylt7es2ukfuals2mh5an72yzxgvyel65mw3ere7qs@nw6m4ffpo4m3>
	 <8d13e4296408c53a93390525d0bcb906cbd13df4.camel@j-davis.com>
	 <CACJufxHG9MBQozbJQ4JRBcRbUO+t+sx4qLZX092rS_9b4SR_EA@mail.gmail.com>
	 <CAExHW5vf9D+8-a5_BEX3y=2y_xY9hiCxV1=C+FnxDvfprWvkng@mail.gmail.com>
Content-Type: multipart/mixed; boundary="=-Q0F+4LC/PGyd4BEWXhJ0"
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/768b2a237e892bf1334bdcbb066cdc1bd1368cb2.camel%40j-davis.com>
Precedence: bulk

--=-Q0F+4LC/PGyd4BEWXhJ0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, 2025-02-25 at 11:11 +0530, Ashutosh Bapat wrote:
> So the dumped statistics are not restored exactly. The reason for
> this
> is the table statistics is dumped before dumping ALTER TABLE ... ADD
> CONSTRAINT command which changes the statistics. I think all the
> pg_restore_relation_stats() calls should be dumped after all the
> schema and data modifications have been done. OR what's the point in
> dumping statistics only to get rewritten even before restore
> finishes.

In your example, it's not so bad because the stats are actually better:
the index is built after the data is present, and therefore relpages
and reltuples are correct.

The problem is more clear if you use --no-data. If you load data,
ANALYZE, pg_dump --no-data, then reload the sql file, then the stats
are lost.

That workflow is very close to what pg_upgrade does. We solved the
problem for pg_upgrade in commit 71b66171d0 by simply not updating the
statistics when building an index and IsBinaryUpgrade.

To solve the issue with dump --no-data, I propose that we change the
test in 71b66171d0 to only update the stats if the physical relpages is
non-zero.

Patch attached:

 * If the dump is --no-data, or during pg_upgrade, the table will be
empty, so the physical relpages will be zero and the restored stats
won't be overwritten.

 * If (like in your example) the dump includes data, the new stats are
based on real data, so they are better anyway. This is sort of like the
case where autoanalyze kicks in.

 * If the dump is --statistics-only, then there won't be any indexes
created in the SQL file, so when you restore the stats, they will
remain until you do something else to change them.

 * If your example really is a problem, you'd need to dump first with -
-no-statistics, and then with --statistics-only, and restore the two
SQL files in order.


Alternatively, we could put stats into SECTION_POST_DATA, which was
already discussed[*], and we decided against it (though there was not a
clear consensus).

Regards,
	Jeff Davis

*:
https://www.postgresql.org/message-id/1798867.1712376328%40sss.pgh.pa.us

--=-Q0F+4LC/PGyd4BEWXhJ0
Content-Disposition: attachment;
	filename*0=v1-0001-Do-not-update-stats-on-empty-table-when-building-.pat;
	filename*1=ch
Content-Type: text/x-patch;
	name="v1-0001-Do-not-update-stats-on-empty-table-when-building-.patch";
	charset="UTF-8"
Content-Transfer-Encoding: base64

RnJvbSA4NmMwM2NiNTI1YjQ5ZDI0MDE5YzVjMGVhOGVjMzZiYjgyYjNjNThhIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBKZWZmIERhdmlzIDxqZWZmQGotZGF2aXMuY29tPgpEYXRlOiBU
aHUsIDI3IEZlYiAyMDI1IDE3OjA2OjAwIC0wODAwClN1YmplY3Q6IFtQQVRDSCB2MV0gRG8gbm90
IHVwZGF0ZSBzdGF0cyBvbiBlbXB0eSB0YWJsZSB3aGVuIGJ1aWxkaW5nIGluZGV4LgoKV2UgcHJl
dmlvdXNseSBmaXhlZCB0aGlzIGZvciBiaW5hcnkgdXBncmFkZSBpbiA3MWI2NjE3MWQwLCBidXQg
YQpzaW1pbGFyIHByb2JsZW0gZXhpc3RzIHdoZW4gdXNpbmcgcGdfZHVtcCAtLW5vLWRhdGEgd2l0
aG91dCBwZ191cGdyYWRlCmludm9sdmVkLiBGaXggYm90aCBwcm9ibGVtcyBieSBub3QgdXBkYXRp
bmcgdGhlIHN0YXRzIHdoZW4gdGhlIHRhYmxlCmhhcyBubyBwYWdlcy4KClJlcG9ydGVkLWJ5OiBB
c2h1dG9zaCBCYXBhdCA8YXNodXRvc2guYmFwYXQub3NzQGdtYWlsLmNvbT4KRGlzY3Vzc2lvbjog
aHR0cHM6Ly9wb3N0Z3IuZXMvbS9DQUV4SFc1dmY5RCs4LWE1X0JFWDN5PTJ5X3hZOWhpQ3hWMT1D
K0ZueER2ZnByV3ZrbmdAbWFpbC5nbWFpbC5jb20KLS0tCiBzcmMvYmFja2VuZC9jYXRhbG9nL2lu
ZGV4LmMgICAgICAgICAgICAgICAgfCAxNyArKysrKysrKysrKy0tLS0tLQogc3JjL3Rlc3QvcmVn
cmVzcy9leHBlY3RlZC9zdGF0c19pbXBvcnQub3V0IHwgMjIgKysrKysrKysrKysrKysrKysrKysr
LQogc3JjL3Rlc3QvcmVncmVzcy9zcWwvc3RhdHNfaW1wb3J0LnNxbCAgICAgIHwgMTIgKysrKysr
KysrKysrCiAzIGZpbGVzIGNoYW5nZWQsIDQ0IGluc2VydGlvbnMoKyksIDcgZGVsZXRpb25zKC0p
CgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvY2F0YWxvZy9pbmRleC5jIGIvc3JjL2JhY2tlbmQv
Y2F0YWxvZy9pbmRleC5jCmluZGV4IGYzN2I5OTBjODFkLi4xYTNmZGVhYjM1MCAxMDA2NDQKLS0t
IGEvc3JjL2JhY2tlbmQvY2F0YWxvZy9pbmRleC5jCisrKyBiL3NyYy9iYWNrZW5kL2NhdGFsb2cv
aW5kZXguYwpAQCAtMjgzMywxMSArMjgzMyw3IEBAIGluZGV4X3VwZGF0ZV9zdGF0cyhSZWxhdGlv
biByZWwsCiAJaWYgKHJlbHR1cGxlcyA9PSAwICYmIHJlbC0+cmRfcmVsLT5yZWx0dXBsZXMgPCAw
KQogCQlyZWx0dXBsZXMgPSAtMTsKIAotCS8qCi0JICogRG9uJ3QgdXBkYXRlIHN0YXRpc3RpY3Mg
ZHVyaW5nIGJpbmFyeSB1cGdyYWRlLCBiZWNhdXNlIHRoZSBpbmRleGVzIGFyZQotCSAqIGNyZWF0
ZWQgYmVmb3JlIHRoZSBkYXRhIGlzIG1vdmVkIGludG8gcGxhY2UuCi0JICovCi0JdXBkYXRlX3N0
YXRzID0gcmVsdHVwbGVzID49IDAgJiYgIUlzQmluYXJ5VXBncmFkZTsKKwl1cGRhdGVfc3RhdHMg
PSByZWx0dXBsZXMgPj0gMDsKIAogCS8qCiAJICogRmluaXNoIEkvTyBhbmQgdmlzaWJpbGl0eSBt
YXAgYnVmZmVyIGxvY2tzIGJlZm9yZQpAQCAtMjg1MCw3ICsyODQ2LDE2IEBAIGluZGV4X3VwZGF0
ZV9zdGF0cyhSZWxhdGlvbiByZWwsCiAJewogCQlyZWxwYWdlcyA9IFJlbGF0aW9uR2V0TnVtYmVy
T2ZCbG9ja3MocmVsKTsKIAotCQlpZiAocmVsLT5yZF9yZWwtPnJlbGtpbmQgIT0gUkVMS0lORF9J
TkRFWCkKKwkJLyoKKwkJICogRG9uJ3QgdXBkYXRlIHN0YXRpc3RpY3Mgd2hlbiB0aGUgcmVsYXRp
b24gaXMgY29tcGxldGVseSBlbXB0eS4gVGhpcworCQkgKiBpcyBpbXBvcnRhbnQgZHVyaW5nIGJp
bmFyeSB1cGdyYWRlLCBiZWNhdXNlIGF0IHRoZSB0aW1lIHRoZSBzY2hlbWEKKwkJICogaXMgbG9h
ZGVkLCB0aGUgZGF0YSBoYXMgbm90IHlldCBiZWVuIG1vdmVkIGludG8gcGxhY2UuIEl0J3MgYWxz
bworCQkgKiB1c2VmdWwgd2hlbiByZXN0b3JpbmcgYSBkdW1wIGNvbnRhaW5pbmcgb25seSBzY2hl
bWEgYW5kIHN0YXRpc3RpY3MuCisJCSAqLworCQlpZiAocmVscGFnZXMgPT0gMCkKKwkJCXVwZGF0
ZV9zdGF0cyA9IGZhbHNlOworCisJCWlmICh1cGRhdGVfc3RhdHMgJiYgcmVsLT5yZF9yZWwtPnJl
bGtpbmQgIT0gUkVMS0lORF9JTkRFWCkKIAkJCXZpc2liaWxpdHltYXBfY291bnQocmVsLCAmcmVs
YWxsdmlzaWJsZSwgTlVMTCk7CiAJfQogCmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL2V4
cGVjdGVkL3N0YXRzX2ltcG9ydC5vdXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3N0YXRz
X2ltcG9ydC5vdXQKaW5kZXggMWYxNTBmN2IwOGQuLjRjODFmYjYwYzkxIDEwMDY0NAotLS0gYS9z
cmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3N0YXRzX2ltcG9ydC5vdXQKKysrIGIvc3JjL3Rlc3Qv
cmVncmVzcy9leHBlY3RlZC9zdGF0c19pbXBvcnQub3V0CkBAIC0xMiwxNCArMTIsMzQgQEAgQ1JF
QVRFIFRBQkxFIHN0YXRzX2ltcG9ydC50ZXN0KAogICAgIGFyYW5nZSBpbnQ0cmFuZ2UsCiAgICAg
dGFncyB0ZXh0W10KICkgV0lUSCAoYXV0b3ZhY3V1bV9lbmFibGVkID0gZmFsc2UpOworU0VMRUNU
CisgICAgcGdfY2F0YWxvZy5wZ19yZXN0b3JlX3JlbGF0aW9uX3N0YXRzKAorICAgICAgICAncmVs
YXRpb24nLCAnc3RhdHNfaW1wb3J0LnRlc3QnOjpyZWdjbGFzcywKKyAgICAgICAgJ3JlbHBhZ2Vz
JywgMTg6OmludGVnZXIsCisJJ3JlbHR1cGxlcycsIDIxOjpyZWFsLAorCSdyZWxhbGx2aXNpYmxl
JywgMjQ6OmludGVnZXIpOworIHBnX3Jlc3RvcmVfcmVsYXRpb25fc3RhdHMgCistLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0KKyB0CisoMSByb3cpCisKKy0tIGNyZWF0aW5nIGFuIGluZGV4IG9u
IGFuIGVtcHR5IHRhYmxlIHNob3VsZG4ndCBvdmVyd3JpdGUgc3RhdHMKIENSRUFURSBJTkRFWCB0
ZXN0X2kgT04gc3RhdHNfaW1wb3J0LnRlc3QoaWQpOworU0VMRUNUIHJlbHBhZ2VzLCByZWx0dXBs
ZXMsIHJlbGFsbHZpc2libGUKK0ZST00gcGdfY2xhc3MKK1dIRVJFIG9pZCA9ICdzdGF0c19pbXBv
cnQudGVzdCc6OnJlZ2NsYXNzOworIHJlbHBhZ2VzIHwgcmVsdHVwbGVzIHwgcmVsYWxsdmlzaWJs
ZSAKKy0tLS0tLS0tLS0rLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tCisgICAgICAgMTggfCAg
ICAgICAgMjEgfCAgICAgICAgICAgIDI0CisoMSByb3cpCisKIC0tIHN0YXJ0aW5nIHN0YXRzCiBT
RUxFQ1QgcmVscGFnZXMsIHJlbHR1cGxlcywgcmVsYWxsdmlzaWJsZQogRlJPTSBwZ19jbGFzcwog
V0hFUkUgb2lkID0gJ3N0YXRzX2ltcG9ydC50ZXN0Jzo6cmVnY2xhc3M7CiAgcmVscGFnZXMgfCBy
ZWx0dXBsZXMgfCByZWxhbGx2aXNpYmxlIAogLS0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0tLS0t
LS0tLS0tLS0KLSAgICAgICAgMCB8ICAgICAgICAtMSB8ICAgICAgICAgICAgIDAKKyAgICAgICAx
OCB8ICAgICAgICAyMSB8ICAgICAgICAgICAgMjQKICgxIHJvdykKIAogQkVHSU47CmRpZmYgLS1n
aXQgYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9zdGF0c19pbXBvcnQuc3FsIGIvc3JjL3Rlc3QvcmVn
cmVzcy9zcWwvc3RhdHNfaW1wb3J0LnNxbAppbmRleCA4YzE4M2JjZWI4YS4uYzhhYmI3MTUxMzAg
MTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3N0YXRzX2ltcG9ydC5zcWwKKysrIGIv
c3JjL3Rlc3QvcmVncmVzcy9zcWwvc3RhdHNfaW1wb3J0LnNxbApAQCAtMTUsOCArMTUsMjAgQEAg
Q1JFQVRFIFRBQkxFIHN0YXRzX2ltcG9ydC50ZXN0KAogICAgIHRhZ3MgdGV4dFtdCiApIFdJVEgg
KGF1dG92YWN1dW1fZW5hYmxlZCA9IGZhbHNlKTsKIAorU0VMRUNUCisgICAgcGdfY2F0YWxvZy5w
Z19yZXN0b3JlX3JlbGF0aW9uX3N0YXRzKAorICAgICAgICAncmVsYXRpb24nLCAnc3RhdHNfaW1w
b3J0LnRlc3QnOjpyZWdjbGFzcywKKyAgICAgICAgJ3JlbHBhZ2VzJywgMTg6OmludGVnZXIsCisJ
J3JlbHR1cGxlcycsIDIxOjpyZWFsLAorCSdyZWxhbGx2aXNpYmxlJywgMjQ6OmludGVnZXIpOwor
CistLSBjcmVhdGluZyBhbiBpbmRleCBvbiBhbiBlbXB0eSB0YWJsZSBzaG91bGRuJ3Qgb3Zlcndy
aXRlIHN0YXRzCiBDUkVBVEUgSU5ERVggdGVzdF9pIE9OIHN0YXRzX2ltcG9ydC50ZXN0KGlkKTsK
IAorU0VMRUNUIHJlbHBhZ2VzLCByZWx0dXBsZXMsIHJlbGFsbHZpc2libGUKK0ZST00gcGdfY2xh
c3MKK1dIRVJFIG9pZCA9ICdzdGF0c19pbXBvcnQudGVzdCc6OnJlZ2NsYXNzOworCiAtLSBzdGFy
dGluZyBzdGF0cwogU0VMRUNUIHJlbHBhZ2VzLCByZWx0dXBsZXMsIHJlbGFsbHZpc2libGUKIEZS
T00gcGdfY2xhc3MKLS0gCjIuMzQuMQoK


--=-Q0F+4LC/PGyd4BEWXhJ0--