Re: Curious about dead rows. - Mailing list pgsql-performance

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