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