Postgres Pro
Downloads
Home   >   mailing lists

Thread: Rebuild indexes

Rebuild indexes

From
Shankar K
Date:
Hi Everyone,

I've a kind of less inserts/mostly updates table,
which we vacuum every half-hour.

here is the output of vacuum analyze

INFO:  --Relation public.accounts--
INFO:  Index accounts_u1: Pages 1498; Tuples 515:
Deleted 179.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index accounts_u2: Pages 2227; Tuples 515:
Deleted 179.
    CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  Index accounts_u3: Pages 246; Tuples 515:
Deleted 179.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.

however its indexes keeps growing on and on. After
surfing the manuals for a while, i came to know that
vacuum doesn't clears up dead tuples caused by
updates. so i then decided to do reindex online, but
that makes exclusive lock on table which would prevent

writing on to tables.

finally i'm at a point where i decided to do index
swapping.

for e.g.

1. create index accounts_u1_swap,accounts_u2_swap and
accounts_u3_swap in addition to the original indexes

2. analyze table to update stats, so that the table
knows about new indexes.

3. drop original indexes

4. i wish i had a rename index command to rename _swap
to its original index name. now create indexes with
original name

5. follow #2  and #3 (now drop _swap indexes)

Is there a better way to do this. comments are
appreciated.

thanks
-Shankar

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

Re: Rebuild indexes

From
Josh Berkus
Date:
Shankar,

> Is there a better way to do this. comments are
> appreciated.

No.  This is one of the major features in 7.4; FSM and VACUUM will manage
indexes as well.   Until then, we all suffer ....

BTW, the REINDEX command is transaction-safe.   So if your database has "lull"
periods, you can run it without worrying that any updates will get turned
back.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Re: [ADMIN] Rebuild indexes

From
Tom Lane
Date:
Shankar K <shan0075@yahoo.com> writes:
> ... so i then decided to do reindex online, but
> that makes exclusive lock on table which would prevent
> writing on to tables.

So does CREATE INDEX, so it's not clear what you're buying with
all these pushups.

> 2. analyze table to update stats, so that the table
> knows about new indexes.

You do not need to ANALYZE to get the system to notice new indexes.

> 4. i wish i had a rename index command to rename _swap
> to its original index name.

You can rename indexes as if they were tables.

            regards, tom lane

Re: [ADMIN] Rebuild indexes

From
Shankar K
Date:
thanks tom. i wasn't sure about create index taking
exclusive locks on tables too. so i could as well
reindex than doing the whole _swap mess during
off-peak hrs.


--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Shankar K <shan0075@yahoo.com> writes:
> > ... so i then decided to do reindex online, but
> > that makes exclusive lock on table which would
> prevent
> > writing on to tables.
>
> So does CREATE INDEX, so it's not clear what you're
> buying with
> all these pushups.
>
> > 2. analyze table to update stats, so that the
> table
> > knows about new indexes.
>
> You do not need to ANALYZE to get the system to
> notice new indexes.
>
> > 4. i wish i had a rename index command to rename
> _swap
> > to its original index name.
>
> You can rename indexes as if they were tables.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
>       subscribe-nomail command to
> majordomo@postgresql.org so that your
>       message can get through to the mailing list
cleanly


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com