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.