Re: Reduce maximum error in tuples estimation after vacuum.

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. 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

Using n_dead_tup before vacuuming seems to make it better but I
heard that the plan is abandoned from some reason I don't know.

So I've come up with the another plan - using FSM to estimate the
tuple density in unscanned pages. The point is that make
estimation reliying on the uniformity of tuple length instead of
tuple density. This change seems keeping that errors under a few
times of tuples. Additional page reads for FSM are about 4000th
(SlotsPerFSMPage) of the skipped pages, and I suppose this is
tolerable during vacuum.

Overall algorithm could be illistrated as below,

- summing up used bytes, max offnum(PageGetMaxOffsetNumber),
maximum free bytes for tuple data , and free bytes after page
vacuum through all scanned pages.

- summing up free bytes informed by FSM through all skipped
pages.

- Calculate mean tuple length from the overall used bytes and
sum of max offnums, and scanned pages.

- Guess tuple density in skipped pages using overall free bytes
from FSM and the mean tuple length calculated above.

- Finally, feed estimated number of the live tuples BEFORE
vacuum into vac_estimate_reltuples.

Of course this method affected by the imbalance of tuple LENGTH,
but it also seems to be kept within a few times of the number of
tuples.

for rows with invariable length, the test for head shows, where
"tups est" is pg_class.reltuples and "tups real" is count(*).

del% | pages | n_live_tup | tups est | tups real | est/real | bufs
-----+-------+------------+----------+-----------+----------+------
0.9 | 4425 | 100001 | 470626 | 100001 | 4.706 | 3985
0.95 | 4425 | 50001 | 441196 | 50001 | 8.824 | 4206
0.99 | 4425 | 417670 | 417670 | 10001 | 41.763 | 4383

and with the patch

0.9 | 4425 | 106169 | 106169 | 100001 | 1.062 | 3985
0.95 | 4425 | 56373 | 56373 | 50001 | 1.127 | 4206
0.99 | 4425 | 10001 | 16535 | 10001 | 1.653 | 4383

What do you think about this?

=====

The attached files are:

- vacuum_est_improve_20130614.patch: the patch for this proposal

- vactest.sql: sql script to cause the sitiation

- vactest.sh: test script to find the errors relating this patch.

- test_result.txt: all of the test result for various deletion
ratio which the test script above yields.

regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center

Sorry, I made an mistake.

"Kyotaro HORIGUCHI" <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>:

> Overall algorithm could be illistrated as below,
>
> - summing up used bytes, max offnum(PageGetMaxOffsetNumber),

Not "max" offnum, the number of linp's used after page vacuum.

> maximum free bytes for tuple data , and free bytes after page
> vacuum through all scanned pages.
>
> - summing up free bytes informed by FSM through all skipped
> pages.
>
> - Calculate mean tuple length from the overall used bytes and
> sum of max offnums, and scanned pages.

Here also is the same. not sum of max offnum but total of used
entrre(linp)s.

> - Guess tuple density in skipped pages using overall free bytes
> from FSM and the mean tuple length calculated above.
>
> - Finally, feed estimated number of the live tuples BEFORE
> vacuum into vac_estimate_reltuples.

regards,

--
Kyotaro Horiguchi

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.

Hello,

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

I think you had reproduced it.

> Output on Windows:
> -------------------
> postgres=# create table t (a int, b int);
(snip)
> 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)

Yes, this is the same for me. You should've done this instead,

postgres=# select reltuples from pg_class where relname = 't';
reltuples
-----------
1e+06
(1 row)

This is 100 times larger than n_live_tup, and it is this value
which used for judge the necessity of autovacuum.

autovacuum.c: 2695
| reltuples = classForm->reltuples;
| vactuples = tabentry->n_dead_tuples;
....
| vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
| anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;

Although..

> 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)

... Mmm.. I have following figures for the same operation.

