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

Re: Curious about dead rows.

From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
To: "Jean-David Beyer" <jeandavid8(at)verizon(dot)net>
Cc: "pgsql performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Curious about dead rows.
Date: 2007-11-14 00:28:24
Message-ID: b42b73150711131628q1469df3el6f557e10de07a05@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Nov 10, 2007 1:38 PM, Jean-David Beyer <jeandavid8(at)verizon(dot)net> wrote:
> Tom Lane wrote:
> > Jean-David Beyer <jeandavid8(at)verizon(dot)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.

what does pg_stat_all_tables say (assuming row level stats are on)?

merlin

In response to

Responses

pgsql-performance by date

Next:From: Jean-David BeyerDate: 2007-11-14 02:26:08
Subject: Re: Curious about dead rows.
Previous:From: Ansgar -59cobalt- WiechersDate: 2007-11-14 00:11:03
Subject: Re: Curious about dead rows.

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