From: Joshua D. Drake <jd@commandprompt.com>
To: Jean-David Beyer <jeandavid8@verizon.net>
Cc: pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: Curious about dead rows.
Date: Sat, 10 Nov 2007 10:51:19 -0800
Message-ID: <20071110105119.35346a29@scratch> (raw)
In-Reply-To: <4735FA9F.1080805@verizon.net>
References: <4735DBDB.5050208@verizon.net>
<16627.1194714491@sss.pgh.pa.us>
<4735FA9F.1080805@verizon.net>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Sat, 10 Nov 2007 13:38:23 -0500
Jean-David Beyer <jeandavid8@verizon.net> wrote:
> 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.
Truncate will not create dead rows. However ROLLBACK will. Are you
getting any duplicate key errors or anything like that when you insert?
Sincerely,
Joshua D. Drake
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHNf2pATb/zqfZUUQRApYEAKCWp107koBhpWQbMjwLybBB6SvDmQCgj8Q6
kPAE4qe1fT6RNbFtqlIw52M=
=/5us
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jd@commandprompt.com, jeandavid8@verizon.net
Subject: Re: Curious about dead rows.
In-Reply-To: <20071110105119.35346a29@scratch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox