Thread: fillfactor using WITH syntax
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 TABLEtable (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 tableWITH (...) - 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
ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
> Now, I want to ask you how to modify WITH parameters for existing
> tables/indexes.
I'd go with the ALTER TABLE, rather than cluttering N other commands.
There's already precedent for delayed effects of parameter alterations
(SET, ALTER SET STATISTICS, ALTER SET STORAGE, etc). Documenting which
commands cause the new values to take effect seems sufficient.
regards, tom lane
On Tue, 2006-06-06 at 11:45 +0900, ITAGAKI Takahiro wrote: > 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 (...) Sounds good. This is important in other situations too, e.g. http://archives.postgresql.org/pgsql-hackers/2005-09/msg00851.php > 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. Are you implementing the array of parameters on pg_index as Tom suggested or pg_class.relfillfactor? Why not implement an array of option parameters on pg_class, so both heaps and indexes can be given additional parameters? That way you wouldn't need a specific relfillfactor attribute. That would allow us to keep CREATE TABLE free of additional keywords also. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs <simon@2ndquadrant.com> writes:
> Why not implement an array of option parameters on pg_class, so both
> heaps and indexes can be given additional parameters? That way you
> wouldn't need a specific relfillfactor attribute. That would allow us to
> keep CREATE TABLE free of additional keywords also.
None of this should go anywhere near pg_class. IIRC the solutions we
discussed involved adding some sort of array to pg_index. A solution
that only works for FILLFACTOR is missing the point, too.
regards, tom lane
On Tue, 2006-06-06 at 10:27 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > Why not implement an array of option parameters on pg_class, so both > > heaps and indexes can be given additional parameters? That way you > > wouldn't need a specific relfillfactor attribute. That would allow us to > > keep CREATE TABLE free of additional keywords also. > > None of this should go anywhere near pg_class. IIRC the solutions we > discussed involved adding some sort of array to pg_index. Itagaki had suggested adding options to heaps also, so clearly we'd need to add that to pg_class, rather than pg_index in that case. PCTFREE would be useful for heaps as well as indexes, but there could be other options also. Extending the thought for the general case, I see no reason why we would want to permanently exclude heaps from having a more flexible set of options when we aim to provide that for indexes. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs <simon@2ndquadrant.com> wrote: > Itagaki had suggested adding options to heaps also, so clearly we'd need > to add that to pg_class, rather than pg_index in that case. Yes, I want to add options tables not only indexes. There is pg_index for indexes, but is not pg_table for tables, so I added options to pg_class. > > > Why not implement an array of option parameters on pg_class, so both > > > heaps and indexes can be given additional parameters? That way you > > > wouldn't need a specific relfillfactor attribute. That would allow us to > > > keep CREATE TABLE free of additional keywords also. Ok, I'll add a options array to pg_class instead of the fixed-field for fillfactor, referring to the aclitem. --- ITAGAKI Takahiro NTT Open Source Software Center