pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jean-David Beyer <jeandavid8@verizon.net>
To: pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: Curious about dead rows.
Date: Wed, 14 Nov 2007 16:07:36 -0500
Message-ID: <473B6398.9030507@verizon.net> (raw )
In-Reply-To: <473B4AB4.70506@sime.com >
References: <4735DBDB.5050208@verizon.net >
<473B4AB4.70506@sime.com >
Mario Weilguni wrote:
> Jean-David Beyer schrieb:
>> 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
>>
>> (I suppose that means autovacuum is working.) Is this normal, or have
>> I got
>> something wrong? Why so many dead rows when just doing inserts? It is not
>> that I think the number is too high, considering the number of rows in
>> the
>> table at the point where I copied this line. It is just that I do not
>> understand why there are any.
>>
>>
> Did you rollback some transactions? It will generate dead rows too - at
> least I think so.
>
No, and the statistics confirm this.
stock=> SELECT * FROM pg_stat_database WHERE datname = 'stock';
datid | datname | numbackends | xact_commit | xact_rollback | blks_read |
blks_hit
-------+---------+-------------+-------------+---------------+-----------+-----------
16402 | stock | 1 | 1267 | 0 | 232234 |
146426135
(1 row)
stock=> SELECT * FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER
BY relname;
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
-------+------------+----------+----------+--------------+----------+---------------+-----------+-----------+-----------
89000 | public | co_name | 7 | 215873 | 1 |
30839 | 0 | 0 | 0
89004 | public | company | 9 | 219519 | 5624483 |
5648873 | 0 | 0 | 0
89008 | public | div | 7 | 0 | 1 |
0 | 0 | 0 | 0
89010 | public | djia | 4 | 2044 | 0 |
0 | 0 | 0 | 0
89012 | public | earn | 2 | 0 | 0 |
0 | 0 | 0 | 0
89014 | public | ibd | 5 | 0 | 1 |
0 | 0 | 0 | 0
89016 | public | merg | 2 | 0 | 0 |
0 | 0 | 0 | 0
89018 | public | price | 9 | 0 | 1 |
0 | 0 | 0 | 0
89022 | public | source | 3 | 27 | 0 |
0 | 0 | 0 | 0
89025 | public | sp_500 | 2 | 0 | 0 |
0 | 0 | 0 | 0
89027 | public | split | 3 | 0 | 1 |
0 | 0 | 0 | 0
89029 | public | tick | 13 | 400946 | 980983 |
1510922 | 0 | 0 | 0
89034 | public | vl_as | 7 | 6524595 | 1 |
932085 | 932085 | 0 | 0
89036 | public | vl_cf | 7 | 6317808 | 1 |
902544 | 902544 | 0 | 0
89038 | public | vl_in | 7 | 6798351 | 1 |
971193 | 966989 | 0 | 0
89040 | public | vl_li | 7 | 6524595 | 1 |
932085 | 932085 | 0 | 0
89042 | public | vl_mi | 7 | 6368579 | 1 |
909797 | 909797 | 0 | 0
89044 | public | vl_ranks | 8 | 7624818 | 1 |
985548 | 980982 | 0 | 0
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 16:05:01 up 22 days, 9:23, 0 users, load average: 4.45, 4.11, 4.03
view thread (41+ messages) latest in thread
Message-ID: <473B6398.9030507@verizon.net>
Permalink: ../473B6398.9030507@verizon.net/
Also on: postgresql.org/message-id/473B6398.9030507@verizon.net
copy link · copy postgr.es
reply 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: jeandavid8@verizon.net
Subject: Re: Curious about dead rows.
In-Reply-To: <473B6398.9030507@verizon.net>
* 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