pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Rebuild indexes
4+ messages / 3 participants
[nested] [flat]

* Rebuild indexes
@ 2003-07-28 21:29  Shankar K <shan0075@yahoo.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Shankar K @ 2003-07-28 21:29 UTC (permalink / raw)
  To: pgsql-performance; +Cc: pgsql-admin@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [PERFORM] Rebuild indexes
@ 2003-07-29 00:18  Josh Berkus <josh@agliodbs.com>
  parent: Shankar K <shan0075@yahoo.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2003-07-29 00:18 UTC (permalink / raw)
  To: Shankar K <shan0075@yahoo.com>; pgsql-performance; +Cc: pgsql-admin@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Rebuild indexes
@ 2003-07-29 02:21  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Shankar K <shan0075@yahoo.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2003-07-29 02:21 UTC (permalink / raw)
  To: Shankar K <shan0075@yahoo.com>; +Cc: pgsql-performance; pgsql-admin@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [ADMIN] Rebuild indexes
@ 2003-07-29 03:00  Shankar K <shan0075@yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Shankar K @ 2003-07-29 03:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2003-07-29 03:00 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-28 21:29 Rebuild indexes Shankar K <shan0075@yahoo.com>
2003-07-29 00:18 ` Josh Berkus <josh@agliodbs.com>
2003-07-29 02:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-29 03:00   ` Shankar K <shan0075@yahoo.com>

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