Skip site navigation (1) Skip section navigation (2)

Re: EXPLAIN output explanation requested

From: Ron Arts <ron(dot)arts(at)neonova(dot)nl>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: EXPLAIN output explanation requested
Date: 2008-06-03 20:00:31
Message-ID: 4845A2DF.9070601@neonova.nl (view raw or flat)
Thread:
Lists: pgsql-novice
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



In response to

Responses

pgsql-novice by date

Next:From: ceduardoDate: 2008-06-04 04:03:04
Subject: Error on Windows Server 2003 "could not connect to server: ..."
Previous:From: A. KretschmerDate: 2008-06-03 18:28:39
Subject: Re: EXPLAIN output explanation requested

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group