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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
