>> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
