Re: FILLFACTOR and increasing index

Dne 9.5.2011 17:25, Leonardo Francalanci napsal(a):
> I know that theory is one thing and real testing another; but I can't
> test everything; if there are some (proved?) guidelines I'd like to
> use them (example: I'm not going to test that fillfactor in table creation
> in my case won't make any difference in performance; I trust the
> docs and the fact that "it makes sense").
>

Anyway testing this (with the 'insert only' workload) may be quite simple:

================= fillfactor = 100 ====================

testdb=# create table test_fill (id int);
CREATE TABLE
Time: 2,515 ms

testdb=# create index test_fill_idx on test_fill(id) with (fillfactor=100);
CREATE INDEX
Time: 10,331 ms

testdb=# insert into test_fill select i from generate_series(1,1000000)
s(i);
INSERT 0 1000000
Time: 11542,512 ms

testdb=# select relpages from pg_class where relname = 'test_fill_idx';
relpages
----------
1977
(1 row)

==================== fillfactor = 70 ========================

testdb=# create table test_fill (id int);
CREATE TABLE
Time: 1,382 ms

testdb=# create index test_fill_idx on test_fill(id) with (fillfactor=70);
CREATE INDEX
Time: 10,296 ms

testdb=# insert into test_fill select i from generate_series(1,1000000)
s(i);
INSERT 0 1000000
Time: 11117,398 ms

testdb=# select relpages from pg_class where relname = 'test_fill_idx';
relpages
----------
2819
(1 row)

============================================================

So there seems to be no difference in insert performance (the INSERT
takes about 11s in both cases), but the size of the index with
fillfactor=70 needs much more space.

So with the insert only (in ascending order) workload, I'd go with
fillfactor=100 (or you may leave it at 90, which is the default value,
the difference will be negligible).

regards
Tomas