On Mon, Oct 17, 2011 at 2:15 AM, Szymon Guz <
mabewlun@gmail.com> wrote:
>
>
> On 17 October 2011 02:01, Scott Marlowe <
scott.marlowe@gmail.com> wrote:
>>
>> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz <
mabewlun@gmail.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'