From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Scott Carey <scott(at)richrelevance(dot)com> |
Cc: | postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: ALTER Table and CLUSTER does adding a new column rewrite clustered? (8.4.3) |
Date: | 2010-06-24 15:03:42 |
Message-ID: | 201006241503.o5OF3go14004@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Attachment | Content-Type | Size |
---|---|---|
/rtmp/diff | text/x-diff | 1.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2010-06-24 15:27:58 | Re: cpu bound postgresql setup. |
Previous Message | Rajesh Kumar Mallah | 2010-06-24 14:56:02 | Re: cpu bound postgresql setup. |