Date: Wed, 6 Mar 2024 13:28:20 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
	Corey Huinker <corey.huinker@gmail.com>,
	Peter Smith <smithpb2250@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Tomas Vondra <tomas.vondra@enterprisedb.com>
Subject: Re: Statistics Import and Export
Message-ID: <Zei1xMGH8SJS9yoc@tamriel.snowman.net>
References: 
 <CADkLM=doQW3TE_LyBwG10NppKZLz0CjAgu10O79uMQc2frYCmA@mail.gmail.com>
 <CADkLM=cfZC6w07Yd_3uK=3xy9pgX+X_1xkEROPvxBHHTMab7-A@mail.gmail.com>
 <CADkLM=c7sMxeOGMmV6MtsjFkNnji6XLfj2bTgy04f4vWWhv6PA@mail.gmail.com>
 <ZeDnq/10RBRM/lB9@tamriel.snowman.net>
 <CADkLM=c8waqcMg+tidQmRudS8cziwPHoAQoLq4zKeEbskzVX2Q@mail.gmail.com>
 <CAOuzzgp4vbs5wOUFyE9Ts+kc7+BU-_5oPNs2dgQ5cHhJBUvmFg@mail.gmail.com>
 <CADkLM=dUGyQMK8Rhz1gVLeW8u2nBWOLvH7-j82MAAN_PmcSAQw@mail.gmail.com>
 <CAEze2WgdmG67-9wA2TeQwnV6QieYMVreXob84VT5GfuHOi77yg@mail.gmail.com>
 <CAOuzzgpw1ridazdKsedj=_srNGvyT2wSoHR9AT77BZ567BVtbg@mail.gmail.com>
 <CAEze2WidTCpSkPaL_R=ojmmAyYq_RHSG+dS27upbfF__5KGKZg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Bb1+azeZJV5hwNye"
Content-Disposition: inline
In-Reply-To: 
 <CAEze2WidTCpSkPaL_R=ojmmAyYq_RHSG+dS27upbfF__5KGKZg@mail.gmail.com>
User-Agent: Mutt/2.1.4 (2021-12-11)
Archived-At: 
 <https://www.postgresql.org/message-id/Zei1xMGH8SJS9yoc%40tamriel.snowman.net>
Precedence: bulk


--Bb1+azeZJV5hwNye
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Matthias van de Meent (boekewurm+postgres@gmail.com) wrote:
> On Wed, 6 Mar 2024 at 11:33, Stephen Frost <sfrost@snowman.net> wrote:
> > On Wed, Mar 6, 2024 at 11:07 Matthias van de Meent <boekewurm+postgres@=
gmail.com> wrote:
> >> Or even just one VALUES for the whole statistics loading?
> > I don=E2=80=99t think we=E2=80=99d want to go beyond one relation at a =
time as then it can be parallelized, we won=E2=80=99t be trying to lock a w=
hole bunch of objects at once, and any failures would only impact that one =
relation=E2=80=99s stats load.
>=20
> That also makes sense.

Great, thanks.

> >> I suspect the main issue with combining this into one statement
> >> (transaction) is that failure to load one column's statistics implies
> >> you'll have to redo all the other statistics (or fail to load the
> >> statistics at all), which may be problematic at the scale of thousands
> >> of relations with tens of columns each.
> >
> >
> > I=E2=80=99m pretty skeptical that =E2=80=9Cstats fail to load and lead =
to a failed transaction=E2=80=9D is a likely scenario that we have to spend=
 a lot of effort on.
>=20
> Agreed on the "don't have to spend a lot of time on it", but I'm not
> so sure on the "unlikely" part while the autovacuum deamon is
> involved, specifically for non-upgrade pg_restore. I imagine (haven't
> checked) that autoanalyze is disabled during pg_upgrade, but
> pg_restore doesn't do that, while it would have to be able to restore
> statistics of a table if it is included in the dump (and the version
> matches).

Even if autovacuum was running and it kicked off an auto-analyze of the
relation at just the time that we were trying to load the stats, there
would be appropriate locking happening to keep them from causing an
outright ERROR and transaction failure, or if not, that's a lack of
locking and should be fixed.  With the per-attribute-function-call
approach, that could lead to a situation where some stats are from the
auto-analyze and some are from the stats being loaded but I'm not sure
if that's a big concern or not.

For users of this, I would think we'd generally encourage them to
disable autovacuum on the tables they're loading as otherwise they'll
end up with the stats going back to whatever an auto-analyze ends up
finding.  That may be fine in some cases, but not in others.

A couple questions to think about though: Should pg_dump explicitly ask
autovacuum to ignore these tables while we're loading them?=20
Should these functions only perform a load when there aren't any
existing stats?  Should the latter be an argument to the functions to
allow the caller to decide?

> > What are the cases where we would be seeing stats reloads failing where=
 it would make sense to re-try on a subset of columns, or just generally, i=
f we know that the pg_dump version matches the target server version?
>=20
> Last time I checked, pg_restore's default is to load data on a
> row-by-row basis without --single-transaction or --exit-on-error. Of
> course, pg_upgrade uses it's own set of flags, but if a user is
> restoring stats with  pg_restore, I suspect they'd rather have some
> column's stats loaded than no stats at all; so I would assume this
> requires one separate pg_import_pg_statistic()-transaction for every
> column.

Having some discussion around that would be useful.  Is it better to
have a situation where there are stats for some columns but no stats for
other columns?  There would be a good chance that this would lead to a
set of queries that were properly planned out and a set which end up
with unexpected and likely poor query plans due to lack of stats.
Arguably that's better overall, but either way an ANALYZE needs to be
done to address the lack of stats for those columns and then that
ANALYZE is going to blow away whatever stats got loaded previously
anyway and all we did with a partial stats load was maybe have a subset
of queries have better plans in the interim, after having expended the
cost to try and individually load the stats and dealing with the case of
some of them succeeding and some failing.

Overall, I'd suggest we wait to see what Corey comes up with in terms of
doing the stats load for all attributes in a single function call,
perhaps using the VALUES construct as you suggested up-thread, and then
we can contemplate if that's clean enough to work or if it's so grotty
that the better plan would be to do per-attribute function calls.  If it
ends up being the latter, then we can revisit this discussion and try to
answer some of the questions raised above.

Thanks!

Stephen

--Bb1+azeZJV5hwNye
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmXotcEACgkQ7WyKOINH
ZFVXXA/9GtXXSXls3d0y7JoLPsklRLPLeXKOMs5ejyRVm0zvSFHF6D/M8JDWdODN
WugL8kVCGQPSvCPDLjsSeRft4/RBHCU8HQLBf7mMtiMjcbhZTqvQnqk9NHHlY7wU
fgCjsrmsx+cjf42dLFcqFtwoFtRX1UoExGZj3Zj3PtQKxpOKCXc9V9+w5ehNlNfb
HOs0cbZFb31UE8did/stRWeuWY+MDu1WKOYbQAqvf8IuxkwDrzNhsR4/lBj2Or4Z
3RgeAnINWqsGtHxPhB7lmtWBdrWmXoN5olHeyM5Dje3J+h2X3bAmMCmdKTWIpvBi
gApiA8yD9nvgos16dwRZAqaiOXF5ov7BsmFasm344f6YyO3ZoI350cCGVf3qN4aP
skQTg/j2BddtXe/1rhXFXddY7kmkUKdVncvwzm0L3vYpU1NSvsPpDL0H7agoL0sK
TgBW3H5FXBfjzFLStlsgUjaCpoI3bhC8hL9tkhTt6FqqziDn89lbZGrIPAdjcsH3
OHl7XdBcFcl54WJw0S9wiRXY4kvbDB411gC2gDZsDUPpjLolIxlfBYmHB7k3l9zt
Smph3xqFqWzvDSPcrKIB2sbKZojHA7Ly+ObSMhPPtQE/s4MxCt3DWz8QFjyLulg4
cZnM5GKaQJwyInXAhZKsuVVFF4Yj+BUCaw301t2PreIWzxiDpHs=
=h3WL
-----END PGP SIGNATURE-----

--Bb1+azeZJV5hwNye--