Обсуждение: pg_dump -Are statistics saved?
Hello,
We are running pg version 14.4
I did a pg_dump of a fairly large database (~20TB). I plan to pg_restore it on same server after upgrading Linux from Scientific Linux release 7.9 to Alma 9. We are forced to do pg_dump/pg_restore due to glibc collation changes in Alma 9.
The pg_dump (used verbose) log said “pg_dump: reading extended statistics”.
Does it mean it saved statistics and they can be restored with pg_restore? If not, is it necessary to run ANALYZE after pg_restore?
Thanks in advance for responding.
On Mon, Nov 27, 2023 at 5:28 PM Murthy Nunna <mnunna@fnal.gov> wrote:
Does it mean it saved statistics and they can be restored with pg_restore? If not, is it necessary to run ANALYZE after pg_restore?
Logical dumps do not output system catalogs except large objects. It outputs object definitions In SQL form. Though I'm unsure of that specific message.
David J.
Murthy Nunna <mnunna@fnal.gov> writes:
> The pg_dump (used verbose) log said "pg_dump: reading extended statistics".
> Does it mean it saved statistics and they can be restored with pg_restore?
What that means is that it will output clones of any CREATE STATISTICS
commands you may have issued.
> If not, is it necessary to run ANALYZE after pg_restore?
That has always been necessary after a dump/restore, and still is.
The pg_dump man page makes this clear enough, I should think:
The dump file produced by pg_dump does not contain the statistics used
by the optimizer to make query planning decisions. Therefore, it is
wise to run ANALYZE after restoring from a dump file to ensure optimal
performance; see Section 25.1.3 and Section 25.1.6 for more
information.
regards, tom lane
On Mon, 2023-11-27 at 19:45 -0500, Tom Lane wrote: > Murthy Nunna <mnunna@fnal.gov> writes: > > > > If not, is it necessary to run ANALYZE after pg_restore? > > That has always been necessary after a dump/restore, and still is. > The pg_dump man page makes this clear enough, I should think: > > The dump file produced by pg_dump does not contain the statistics used > by the optimizer to make query planning decisions. Therefore, it is > wise to run ANALYZE after restoring from a dump file to ensure optimal > performance; see Section 25.1.3 and Section 25.1.6 for more > information. If you are not pressed for time, you can wait until autovacuum gets around to calculating the statistics automatically. Yours, Laurenz Albe