Re: Query Performance after pg_restore

I'd run EXPLAIN <query> on each server.

On Sun, Dec 24, 2023 at 12:00 PM Murthy Nunna <mnunna(at)fnal(dot)gov> wrote:

> Thanks, Tom.
>
> I am wondering if there is way to obtain same performance as before. Like
> restoring performance statistics?!
> Simple analyze is not helping. And Vacuum is a overkill where there are no
> dead tuples.
>
> I am not setting any hint bits... I can verify this in new and old if you
> can provide a query or some method to verify this... I still have original
> copy of same database on a different server for comparison.
>
>
>
> -----Original Message-----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Sent: Sunday, December 24, 2023 10:12 AM
> To: Murthy Nunna <mnunna(at)fnal(dot)gov>
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: Query Performance after pg_restore
>
> Murthy Nunna <mnunna(at)fnal(dot)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
> took 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
>
>
>