Re: Importance of re-index

From: Jim Nasby <jnasby(at)pervasive(dot)com>
To: John Sidney-Woollett <johnsw(at)wardbrook(dot)com>
Cc: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>, pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Importance of re-index
Date: 2006-08-19 03:40:29
Message-ID: E547CDE6-BC03-4C4A-BB43-67B4481C8468@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Aug 10, 2006, at 1:57 AM, John Sidney-Woollett wrote:

> Disagree.
>
> We only apply reindex on tables that see lots of updates...
>
> With our 7.4.x databases we vacuum each day, but we see real
> performance gains after re-indexing too - we see lower load
> averages and no decrease in responsiveness over time. Plus we have
> the benefit of reduced disk space usage.

You may be getting temporary performance gains by shrinking the
indexes to a level that's un-sustainable. As you update the table, it
needs to create new index keys, which have to go somewhere.

Also, if I had a dollar for everytime someone thought they were safe
from bloat because they were vacuuming once a day, I'd be living on a
beach somewhere. There's very few databases I've seen where vacuuming
once a day is sufficient, so it's very likely that you are suffering
fromm bloat.

> I think that the two things go hand in hand, although vacuum is the
> most important.
>
> John
>
> Jim C. Nasby wrote:
>> And if you're vacuuming frequently enough, there shouldn't be that
>> much
>> need to reindex.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Nicolas Barbier 2006-08-19 10:03:23 Re: Importance of re-index
Previous Message Michael Fuhr 2006-08-19 02:27:12 Re: Inserting Data