relpages | n_live_tup | reltuples | tuples | ratio
----------+------------+-----------+--------+------------------
4425 | 417670 | 417670 | 10001 | 41.7628237176282

I condisider on this for a time..

> 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?

Could you let me know the pg's version you're running? And it is
appreciated if you're kindly show me the vacuum logs while
testing.

# I found a silly bug in the patch, but I put it off.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

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)
========

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

On Tuesday, June 25, 2013 2:06 PM Kyotaro HORIGUCHI wrote:
> Hello,
>
> > I have tried to reproduce the problem in different m/c's, but
> couldn't
> > reproduce it.
> > I have ran tests with default configuration.
>
> I think you had reproduced it.
>
> > Output on Windows:
> > -------------------
> > postgres=# create table t (a int, b int);
> (snip)
> > 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)
>
> Yes, this is the same for me. You should've done this instead,
>
> postgres=# select reltuples from pg_class where relname = 't';
> reltuples
> -----------
> 1e+06
> (1 row)
>
> This is 100 times larger than n_live_tup, and it is this value
> which used for judge the necessity of autovacuum.
>
> autovacuum.c: 2695
> | reltuples = classForm->reltuples;
> | vactuples = tabentry->n_dead_tuples;
> ....
> | vacthresh = (float4) vac_base_thresh + vac_scale_factor *
> reltuples;
> | anlthresh = (float4) anl_base_thresh + anl_scale_factor *
> reltuples;
>
> Although..
>
> > 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)
>
> ... Mmm.. I have following figures for the same operation.
>
>
> relpages | n_live_tup | reltuples | tuples | ratio
> ----------+------------+-----------+--------+------------------
> 4425 | 417670 | 417670 | 10001 | 41.7628237176282
>
> I condisider on this for a time..
>
> > 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?
>
> Could you let me know the pg's version you're running?

I had used 9.4 Head to run above tests.
Sorry, yesterday I was busy with some other work so could not got time to
check and work on this issue further.
I shall try to work on it today.

> And it is
> appreciated if you're kindly show me the vacuum logs while
> testing.
>
> # I found a silly bug in the patch, but I put it off.
>
> regards,
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center

On Wednesday, June 26, 2013 7:40 AM Kyotaro HORIGUCHI wrote:
> 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?

I could see the same output with your latest script, also I could reproduce
the test if I run the test with individual sql statements.
One of the main point for reproducing individual test was to keep autovacuum
= off.

Now I can look into it further, I have still not gone through in detail
about your new approach to calculate the reltuples, but I am wondering
whether there can be anyway with which estimates can be improved with
different calculation in vac_estimate_reltuples().

One thing I have observed that 2nd parameter is_analyze of
vac_estimate_reltuples() is currently not used.

I cannot work on it till early next week, so others are welcome to join
review.

With Regards,
Amit Kapila.

On Thu, Jun 27, 2013 at 7:27 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> Now I can look into it further, I have still not gone through in detail
> about your new approach to calculate the reltuples, but I am wondering
> whether there can be anyway with which estimates can be improved with
> different calculation in vac_estimate_reltuples().

I think this is getting at the threshold question for this patch,
which is whether it's really making things better or just moving the
problems around. I mean, I have no problem accepting that the new
algorithm is (1) reasonably cheap and (2) better in some cases. But
if it's worse in other cases, which AFAICS hasn't been discussed, then
it's no good.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thursday, June 27, 2013 4:58 PM Amit Kapila wrote:
> On Wednesday, June 26, 2013 7:40 AM Kyotaro HORIGUCHI wrote:
> > 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?
>
> I could see the same output with your latest script, also I could
> reproduce
> the test if I run the test with individual sql statements.
> One of the main point for reproducing individual test was to keep
> autovacuum
> = off.

I checked further that why I could not reproduce the issue with
autovacuum=on.
The reason is that it starts analyzer which changes the value for reltuples
in pg_class and after that the estimated and real values become same.
Kindly refer below code:

relation_needs_vacanalyze()
{
..
anltuples = tabentry->changes_since_analyze;
..
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
..
*doanalyze = (anltuples > anlthresh);
}

Test Results
--------------

postgres=# drop table if exists t;
DROP TABLE
postgres=# create table t (a int, b int, c int, d int default 0, e int
default 0
, f int default 0);
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=# vacuum t;
VACUUM
postgres=# 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';
relpages | n_live_tup | reltuples | tuples | ratio
----------+------------+-----------+--------+------------------
6370 | 417600 | 417600 | 10001 | 41.7558244175582
(1 row)

Here I waited for 1 minute (sufficient time so that analyzer should get
trigger if required).
Infact if you run Analyze t, that also would have served the purpose.

postgres=# 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';
relpages | n_live_tup | reltuples | tuples | ratio
----------+------------+-----------+--------+-------
6370 | 10001 | 10001 | 10001 | 1
(1 row)

Now if subsequent analyzer run corrects the estimate, don't you think that
it is sufficient for the problem reported?

With Regards,
Amit Kapila.

Hello,

> I could see the same output with your latest script, also I could reproduce
> the test if I run the test with individual sql statements.
> One of the main point for reproducing individual test was to keep autovacuum
> = off.

I see. Autovacuum's nap time is 60 sconds for the default
settings. Your operation might help it to snipe the window
between the last massive delete and the next explict vacuum in
store_result().. Anyway setting autovacuum to off should aid to
make clean environment fot this issue.

> Now I can look into it further, I have still not gone through in detail
> about your new approach to calculate the reltuples, but I am wondering
> whether there can be anyway with which estimates can be improved with
> different calculation in vac_estimate_reltuples().

I'll explain this in other words alghough It might be
repetitious.

It is tough to decide how to modify there. Currently I decided to
preserve vac_estimate_reltuples as possible as it is. For that
objective, I picked up old_rel_tuples as intermediate variable
for the aid to 'deceive' the function. This can be different form
deciding to separate this estimation function from that for
analyze.

As I described before, vac_estimates_reltuples has a presumption
that the tuple density in skipped pages is not so different from
that in whole table before vacuuming. Since the density is
calculated without using any hint about the skipped pages, and it
cannot tell how much tuples aganst pg_class.reltuples is already
dead, the value can be far different from the true one and cannot
be verified. Given that we canot use
pg_stat_user_tables.n_dead_tup, reading all pages can fix it but
the penalty should be intolerable.

Using FSM to know the used bytes in skipped pages (which is all
visible by the definition) seems to give good estimations of the
tuples in the skipped pages to some extent assuming the
uniformity of tuple length. Of course strong deviation in length
can deceive the algorithm.

Does it make sense for you?

I might could show the numerical explanation but I'm afraind I
can't do it for now. I'll be able to take time sooner... (also
for reviewing..)

> One thing I have observed that 2nd parameter is_analyze of
> vac_estimate_reltuples() is currently not used.

Mmm, it seems to have been useless from the beginning of the
function...

> I cannot work on it till early next week, so others are welcome to join

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

