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

table/index fillfactor control

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-patches(at)postgresql(dot)org
Subject: table/index fillfactor control
Date: 2006-06-06 09:02:19
Message-ID: 20060606163803.539A.ITAGAKI.TAKAHIRO@oss.ntt.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
This is a patch for table/index fillfactor control discussed in
    http://archives.postgresql.org/pgsql-hackers/2006-06/msg00175.php
Fillfactor works on CREATE, INSERT, UPDATE, COPY, VACUUM FULL, CLUSTER
and REINDEX, but is not done on dump/restore and GIN access method;
I'd like to ask those module developers to complete the patch, please.

This patch might help the TODO-item:
    Allow CREATE INDEX to take an additional parameter for use with
    special index types
but the patch rejects parameters except fillfactor currently.
If we want to implement the feature, it is needed to consider how to
store generic parameters passed at CREATE.



The following syntax are added:
 - Table creation:
    - CREATE TABLE table (columns) WITH (...)
    - CREATE TABLE table WITH (...) AS SELECT/EXECUTE ...
 - Index creation:
    - CREATE INDEX index ON table USING btree (columns) WITH (...)
    - CREATE TABLE table (i integer PRIMARY KEY WITH (...))
    - ALTER TABLE table ADD PRIMARY KEY (columns) WITH (...)
 - Alterating parameters for existing tables/indexes:
    - ALTER TABLE/INDEX name SET (...)

The folloing is a test of the patch:

# CREATE TABLE tbl (i int) WITH (fillfactor=50);
# CREATE INDEX idx ON tbl USING btree (i) WITH (fillfactor=50);
# INSERT INTO tbl SELECT generate_series(1, 100000);

| relname | relfillfactor | relpages |
+---------+---------------+----------+
| tbl     |            50 |     1087 |
| idx     |            50 |      494 |

# ALTER TABLE tbl SET (fillfactor = 100);
# ALTER INDEX idx SET (fillfactor = 100);
# CLUSTER idx ON tbl;
# REINDEX INDEX idx;

| relname | relfillfactor | relpages |
+---------+---------------+----------+
| tbl     |           100 |      541 |
| idx     |           100 |      249 |

---
ITAGAKI Takahiro
NTT OSS Center


Attachment: fillfactor-0606.patch.gz
Description: application/octet-stream (19.2 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Travis CrossDate: 2006-06-06 09:45:26
Subject: Duplicate rows sneaking in despite PRIMARY KEY / UNIQUE constraint
Previous:From: Peter EisentrautDate: 2006-06-06 07:53:27
Subject: Re: Why do we want to %Remove behavior of postmaster -o

pgsql-patches by date

Next:From: ITAGAKI TakahiroDate: 2006-06-06 09:51:34
Subject: 'Index Full Scan' for Index Scan without Index Cond
Previous:From: Victor B. WagnerDate: 2006-06-06 07:51:35
Subject: Contrib module to examine client certificate

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