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