Re: Reduce maximum error in tuples estimation after vacuum.

On Friday, June 14, 2013 2:05 PM Kyotaro HORIGUCHI wrote:
> Hello,
>
> Postgresql estimates the number of live tuples after the vacuum has
> left some buffers unscanned. This estimation does well for most cases,
> but makes completely different result with a strong imbalance of tuple
> density.
>
> For example,
>
> create table t (a int, b int);
> insert into t (select a, (random() * 100000)::int from
> generate_series((select count(*) from t) + 1, 1000000) 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) * 0.99;
>
> After this, pg_stat_user_tables.n_live_tup shows 417670 which is
> 41 times larger than the real number of rows 100001.
Number should be 10001 not 100001.

> And what makes it
> worse, autovacuum nor autoanalyze won't run until n_dead_tup goes above
> 8 times larger than the real number of tuples in the table for the
> default settings..
>
>
> | postgres=# select n_live_tup, n_dead_tup
> | from pg_stat_user_tables where relname='t'; n_live_tup |
> | n_dead_tup
> | ------------+------------
> | 417670 | 0
> |
> | postgres=# select reltuples from pg_class where relname='t';
> | reltuples
> | -----------
> | 417670
> |
> | postgres=# select count(*) from t;
> | count
> | -------
> | 10001

I have tried to reproduce the problem in different m/c's, but couldn't
reproduce it.
I have ran tests with default configuration.

Output on Windows:
-------------------
postgres=# create table t (a int, b int);
CREATE TABLE
postgres=# insert into t (select a, (random() * 100000)::int from
generate_serie
s((select count(*) from t) + 1, 1000000) a);
INSERT 0 1000000
postgres=# update t set b = b + 1 where a < (select count(*) from t) * 0.7;

UPDATE 699999
postgres=# vacuum t;
VACUUM
postgres=# delete from t where a < (select count(*) from t) * 0.99;
DELETE 989999
postgres=#
postgres=# select n_live_tup, n_dead_tup from pg_stat_user_tables where
relname=
't';
n_live_tup | n_dead_tup
------------+------------
10001 | 989999
(1 row)

Output on Suse
----------------
postgres=# drop table if exists t;
create table t (a int, b int);
insert into t (select a, (random() * 100000)::int from
generate_series((select count(*) from t) + 1, 1000000) 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) * 0.99;
vacuum t;
select c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t) as
tuples, reltuples::float / (select count(*) from t) as ratio from
pg_stat_user_tables s, pg_class c where s.relname = 't' and c.relname =
't';DROP TABLE
postgres=# CREATE TABLE
postgres=# INSERT 0 1000000
postgres=# UPDATE 699999
postgres=# VACUUM
postgres=# DELETE 989999
postgres=# VACUUM
postgres=#
relpages | n_live_tup | reltuples | tuples | ratio
----------+------------+-----------+--------+-------
4425 | 10001 | 10001 | 10001 | 1
(1 row)

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/''

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?

With Regards,
Amit Kapila.