Skip site navigation (1) Skip section navigation (2)

TODO-Item: B-tree fillfactor control

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)lab(dot)ntt(dot)co(dot)jp>
To: pgsql-patches(at)postgresql(dot)org
Subject: TODO-Item: B-tree fillfactor control
Date: 2006-02-06 04:27:21
Message-ID: 20060206120725.49B5.ITAGAKI.TAKAHIRO@lab.ntt.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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


Attachment: index_free_percent-0206.patch
Description: application/octet-stream (67.7 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Mark WoodwardDate: 2006-02-06 05:17:27
Subject: Re: Shared memory and memory context question
Previous:From: Mark WoodwardDate: 2006-02-06 03:37:24
Subject: Re: Shared memory and memory context question

pgsql-patches by date

Next:From: Pavel StehuleDate: 2006-02-06 09:58:43
Subject: slow information schema with thausand users, seq.scan pg_authid
Previous:From: Stephen FrostDate: 2006-02-05 21:11:49
Subject: Re: pg_restore COPY error handling

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group