From: Tom Lane <tgl@sss.pgh.pa.us>
To: Murthy Nunna <mnunna@fnal.gov>
cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Query Performance after pg_restore
In-reply-to: 
 <DM8PR09MB66777254DC04045815B09196B89AA@DM8PR09MB6677.namprd09.prod.outlook.com>
References: 
 <DM8PR09MB66777254DC04045815B09196B89AA@DM8PR09MB6677.namprd09.prod.outlook.com>
Comments: In-reply-to Murthy Nunna <mnunna@fnal.gov>
	message dated "Sun, 24 Dec 2023 15:53:39 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1615173.1703434335.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 24 Dec 2023 11:12:15 -0500
Message-ID: <1615174.1703434335@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1615174.1703434335%40sss.pgh.pa.us>
Precedence: bulk

Murthy Nunna <mnunna@fnal.gov> writes:
> I did pg_dump of a ~20TB database followed by pg_restore. I find simple =
queries like select count(*) running slow. I did a select count(*) on all =
tables before pg_dump which took ~4 hours. After pg_restore, same thing to=
ok 32 hours.

My bet is that that was setting commit hint bits, and hence incurring
a lot of writes.  If the data is reasonably stable that's a one-time
expense.

			regards, tom lane