Re: Restoring from PostgreSQL 9.5 dump to 10 is super slow

Hi Andres,

On Mon, May 6, 2019 at 8:09 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
> On 2019-05-06 16:54:08 +0530, Siddharth Karandikar wrote:
> > I am trying to restore database dump created on PostgreSQL 9.5.16 to
> > PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring
> > the same dump to 9.5 doesn't take that much. So I am wondering what
> > could be reason behind this slowness.
>
> Is there any chance the configuration is different between 9.5 and 10?
I double checked, but there is no difference in configurations of 9.5
and 10. 9.5 restore works fine
with maintenance_work_mem set to 16MB.

But I will rerun restore on 10 with higher maintenance_work_mem.

> If there e.g. is an index on the table, the maintanance_work_mem setting
> would make a large differerence when rebuilding. Note that the
> medium-tablecase is noticably faster in 10 and that there's been some
> speedup work around that in 10.
>
> >
> > Postgres configuration that I have on this setup:
> > shared_buffers = 128MB
> >
> > work_mem = 1MB
> > maintenance_work_mem = 16MB
>
> maintenance_work_mem = 16Mb is a very low value - it's e.g. used for
> index builds, to sort the data.
>
> Greetings,
>
> Andres Freund