Tried restoring to pg10 with maintenance_work_mem=1GB. No change in runtime. When tried same on pg9.5 it ran couple of minutes faster than previous run. Now I am going to turn off autovacuum and see if that makes any difference. On Tue, May 7, 2019 at 10:38 AM Siddharth Karandikar <siddharth.karandikar@xxxxxxxxx> wrote: > > Hi Andres, > > On Mon, May 6, 2019 at 8:09 PM Andres Freund <andres@xxxxxxxxxxx> 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
