From: Bruce Momjian <bruce@momjian.us>
To: Scott Carey <scott@richrelevance.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: ALTER Table and CLUSTER does adding a new column rewrite clustered? (8.4.3)
Date: Thu, 24 Jun 2010 11:03:42 -0400 (EDT)
Message-ID: <201006241503.o5OF3go14004@momjian.us> (raw)
In-Reply-To: <37F2FA33-49B6-478D-BCFE-FD0DBC4CBBA8@richrelevance.com>
Scott Carey wrote:
> v. 8.4.3
>
> I have a table that has several indexes, one of which the table is
> clustered on. If I do an ALTER TABLE Foo ADD COLUMN bar integer not
> null default -1;
>
> It re-writes the whole table.
All good questions:
> * Does it adhere to the CLUSTER property of the table and write the new
> version clustered?
The new table is the exact same heap ordering as the old table; it does
not refresh the clustering if the table has become unclustered.
> * Does it properly write it with the FILLFACTOR setting?
Yes, inserts are used to populate the new table, and inserts honor
FILLFACTOR.
> * Are all the indexes re-created too, or are they bloated and need a REINDEX?
They are recreated.
> http://www.postgresql.org/docs/8.4/static/sql-altertable.html
> does not seem to answer the above, it mentions the conditions that
> cause a rewrite but does not say what the state is after the rewrite
> with respect to CLUSTER, FILLFACTOR, and index bloat.
I have added a documentation patch to mention the indexes are rebuilt;
applied patch attached.
The gory details can be found in src/backend/commands/tablecmds.c.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Attachments:/rtmp/diff (text/x-diff, 1.2K) download
Index: doc/src/sgml/ref/alter_table.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.114
diff -c -c -r1.114 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml 9 Jun 2010 17:48:10 -0000 1.114
--- doc/src/sgml/ref/alter_table.sgml 24 Jun 2010 14:54:00 -0000
***************
*** 689,696 ****
<para>
Adding a column with a non-null default or changing the type of an
! existing column will require the entire table to be rewritten. This
! might take a significant amount of time for a large table; and it will
temporarily require double the disk space. Adding or removing a system
<literal>oid</> column likewise requires rewriting the entire table.
</para>
--- 689,696 ----
<para>
Adding a column with a non-null default or changing the type of an
! existing column will require the entire table and indexes to be rewritten.
! This might take a significant amount of time for a large table; and it will
temporarily require double the disk space. Adding or removing a system
<literal>oid</> column likewise requires rewriting the entire table.
</para>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruce@momjian.us, scott@richrelevance.com
Subject: Re: ALTER Table and CLUSTER does adding a new column rewrite clustered? (8.4.3)
In-Reply-To: <201006241503.o5OF3go14004@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox