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