TODO-Item: B-tree fillfactor control

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)lab(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: TODO-Item: B-tree fillfactor control
Date: 2006-01-19 02:00:00
Message-ID: 20060119095341.4DC7.ITAGAKI.TAKAHIRO@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Hi Hackers,

I'm trying the following TODO item:
[Indexes]
- Add fillfactor to control reserved free space during index creation

I have already made an patch and it seemed to work well.

----
$ ./pgbench -i -s 10
# select relpages from pg_class where relname = 'accounts_pkey';
relpages | 2745 ( default PCTFree is 10% )

# set btree_leaf_free_percent = 0;
# reindex index accounts_pkey;
# select relpages from pg_class where relname = 'accounts_pkey';
relpages | 2475 ( <- about 2745 * 0.9 = 2470.5 )

# set btree_leaf_free_percent = 30;
# reindex index accounts_pkey;
# select relpages from pg_class where relname = 'accounts_pkey';
relpages | 3537 ( <- about 2745 * 0.9 / 0.7 = 3529.3 )
----

And now, I need advice on some issues.

- Is it appropriate to use GUC variables to control fillfactors?
Is it better to extend CREATE INDEX / REINDEX grammar?
- Should indexes remember their fillfactors when they are created?
The last fillfactors will be used on next reindex.
- Is fillfactor useful for hash and gist indexes?
I think hash does not need it, but gist might need it.

Look forward to your comments.
Thanks,

---
ITAGAKI Takahiro
NTT Cyber Space Laboratories

Attachment Content-Type Size
btree_free_percent.patch application/octet-stream 2.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Rod Taylor 2006-01-19 02:02:28 Re: No heap lookups on index
Previous Message Dann Corbit 2006-01-19 01:51:47 Re: Surrogate keys (Was: enums)

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2006-01-19 03:03:33 Re: pgxs/windows
Previous Message Andrew Dunstan 2006-01-19 00:17:06 Re: pgxs/windows