Date: Sat, 10 Nov 2007 13:38:23 -0500
From: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: Curious about dead rows.
In-reply-to: <16627.1194714491@sss.pgh.pa.us>
To: pgsql performance <pgsql-performance@postgresql.org>
Message-id: <4735FA9F.1080805@verizon.net>
Organization: Institute for Regimented Whimsey
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
References: <4735DBDB.5050208@verizon.net> <16627.1194714491@sss.pgh.pa.us>
User-Agent: Thunderbird 1.5.0.9 (X11/20070317)

Tom Lane wrote:
> Jean-David Beyer <jeandavid8@verizon.net> writes:
>> I am doing lots of INSERTs on a table that starts out empty (I did a
>> TRUNCATE on it). I am not, AFAIK, doing DELETEs or UPDATEs. Autovacuum is
>> on. I moved logging up to debug2 level to see what was going on, and I get
>> things like this:
> 
>>  "vl_as": scanned 3000 of 5296 pages, containing 232944 live rows and 1033
>> dead rows; 3000 rows in sample, 411224 estimated total rows
> 
>> A little later, it says:
> 
>> "vl_as": scanned 3000 of 6916 pages, containing 233507 live rows and 493
>> dead rows; 3000 rows in sample, 538311 estimated total rows
> 
> Well, *something* is doing deletes or updates in that table.  Better
> look a bit harder at your application ...
> 
OK, you agree that if I am doing only INSERTs, that there should not be any
dead rows. Therefore, I _must_ be doing deletes or updates.

But the program is pretty simple, and I see no UPDATEs or DELETEs. I
searched all the program source files (that contain none of them) and all
the libraries I have written, and they have none either. Right now the
programs are not to the state where UPDATEs or DELETEs are required (though
they will be later). I am still developing them and it is easier to just
restore from backup or start over from the beginning since most of the
changes are data laundering from an ever-increasing number of spreadsheets.

Am I right that TRUNCATE deletes all the rows of a table. They may then be
still there, but would not autovacuum clean out the dead rows? Or maybe it
has not gotten to them yet? I could do an explicit one earlier.

-- 
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 13:10:01 up 18 days, 6:28, 7 users, load average: 4.46, 4.34, 4.23