fillfactor using WITH syntax

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: fillfactor using WITH syntax
Date: 2006-06-06 02:45:14
Message-ID: 20060606093753.5391.ITAGAKI.TAKAHIRO@oss.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Hi Hackers,

I'm rewriting fillfactor patch, per the following discussion,
http://archives.postgresql.org/pgsql-hackers/2006-03/msg00287.php
Now fillfactor can be set using WITH syntax:
- 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 (...)
The settings are stored on pg_class.relfillfactor and the last value will
be used on next REINDEX. WITH parameter is a list of DefElems, so we can
use it to pass additional parameters to index access methods.

I also added same extention to table creation:
- CREATE TABLE table (columns) WITH (...)
- CREATE TABLE table WITH (...) AS SELECT/EXECUTE ...
Fill factor for tables works on INSERT, COPY, VACUUM FULL, CLUSTER, and
UPDATE to another page (not be used when rows are updated in the same page).
It is not so useful currently however, but if we optimize updating in same
page, the freespace controlling will do some good.
(The optimization is discussed in [HACKERS] Faster Updates,
http://archives.postgresql.org/pgsql-hackers/2006-06/msg00116.php)

Now, I want to ask you how to modify WITH parameters for existing
tables/indexes. One idea is extending re-organization commands:
- REINDEX INDEX index WITH (...)
- CLUSTER index ON table WITH (...)
- VACUUM FULL WITH (...)
Another is to use ALTER. but it may be unclear that the change will
be applied immediately or delayed until next re-organization.
- ALTER TABLE/INDEX name SET (...)

I appreciate any comments.

---
ITAGAKI Takahiro
NTT OSS Center

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-06-06 03:02:33 That EXPLAIN ANALYZE patch still needs work
Previous Message Oleg Bartunov 2006-06-05 19:50:53 Re: Connection Broken with Custom Dicts for TSearch2

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2006-06-06 03:08:37 Re: fillfactor using WITH syntax
Previous Message Qingqing Zhou 2006-06-06 02:35:12 remove lock protection on HeapTupleSatisfiesVacuum