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

From: digoal(at)126(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13348: PostgreSQL 9.5 sampletable BUG return rows not the same as reltuples*sample factor?
Date: 2015-05-25 03:00:07
Message-ID: 20150525030007.17710.95359@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13348
Logged by: digoal
Email address: digoal(at)126(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Paquier 2015-05-25 05:05:30 Incorrect processing of CREATE TRANSFORM with DDL deparding
Previous Message Tom Lane 2015-05-24 15:02:54 Re: pg_upgrade slowness for databases with many tables