Re: index bloat question

On 17 October 2011 02:01, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:
> > Hi,
> > just a couple of questions:
> > will there be an index bloat if I have:
> > - a serial column and only add rows to the table?
> > - a text column and I only add rows to the table?
> > For the serial column the numbers are only incremented, for the text
> column
> > I add random strings.
>
> With no deletes or updates, the only bloat will be from a non 100% fill
> factor.
>

Hi Scott,

if there is no bloat, how could you explain this:

Simple test:

CREATE TABLE test (
id text primary key,
category_id text not null
);

CREATE INDEX i_category ON test (category_id);

I make 500k inserts in one transaction using a python script.
For the random text I use random uuid from the function:

uuid.uuid4()

After those inserts I create another index:

CREATE INDEX i_new ON test (category_id);

select
pg_size_pretty(pg_relation_size('i_category')),
pg_size_pretty(pg_relation_size('i_new'))
;

Results:

'37 MB';'28 MB'

regards
Szymon