Re: query is taking longer time after a while

tomrevam <tomer@fabrix.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