Re: Reduce maximum error in tuples estimation after vacuum.

I've recovered from messing up.

<snip>
> Please let me have a bit of time to diagnose this. 

I was completely messed up and walking on the wrong way. I looked
into the vacuum for UPDATEs, not DELETE's so it's quite resonable
to have such results.

The renewed test script attached shows the verbose output of
vacuum after the deletes. I had following output from it.

# I belive this runs for you..

| INFO: "t": found 989999 removable, 110 nonremovable row
|       versions in 6308 out of 10829 pages

On such a case of partially-scanned, lazy_scan_heap() tries to
estimate resulting num_tuples in vac_estimate_reltuples()
assuming the uniformity of tuple density, which failes for such a
a strong imbalance made by bulk updates.

Do you find any differences between what you will have and the
following I had?

| $ ./vactest.sh
| ### test1 ratio = 0.4
| INFO:  vacuuming "public.t"
| INFO:  "t": removed 399999 row versions in 2549 pages
| INFO:  "t": found 399999 removable, 194 nonremovable row versions in 2549 out of 10829 pages
| DETAIL:  0 dead row versions cannot be removed yet.
| There were 0 unused item pointers.
| 0 pages are entirely empty.
| CPU 0.00s/0.04u sec elapsed 0.04 sec.
| ### test1 ratio = 0.99
| INFO:  vacuuming "public.t"
| INFO:  "t": removed 989999 row versions in 6308 pages
| INFO:  "t": found 989999 removable, 110 nonremovable row versions in 6308 out of 10829 pages
| DETAIL:  0 dead row versions cannot be removed yet.
| There were 93 unused item pointers.
| 0 pages are entirely empty.
| CPU 0.00s/0.11u sec elapsed 0.24 sec.
| INFO:  "t": truncated 10829 to 6370 pages
| DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.
| ### test1 ratio = 1.00
| INFO:  vacuuming "public.t"
| INFO:  "t": removed 999999 row versions in 6371 pages
| INFO:  "t": found 999999 removable, 1 nonremovable row versions in 6371 out of 10829 pages
| DETAIL:  0 dead row versions cannot be removed yet.
| There were 93 unused item pointers.
| 0 pages are entirely empty.
| CPU 0.00s/0.11u sec elapsed 0.20 sec.
| INFO:  "t": truncated 10829 to 6370 pages
| DETAIL:  CPU 0.01s/0.00u sec elapsed 0.27 sec.
|  # | del% | ## | pages | n_live_tup | tups est | tups real |  est/real  | bufs 
| ---+------+----+-------+------------+----------+-----------+------------+------
|  1 |  0.4 |  1 | 10829 |     600001 |   764808 |    600001 |      1.275 | 2549
|  1 | 0.99 |  1 |  6370 |      10001 |   417600 |     10001 |     41.756 | 6308
|  1 |    1 |  1 |  6370 |     411673 |   411673 |         1 | 411673.000 | 6371
| (3 rows)

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
#! /bin/bash

dbname="postgres"
function insert_result() {psql ${dbname} -f - > /dev/null <<EOF
insert into result select $1, $2, $3, c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t), reltuples::float
/(select count(*) from t) from pg_stat_user_tables s, pg_class c where s.relname = 't' and c.relname = 't';
 
EOF
}

function vac_with_bufs() {local f=`mktemp vactest_XXXX`psql ${dbname} -c "vacuum verbose t" 2>>$f 1>/dev/nullcat $f
1>&2cat$f | egrep "INFO: *\"t\": found " | sed -e 's/^.* versions in \([0-9]*\) .*$/\1/'rm $f
 
}

function update_result_bufs() {local test_no=$1local delratio=$2local vac_no=$3local bufs=$4
psql ${dbname} -c "update result set bufs=${bufs} where \"#\"=$test_no and \"del%\"=$delratio and \"##\"=$vac_no"
>/dev/null
}

function store_result() {local test_no=$1local delratio=$2scanned_bufs=`vac_with_bufs`insert_result $test_no $delratio
1update_result_bufs$test_no $delratio 1 $scanned_bufs
 
#    scanned_bufs=`vac_with_bufs`
#    insert_result $test_no $delratio 2
#    update_result_bufs $test_no $delratio 2 $scanned_bufs
}


function test1() {local delratio=$1
echo "### test1 ratio = $delratio"psql ${dbname} -f - > /dev/null <<EOF
drop table if exists t;
create table t (a int, b int, c int, d int default 0, e int default 0, f int default 0);
insert into t (select a, (random() * 100000)::int from generate_series((select count(*) from t) + 1, $nrows) a);
update t set b = b + 1 where a <  (select count(*) from t) * 0.7;
vacuum t;
delete from t where a < (select count(*) from t) * $delratio
EOFstore_result 1 $delratio
}

function test2() {local delratio=$1
echo "### test2 ratio = $delratio"psql ${dbname} -f - > /dev/null <<EOF
drop table if exists t;
create table t (a int, b text);
insert into t (select a, 'abcdefg' from generate_series((select count(*) from t) + 1, $nrows) a);
update t set b = repeat('abcdefghij', 250) where a <  (select count(*) from t) * 0.7;
vacuum t;
delete from t where a < (select count(*) from t) * $delratio;
EOFstore_result 2 $delratio
}

psql ${dbname} -f - > /dev/null <<EOF
drop table if exists result;
create table result ("#" int, "del%" float, "##" int, pages int, n_live_tup int, "tups est" int, "tups real" int,
"est/real"numeric(10, 3), bufs int default 0);
 
EOF
nrows=1000000
# test1 0.1
# test1 0.2
# test1 0.3
test1 0.4
# test1 0.5
# test1 0.6
# test1 0.7
# test1 0.8
# test1 0.9
# test1 0.95
test1 0.99
test1 1.00

# nrows=100000
# test2 0.1
# test2 0.2
# test2 0.3
# test2 0.4
# test2 0.5
# test2 0.6
# test2 0.7
# test2 0.8
# test2 0.9
# test2 0.95
# test2 0.99
# test2 1.00

psql ${dbname} -c 'select * from result order by "#", "del%", "##"'