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

Andrew Sullivan wrote:
> On Wed, Nov 14, 2007 at 07:12:45AM -0500, Jean-David Beyer wrote:
>> I know there have been rollbacks but I do a REINDEX, CLUSTER, and
>> VACUUM ANALYZE before starting the inserts in question. Do I need to do
>> a VACUUM FULL ANALYZE instead?
>
> I had another idea.  As Alvaro says, CLUSTER will do everything you need.
>  But are you sure there are _no other_ transactions open when you do
> that? This could cause problems, and CLUSTER's behaviour with other open
> transactions is not, um, friendly prior to the current beta.
>
These were not done at exactly the same time, but as close as I can.

REINDEX
CLUSTER;
CLUSTER
 (part of a shell script that runs the other stuff)

File `/homeB/jdbeyer/stocks/DATA/valueLine/19860103.tsv' OK
File `/homeB/jdbeyer/stocks/DATA/valueLine/19860131.tsv' OK
File `/homeB/jdbeyer/stocks/DATA/valueLine/19860228.tsv' OK
File `/homeB/jdbeyer/stocks/DATA/valueLine/19860328.tsv' OK
File `/homeB/jdbeyer/stocks/DATA/valueLine/19860502.tsv' OK
File `/homeB/jdbeyer/stocks/DATA/valueLine/19860530.tsv' OK
File `/homeB/jdbeyer/stocks/DATA/valueLine/19860627.tsv' OK
(this is showing the program being run on different data).

stock=# SELECT * FROM pg_stat_database WHERE datname = 'stock';
 datid | datname | numbackends | xact_commit | xact_rollback | blks_read |
blks_hit
-------+---------+-------------+-------------+---------------+-----------+----------
 16402 | stock   |           2 |         152 |             0 |     18048 |
15444563
(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  |        0 |            0 |        0 |
      0 |         0 |         0 |         0
 89004 | public     | company  |        0 |            0 |   938764 |
 938764 |         0 |         0 |         0
 89029 | public     | tick     |        0 |            0 |   189737 |
 279580 |         0 |         0 |         0
 89034 | public     | vl_as    |        0 |            0 |        0 |
      0 |    140840 |         0 |         0
 89036 | public     | vl_cf    |        0 |            0 |        0 |
      0 |    140840 |         0 |         0
 89038 | public     | vl_in    |        0 |            0 |        0 |
      0 |    185667 |         0 |         0
 89040 | public     | vl_li    |        0 |            0 |        0 |
      0 |    140840 |         0 |         0
 89042 | public     | vl_mi    |        0 |            0 |        0 |
      0 |    140840 |         0 |         0
 89044 | public     | vl_ranks |        0 |            0 |        0 |
      0 |    189737 |         0 |         0
(18 rows)

2007-11-14 12:00:31 EST DEBUG:  analyzing "public.vl_in"
2007-11-14 12:00:31 EST DEBUG:  "vl_in": scanned 2001 of 2001 pages,
containing 183983 live rows and 52 dead rows; 3000 rows in sample, 183983
estimated total rows
2007-11-14 12:00:31 EST DEBUG:  analyzing "public.vl_cf"
2007-11-14 12:00:31 EST DEBUG:  "vl_cf": scanned 1064 of 1064 pages,
containing 134952 live rows and 89 dead rows; 3000 rows in sample, 134952
estimated total rows
2007-11-14 12:00:31 EST DEBUG:  analyzing "public.vl_as"
2007-11-14 12:00:31 EST DEBUG:  "vl_as": scanned 1732 of 1732 pages,
containing 134952 live rows and 120 dead rows; 3000 rows in sample, 134952
estimated total rows
2007-11-14 12:00:31 EST DEBUG:  analyzing "public.vl_ranks"
2007-11-14 12:00:31 EST DEBUG:  "vl_ranks": scanned 1485 of 1485 pages,
containing 188415 live rows and 162 dead rows; 3000 rows in sample, 188415
estimated total rows
2007-11-14 12:00:31 EST DEBUG:  analyzing "public.vl_mi"
2007-11-14 12:00:31 EST DEBUG:  "vl_mi": scanned 1325 of 1325 pages,
containing 134952 live rows and 191 dead rows; 3000 rows in sample, 134952
estimated total rows
2007-11-14 12:00:31 EST DEBUG:  analyzing "public.vl_li"
2007-11-14 12:00:31 EST DEBUG:  "vl_li": scanned 1326 of 1326 pages,
containing 134952 live rows and 218 dead rows; 3000 rows in sample, 134952
estimated total rows



--
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 11:55:01 up 22 days, 5:13, 3 users, load average: 5.13, 4.71, 4.74