Bruce Momjian wrote:
> > > Quite a few people have reported finding the opposite in practice.
> > > You should probably try vacuuming after deleting or updating some
> > > fraction of the rows, rather than just the all or none cases.
> > >
> >
> > Vacuum after delelting all rows isn't a worst case.
> > There's no moving in that case and vacuum doesn't need to call
> > index_insert() corresponding to the moving of heap tuples.
> >
> > Vacuum after deleting half of rows may be one of the worst case.
> > In this case,index_delete() is called as many times as 'delete all'
> > case and expensive index_insert() is called for moved_in tuples.
>
> I will test that.
>
I tried my test case in less scale than Bruce.
CREATE TABLE t (id int4, dt int4);
for (i=0; i < 2500000; i++) insert into t values ( i, (i * 1009) % 2500000);
delete from t where id < 1250000;
1) vacuum after create index on t(id) 405sec
2) vacuum after create index on t(dt) > 3600sec I gave up to continue execution.
3) vacuum and create index on t(id) and t(dt) 90sec + 114sec + 143sec = 347sec.
Seems random index insert is painful for vacuum.
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp