From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
cc: Corey Huinker <corey.huinker@gmail.com>,
        Robert Haas <robertmhaas@gmail.com>,
        Nathan Bossart <nathandbossart@gmail.com>,
        Jeff Davis <pgsql@j-davis.com>, Michael Paquier <michael@paquier.xyz>,
        jian he <jian.universality@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>,
        Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
        Peter Smith <smithpb2250@gmail.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
        alvherre@alvh.no-ip.org
Subject: Re: Statistics Import and Export
In-reply-to: <urk5wcvgvnqzmvdvbe6b2gthhf3pphfqmbwculciy3lm4kvnay@avvgvhxo544p>
References: <rp6int5sqs5bn7g35brqz7w3t7il6tn2dgpm7tr3pev626wnc4@fi7a3enofhct> <Z8j8HXlIIGYPA9L8@nathan> <CADkLM=e-H5H+pY8iA58ie28PVSRJ1HBpzyy1=FWmy0Hm+s4boQ@mail.gmail.com> <6rpmhyrtci4epuzay7y5xvd2cwdwb6zmtt6ofxrvakyawxhm7s@6grajbpr4kij> <CADkLM=ct+i2G=Z+c_8np1+C75uvzjUK_SBsouUvgWMHRs2cnyg@mail.gmail.com> <xe3yjkcthy7f377zjayuckxyd62z5cekgjau3j4vp6elbicson@cx2uiilwmxyy> <CADkLM=f1n2_Vomq0gKab7xdxDHmJGgn=DE48P8fzQOp3Mrs1Qg@mail.gmail.com> <pbjbvxnq36xo2ooufcnlfqsqnxuvggbqxbx3myjtswokshfjbl@hfihx57z4j4t> <CA+TgmoYGuEC=E4TbomRyqO8+Uav=90G0dYXw=rA1d5UmRB76Yg@mail.gmail.com> <CADkLM=dTTf9tonKsQnDCDp5oyODE2mFV7K6nFuCp84QE3GBWuQ@mail.gmail.com> <urk5wcvgvnqzmvdvbe6b2gthhf3pphfqmbwculciy3lm4kvnay@avvgvhxo544p>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Thu, 06 Mar 2025 13:04:55 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <714294.1741286854.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 06 Mar 2025 13:47:34 -0500
Message-ID: <714295.1741286854@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/714295.1741286854%40sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
>    And in contrast to analyzing the database in parallel, the pg_dump/re=
store
>    work to restore stats afaict happens single-threaded for each databas=
e.

In principle we should be able to do stats dump/restore parallelized
just as we do for data.  There are some stumbling blocks in the way
of that:

1. pg_upgrade has made a policy judgement to apply parallelism across
databases not within a database, ie it will launch concurrent dump/
restore tasks in different DBs but not authorize any one of them to
eat multiple CPUs.  That needs to be re-thought probably, as I think
that decision dates to before we had useful parallelism in pg_dump and
pg_restore.  I wonder if we could just rip out pg_upgrade's support
for DB-level parallelism, which is not terribly pretty anyway, and
simply pass the -j switch straight to pg_dump and pg_restore.

2. pg_restore should already be able to perform stats restores in
parallel (if authorized to use multiple threads), but I'm less clear
on whether that works right now for pg_dump.

3. Also, parallel restore depends critically on the TOC entries'
dependencies being sane, and right now I do not think they are.
I looked at "pg_restore -l -v" output for the regression DB, and it
seems like it's not taking care to ensure that table/MV data is loaded
before the table/MV's stats.  (Maybe that accounts for some of the
complaints we've seen about stats getting mangled??)

> I think the stats need to be handled much more like we handle the actual=
 table
> data, which are obviously *not* stored in memory for the whole run of pg=
_dump.

+1

			regards, tom lane