Re: Curious about dead rows.

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