Re: [HACKERS] Re: vacuum timings

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(at)tpf(dot)co(dot)jp