Re: EXPLAIN output explanation requested

A. Kretschmer wrote:
> am Tue, dem 03.06.2008, um 19:51:05 +0200 mailte Ron Arts folgendes:
>> A. Kretschmer schreef:
>>> am Tue, dem 03.06.2008, um 12:35:34 +0200 mailte A. Kretschmer folgendes:
>>>> I guess, you have much insert/delete or update - operations on this
>>>> table and no recent vacuum.
>>>>
>>>> Try to run a 'vacuum full;' and re-run your query. And, run a 'explain
>>>> analyse <your query>' to see the estimated costs and the real costs.
>>> Btw, run 'select relpages, reltuples from pg_class where relname='phone';
>>> before and after the 'vacuum full' and show us the result.
>>>
>>>
>>> Andreas
>> I did a vacuum without success, but `vacuum full` did the trick:
>
> vacuum: tags dead tuples for reuse
> vacuum full: recover the space occopied by dead tuples
>
>
> Andreas

The docs state (explaining that VACUUM FULL is unnecessary because it only returns diskspace to the OS)

> The standard form of VACUUM is best used with the goal of maintaining a fairly level steady-state usage of disk space. If you need to return
> disk space to the operating system you can use VACUUM FULL — but what's the point of releasing disk space that will only have to be allocated
> again soon?

But it seems there is a very valid reason for running VACUUM FULL once in a while: when records are frequently updated,
but seldomly added/deleted to the table. Otherwise the number of stale records keep growing, and so will the
time to scan the entire table.

And the sentence:

> VACUUM FULL is recommended for cases where you know you have deleted the majority of rows in a table

should also include 'or updated the same records many times'. Right?

Ron