Обсуждение: BUG #13348: PostgreSQL 9.5 sampletable BUG return rows not the same as reltuples*sample factor?
BUG #13348: PostgreSQL 9.5 sampletable BUG return rows not the same as reltuples*sample factor?
От
digoal@126.com
Дата:
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
digoal@126.com writes: > 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 ? No. It is neither practical nor required by the standard that the number of returned rows be exactly N*S. If we tried to do that, rows near the end of the table would end up having a different probability of being selected than rows near the front. regards, tom lane