Re: query is taking longer time after a while

In response to Vick Khera <vivek(at)khera(dot)org>:

> On Tue, Sep 29, 2009 at 9:48 AM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> > There has (over the last few years) been a lot of speculation from people
> > who think that indexes may suffer performance degradation under some
> > workloads.  I've yet to see any actual evidence.
>
> Just last week I reindexed a 70+ million row table and shaved about
> 50% of the bloated index pages on a two integer column index. I
> believe it hadn't been reindexed in about 6 months.

Right. I've seen the same kind of thing with our Bacula databases.

> I regularly have to re-index for performance purposes. My data lives
> on most tables for about 6 months and is then deleted, or lives
> forever and is updated frequently.

This is the part that I've yet to see, is any actual demonstration that
this makes a _performance_ difference. The space saving is well known
and easy to demonstrate, but in my own tests, whether or not doing a
REINDEX has any appreciable performance improvement has never been clear.
It stands to reason that it will, but I've just never seen demonstrated.

It's quite possible that I simply missed the discussion thread on which
this was shown.

I'm not arguing with you. I'm simply curious as to whether index bloat
could cause the magnitude of performance problem the OP is having, and I
don't have any personal experience with that degree of problem, and I've
not heard it from anyone else, either. In my personal experience, REINDEX
seems to provide only a nominal improvement, but it's likely that I'm not
seeing the worst case that others are describing.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/