Re: vacuum timings

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I loaded 10,000,000 rows into CREATE TABLE test (x INTEGER); Table is
> > 400MB and index is 160MB.
>
> > With index on the single in4 column, I got:
> > 78 seconds for a vacuum
> > 121 seconds for vacuum after deleting a single row
> > 662 seconds for vacuum after deleting the entire table
>
> > With no index, I got:
> > 43 seconds for a vacuum
> > 43 seconds for vacuum after deleting a single row
> > 43 seconds for vacuum after deleting the entire table
>
> > I find this quite interesting.
>
> How long does it take to create the index on your setup --- ie,
> if vacuum did a drop/create index, would it be competitive?

OK, new timings with -F enabled:

index no index
519 same load
247 " first vacuum
40 " other vacuums

1222 X index creation
90 X first vacuum
80 X other vacuums

<1 90 delete one row
121 38 vacuum after delete 1 row

346 344 delete all rows
440 44 first vacuum
20 <1 other vacuums(index is still same size)

Conclusions:

o indexes never get smaller
o drop/recreate index is slower than vacuum of indexes

What other conclusions can be made?

--
Bruce Momjian | http://www.op.net/~candle
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026