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

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?
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