Re: Importance of re-index

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