Re: query is taking longer time after a while

Bill Moran <wmoran(at)potentialtech(dot)com> writes:
>> My interpretation of the OPs problem was that the inserts and deletes
>> were happening at similar rates. Thus this won't be a problem.

> 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.

There is a known usage pattern that leads to bloated btree indexes:
if you load lots of data and then decimate it after awhile, using a rule
that matches some index's order. For instance if you load many years'
worth of daily data and then delete all but the last entry for each
month, then an index on the date column will be left in a not-very-dense
state with only about 1/30th as many entries per page as it should
ideally have. VACUUM does not attempt to merge partially-full index
pages so it can't fix this for you, and subsequent additions will
probably be going at the end of the index so there is no hope of the
underused pages getting repopulated.

The number of actual occurrences of this pattern in the field doesn't
seem to be very high though.

> The OP did mention that he's using autovac, which will take care of
> both vacuum and analyze for him. However, he didn't provide his
> autovac config, and it happens at times that the defaults are not
> aggressive enough to keep a table well-maintained.

Agreed, that's definitely a risk.

regards, tom lane