On Wednesday, July 03, 2013 1:21 PM Kyotaro HORIGUCHI wrote:
> Hello,
>
> > I could see the same output with your latest script, also I could
> reproduce
> > the test if I run the test with individual sql statements.
> > One of the main point for reproducing individual test was to keep
> autovacuum
> > = off.
>
> I see. Autovacuum's nap time is 60 sconds for the default
> settings. Your operation might help it to snipe the window
> between the last massive delete and the next explict vacuum in
> store_result().. Anyway setting autovacuum to off should aid to
> make clean environment fot this issue.
>
> > Now I can look into it further, I have still not gone through in
> detail
> > about your new approach to calculate the reltuples, but I am
> wondering
> > whether there can be anyway with which estimates can be improved with
> > different calculation in vac_estimate_reltuples().
>
> I'll explain this in other words alghough It might be
> repetitious.
>
> It is tough to decide how to modify there. Currently I decided to
> preserve vac_estimate_reltuples as possible as it is. For that
> objective, I picked up old_rel_tuples as intermediate variable
> for the aid to 'deceive' the function. This can be different form
> deciding to separate this estimation function from that for
> analyze.
>
> As I described before, vac_estimates_reltuples has a presumption
> that the tuple density in skipped pages is not so different from
> that in whole table before vacuuming. Since the density is
> calculated without using any hint about the skipped pages, and it
> cannot tell how much tuples aganst pg_class.reltuples is already
> dead, the value can be far different from the true one and cannot
> be verified. Given that we canot use
> pg_stat_user_tables.n_dead_tup, reading all pages can fix it but
> the penalty should be intolerable.
>
> Using FSM to know the used bytes in skipped pages (which is all
> visible by the definition) seems to give good estimations of the
> tuples in the skipped pages to some extent assuming the
> uniformity of tuple length. Of course strong deviation in length
> can deceive the algorithm.
>
> Does it make sense for you?

I understood your patch's algorithm, but still I have doubt in my mind that
if the next analyze can correct the estimates,
Why would that be not sufficient. Please refer my last mail for analysis of
same
http://www.postgresql.org/message-id/000601ce77ad$7d3388e0$779a9aa0$@kapila@
huawei.com

Performance Data
------------------
I have checked few cases where FSM is not updated accurately, this patch
seems to give much worse results than current code.

Test with Patch
--------------------
1. Test given by you where tuple density is non-uniform

postgres=# drop table if exists t;
DROP TABLE
postgres=# create table t (a int, b int, c int, d int default 0, e int
default 0
, f int default 0);
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=# vacuum t;
VACUUM
postgres=# 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';
relpages | n_live_tup | reltuples | tuples | ratio
----------+------------+-----------+--------+------------------
6370 | 13596 | 13596 | 10001 | 1.35946405359464
(1 row)

2. Test where tuple density is non-uniform and FSM updates before
calculation in Vacuum are not accurate.
I have created index on table to simulate this test

postgres=# drop table if exists t;
DROP TABLE
postgres=# create table t (a int, b int, c int, d int default 0, e int
default 0
, f int default 0);
CREATE TABLE
postgres=# create index on t(a);
CREATE INDEX
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=# vacuum t;
VACUUM
postgres=# 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';
relpages | n_live_tup | reltuples | tuples | ratio
----------+------------+--------------+--------+------------------
6370 | 1001327 | 1.00133e+006 | 10001 | 100.122687731227
(1 row)

Now this result in tuple estimation worse than current code.

I think we need to have more tests to show that new calculation is better in
all cases than current calculation.

With Regards,
Amit Kapila.

On 07/03/2013 12:51 AM, Kyotaro HORIGUCHI wrote:
> It is tough to decide how to modify there. Currently I decided to
> preserve vac_estimate_reltuples as possible as it is. For that
> objective, I picked up old_rel_tuples as intermediate variable
> for the aid to 'deceive' the function. This can be different form
> deciding to separate this estimation function from that for
> analyze.

Kyotaro, do you think you'll be revising this patch in the next 2 days,
or should I bounce it, and you can resubmit it for the next commitfest?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Hello, I'm very sory to take your time on this mess.

ul 2013 16:06:11 +0530, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote in <014201ce7bc6$f71eb950$e55c2bf0$(at)kapila@huawei.com>
> I understood your patch's algorithm, but still I have doubt in my mind that
> if the next analyze can correct the estimates,
> Why would that be not sufficient. Please refer my last mail for analysis of
> same
> http://www.postgresql.org/message-id/000601ce77ad$7d3388e0$779a9aa0$@kapila@
> huawei.com

Hmm. I've reconfirmed what was happened on my test set.

As the result, the misestimation with dead_tup = 0 which I
thought to observe has turned out to be an illusion..

Tuple number estimation is working as it is expected.

I withdraw this patch.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center