Re: Curious about dead rows.

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