Re: Reduce maximum error in tuples estimation after vacuum.

Sorry for lots of mistakes I've made,

I noticed that I was dancing on the 9.3dev at sometime, so I
reran on 9.4devel up to date, having the same result from the
view of inaccuracy of pg_class.reltuples after vacuuming.

Although, what differs from my old testset, vacuum reported that
it sanned the whole table pages. It cannot be the case that it
gives such a reltuples value for the case since before...

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

=====
> When I tried to run vactest.sh, it gives below error:
> linux:~/akapila/vacuum_nlivetup> ./vactest.sh
> ./vactest.sh: line 11: syntax error near unexpected token `&'
> ./vactest.sh: line 11: ` psql ${dbname} -c "vacuum verbose t" |&
> egrep "INFO: *\"t\": found " | sed -e 's/^.* versions in \([0-9]*\)
> .*$/\1/''

'|&' I carelessly used should not be understood by real
/bin/sh. Since I might use other notations out of sh syntex, it
seems to more helpful to replace the shbang with '/bin/bash'
instead of '/bin/sh'..

In addition, the test script I brought up here discards all
outputs of sql commands. The attached script shows the verbose
response from vacuum.

- replaced shbang with /bin/bash
- replaced '|&' notation with '2>&1 |'
- removed '> /dev/null' from psql commandline

> Can you help me in reproducing the problem by letting me know
> if I am doing something wrong or results of test are not
> predictable?

Thank you for getting involved and sorry for the insufficient
preparation. But please wait for a while to go on.

I ran the attached revised script for the distcleaned current
head on the master branch (9.4devel) on CentOS 6.4 and got the
same result as previous, shown below. But I found the result
ununderstandable. I'd like to have a bit time to diagnose this.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

===========
$ ./vactest.sh
test1 ratio = 0.4
DROP TABLE
CREATE TABLE
INSERT 0 1000000
UPDATE 699999
psql:<stdin>:5: INFO: vacuuming "public.t"
psql:<stdin>:5: INFO: "t": removed 699999 row versions in 4459 pages
psql:<stdin>:5: INFO: "t": found 699999 removable, 1000000 nonremovable row versions in 10829 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.13u sec elapsed 0.13 sec.
VACUUM
DELETE 399999
test1 ratio = 0.99
DROP TABLE
CREATE TABLE
INSERT 0 1000000
UPDATE 699999
psql:<stdin>:5: INFO: vacuuming "public.t"
psql:<stdin>:5: INFO: "t": removed 699999 row versions in 4459 pages
psql:<stdin>:5: INFO: "t": found 699999 removable, 1000000 nonremovable row versions in 10829 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.15u sec elapsed 0.25 sec.
VACUUM
DELETE 989999
test1 ratio = 1.00
DROP TABLE
CREATE TABLE
INSERT 0 1000000
UPDATE 699999
psql:<stdin>:5: INFO: vacuuming "public.t"
psql:<stdin>:5: INFO: "t": removed 699999 row versions in 4459 pages
psql:<stdin>:5: INFO: "t": found 699999 removable, 1000000 nonremovable row versions in 10829 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.14u sec elapsed 0.19 sec.
VACUUM
DELETE 999999
# | 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 | 1 | 411673 | 1 | 411673.000 | 6371
(3 rows)
========