Re: query is taking longer time after a while

tomrevam <tomer(at)fabrix(dot)tv> writes:
> Bill Moran wrote:
>> My apologies, I should have asked for the output of VACUUM VERBOSE on
>> the problem table in conjunction with these settings. (make sure you
>> do VACUUM VERBOSE when the table is exhibiting the speed problem)

> INFO: "session_allocation_info": found 38814 removable, 4662043
> nonremovable row versions in 205325 pages
> DETAIL: 1590 dead row versions cannot be removed yet.
> There were 11978040 unused item pointers.
> 89189 pages contain useful free space.
> 0 pages are entirely empty.

That doesn't look bad on the surface --- only 1% dead rows --- but
the very large number of unused item pointers indicates that at some
point in the past the table had either a whole lot of dead rows, or
a much larger number of live rows than it does now. I'm still going
to bet that the problem comes down to inadequate vacuuming.

regards, tom lane