>> Yes, I use the same approach, but I'm not aware of any such guideline
>> related to fillfactor with indexes. Anyway those guidelines need to be
>> written by someone, so you have a great opportunity ;-)
>
>
> I did a quick test using your example. As in your test, "increasing"
> values don't get any gain from a different fillfactor.
> I tried a random index:
>
> create table test_fill (id int);
> create index test_fill_idx on test_fill(id) with (fillfactor=100);
> insert into test_fill select (random()*100000)::integer from
> generate_series(1,10000000) i;
>
>
> time: 373936.724
>
> drop table test_fill;
> create table test_fill (id int);
> create index test_fill_idx on test_fill(id) with (fillfactor=50);
>
> insert into test_fill select (random()*100000)::integer from
> generate_series(1,10000000) i;
> time: 393209.911
>
>
> not much difference...
>
> Now I'm getting confused... is which cases fillfactor makes a
> difference???
What about the index size? How much space do they occupy? Analyze the
table and do this
SELECT relpages FROM pg_class WHERE relname = 'indexname';
and I believe you'll see the difference - the former index
(fillfactor=100) should grow much larger that the latter one.
The minimal performance difference is probably caused by the fact that
we're dealing with int4 column (and you've used just 100000 rows, i.e.
about 0.5MB of data) so the index is going to be tiny anyway.
Let's try to do that with varchar(32) column, just do something like this
db=# create table test_fill (id varchar(32));
db=# create index test_fill_idx on test_fill(id) with (fillfactor=100);
db=# insert into test_fill select md5(round(random()*100000)::text) from
generate_series(1,10000000) i;
I believe this might make a difference ...
regards
Tomas