Re: index bloat question

[Szymon Guz <mabewlun@xxxxxxxxx>]

On 17 October 2011 02:01, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:

On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz <mabewlun@xxxxxxxxx> 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