This is a draft patch for index fillfactor control discussed in
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00013.php
I added the following features:
- Add support for btree, hash and gist.
- Syntax extension using PCTFREE.
- Save settings to catalog. Next REINDEX will use the last value.
I'd like to ask index developers to review the patch, especially
the method to control fill factor for hash and gist.
I'll write documentations if there is no problem in the features.
Comments are welcome.
[Syntax extension]
- CREATE INDEX index ON table (columns) [ PCTFREE percent ];
- REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ PCTFREE percent ];
- ALTER INDEX index SET PCTFREE percent;
- PRIMARY KEY, UNIQUE constraint
CREATE TABLE / ALTER TABLE table ADD PRIMARY KEY [ PCTFREE percent ]
- with GUC
SET btree_free_percent = 30;
CREATE INDEX index ON table (...);
SET btree_free_percent = 10; -- revert
[Test and Result]
# CREATE table test1 (i int);
# INSERT INTO test1 SELECT generate_series(1, 100000);
# CREATE table test2 (c circle);
# INSERT INTO test2
# SELECT circle(point(100 * random(), 100 * random()), random())
# from generate_series(1, 100000);
# CREATE INDEX idx1_btree_0 ON test1 USING btree (i) PCTFREE 0;
# CREATE INDEX idx1_btree_10 ON test1 USING btree (i) PCTFREE 10;
# CREATE INDEX idx1_btree_30 ON test1 USING btree (i) PCTFREE 30;
# CREATE INDEX idx1_hash_0 ON test1 USING hash (i) PCTFREE 0;
# CREATE INDEX idx1_hash_25 ON test1 USING hash (i) PCTFREE 25;
# CREATE INDEX idx1_hash_40 ON test1 USING hash (i) PCTFREE 40;
# CREATE INDEX idx2_gist_0 ON test2 USING gist (c) PCTFREE 0;
# CREATE INDEX idx2_gist_10 ON test2 USING gist (c) PCTFREE 10;
# CREATE INDEX idx2_gist_30 ON test2 USING gist (c) PCTFREE 30;
# SELECT relname, relpages from pg_class where relname LIKE 'idx%' ORDER BY relname;
relname | relpages
---------------+----------
idx1_btree_0 | 249
idx1_btree_10 | 276 -- 249 / 0.9 = 277
idx1_btree_30 | 357 -- 249 / 0.7 = 356
idx1_hash_0 | 375
idx1_hash_25 | 413 -- Hash is not linear against fill factors.
idx1_hash_40 | 453 --
idx2_gist_0 | 882
idx2_gist_10 | 977 -- 882 / 0.9 = 980
idx2_gist_30 | 1273 -- 882 / 0.7 = 1260
(9 rows)
---
ITAGAKI Takahiro
NTT Cyber Space Laboratories
