BUG #13348: PostgreSQL 9.5 sampletable BUG return rows not the same as reltuples*sample factor?

Поиск
Список
Период
Сортировка
От digoal@126.com
Тема BUG #13348: PostgreSQL 9.5 sampletable BUG return rows not the same as reltuples*sample factor?
Дата
Msg-id 20150525030007.17710.95359@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #13348: PostgreSQL 9.5 sampletable BUG return rows not the same as reltuples*sample factor?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      13348
Logged by:          digoal
Email address:      digoal@126.com
PostgreSQL version: Unsupported/Unknown
Operating system:   CentOS 6.x x64
Description:

when use system sample method , it's return block's all row. yes ,it's
correct by PostgreSQL doc desc.
But when use BERNOULLI sample method, it's return rows not the same as
reltuples*factor.

Can we add filter to exact return rows equal to reltuples*factor ?

postgres=# create table test(id int);
CREATE TABLE
postgres=# insert into test select generate_series(1,100000000);
INSERT 0 100000000
Time: 128199.864 ms
postgres=# select count(*) from test tablesample system (0.000001);
 count
-------
   909
(1 row)
Time: 1.930 ms

postgres=# select 100000000*0.000001;
  ?column?
------------
 100.000000
(1 row)
Time: 0.631 ms

postgres=# select count(*) from (select ctid,* from test limit 100000) t
where ctid::text ~ '\(1,';
 count
-------
   909
(1 row)
Time: 196.232 ms

but

postgres=# select relpages,reltuples from pg_class where relname='test';
 relpages |  reltuples
----------+-------------
   110012 | 2.72691e+07
(1 row)
Time: 0.786 ms
postgres=# select count(*) from test;
   count
-----------
 100000000
(1 row)
Time: 12041.390 ms

postgres=# select count(*) from test tablesample BERNOULLI (0.000001) ;
 count
-------
     1
(1 row)
Time: 6245.059 ms
postgres=# select count(*) from test tablesample BERNOULLI (0.00001) ;
 count
-------
    13
(1 row)
Time: 6305.808 ms

postgres=# select 2.72691e+07*0.00001;
 ?column?
-----------
 272.69100
(1 row)
postgres=# vacuum analyze test;
VACUUM
Time: 9880.728 ms
postgres=# select relpages,reltuples from pg_class where relname='test';
 relpages | reltuples
----------+-----------
   110012 |     1e+08
(1 row)
Time: 0.438 ms
postgres=# select count(*) from test tablesample BERNOULLI (0.00001) ;
 count
-------
     6
(1 row)
Time: 6243.548 ms
postgres=# select count(*) from test tablesample BERNOULLI (0.00001) ;
 count
-------
     9
(1 row)
Time: 6275.948 ms
postgres=# select count(*) from test tablesample BERNOULLI (0.00001) ;
 count
-------
     9
(1 row)
Time: 6243.882 ms

В списке pgsql-bugs по дате отправления:

Предыдущее
От: andrew@tao11.riddles.org.uk
Дата:
Сообщение: BUG #13350: blindly fsyncing data dir considered harmful
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #13348: PostgreSQL 9.5 sampletable BUG return rows not the same as reltuples*sample factor?