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
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 |
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 |