pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRebuild 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