Postgres Pro
Downloads
Home   >   mailing lists

Thread: ALTER INDEX set fillfactor

ALTER INDEX set fillfactor

From
Simon Riggs
Date:
pgadmin sneaks a REINDEX into the SQL when you specify a change to the
fillfactor of an index.

That's not very handy because the Postgres manual says specifically that
ALTER INDEX doesn't issue a REINDEX. It's a perfectly valid thing to run
on its own, since it will affect the future growth of the index.

Issuing the REINDEX can lock the table for hours and shouldn't be issued
quietly on production systems. There should be a REINDEX immediate
option, or a reminder prompt.

It would be even better if there was an option to do that CONCURRENTLY,
i.e. add the new index and then drop the old one afterwards. (It's
unfortunate that there isn't a REINDEX concurrently, but there isn't
yet).

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Re: ALTER INDEX set fillfactor

From
Dave Page
Date:
Simon Riggs wrote:
> pgadmin sneaks a REINDEX into the SQL when you specify a change to the
> fillfactor of an index.
>
> That's not very handy because the Postgres manual says specifically that
> ALTER INDEX doesn't issue a REINDEX. It's a perfectly valid thing to run
> on its own, since it will affect the future growth of the index.

Thanks, fixed in SVN.

> Issuing the REINDEX can lock the table for hours and shouldn't be issued
> quietly on production systems. There should be a REINDEX immediate
> option, or a reminder prompt.

Yeah - not this close to a release though.

> It would be even better if there was an option to do that CONCURRENTLY,
> i.e. add the new index and then drop the old one afterwards. (It's
> unfortunate that there isn't a REINDEX concurrently, but there isn't
> yet).

Funny - I suggested that to Greg just the other day...

/D

Re: ALTER INDEX set fillfactor

From
Simon Riggs
Date:
On Fri, 2007-10-05 at 21:24 +0100, Dave Page wrote:
> Simon Riggs wrote:

> > It would be even better if there was an option to do that CONCURRENTLY,
> > i.e. add the new index and then drop the old one afterwards. (It's
> > unfortunate that there isn't a REINDEX concurrently, but there isn't
> > yet).
>
> Funny - I suggested that to Greg just the other day...

Sorry, I meant in the absence of REINDEX concurrently, we can issue:

CREATE INDEX CONCURRENTLY newindex
DROP INDEX oldindex

on each index on the table in turn, which does the same thing.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com