Обсуждение: 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