Re: Reduce maximum error in tuples estimation after vacuum.

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.