1CREATE TABLE test_tablesample (id int, name text) WITH (fillfactor=10); 2-- use fillfactor so we don't have to load too much data to get multiple pages 3 4INSERT INTO test_tablesample 5 SELECT i, repeat(i::text, 200) FROM generate_series(0, 9) s(i); 6 7SELECT t.id FROM test_tablesample AS t TABLESAMPLE SYSTEM (50) REPEATABLE (0); 8SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (100.0/11) REPEATABLE (0); 9SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); 10SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (50) REPEATABLE (0); 11SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (5.5) REPEATABLE (0); 12 13-- 100% should give repeatable count results (ie, all rows) in any case 14SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100); 15SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100) REPEATABLE (1+2); 16SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100) REPEATABLE (0.4); 17 18CREATE VIEW test_tablesample_v1 AS 19 SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (10*2) REPEATABLE (2); 20CREATE VIEW test_tablesample_v2 AS 21 SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (99); 22\d+ test_tablesample_v1 23\d+ test_tablesample_v2 24 25-- check a sampled query doesn't affect cursor in progress 26BEGIN; 27DECLARE tablesample_cur CURSOR FOR 28 SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); 29 30FETCH FIRST FROM tablesample_cur; 31FETCH NEXT FROM tablesample_cur; 32FETCH NEXT FROM tablesample_cur; 33 34SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); 35 36FETCH NEXT FROM tablesample_cur; 37FETCH NEXT FROM tablesample_cur; 38FETCH NEXT FROM tablesample_cur; 39 40FETCH FIRST FROM tablesample_cur; 41FETCH NEXT FROM tablesample_cur; 42FETCH NEXT FROM tablesample_cur; 43FETCH NEXT FROM tablesample_cur; 44FETCH NEXT FROM tablesample_cur; 45FETCH NEXT FROM tablesample_cur; 46 47CLOSE tablesample_cur; 48END; 49 50EXPLAIN (COSTS OFF) 51 SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (2); 52EXPLAIN (COSTS OFF) 53 SELECT * FROM test_tablesample_v1; 54 55-- check inheritance behavior 56explain (costs off) 57 select count(*) from person tablesample bernoulli (100); 58select count(*) from person tablesample bernoulli (100); 59select count(*) from person; 60 61-- check that collations get assigned within the tablesample arguments 62SELECT count(*) FROM test_tablesample TABLESAMPLE bernoulli (('1'::text < '0'::text)::int); 63 64-- check behavior during rescans, as well as correct handling of min/max pct 65select * from 66 (values (0),(100)) v(pct), 67 lateral (select count(*) from tenk1 tablesample bernoulli (pct)) ss; 68select * from 69 (values (0),(100)) v(pct), 70 lateral (select count(*) from tenk1 tablesample system (pct)) ss; 71explain (costs off) 72select pct, count(unique1) from 73 (values (0),(100)) v(pct), 74 lateral (select * from tenk1 tablesample bernoulli (pct)) ss 75 group by pct; 76select pct, count(unique1) from 77 (values (0),(100)) v(pct), 78 lateral (select * from tenk1 tablesample bernoulli (pct)) ss 79 group by pct; 80select pct, count(unique1) from 81 (values (0),(100)) v(pct), 82 lateral (select * from tenk1 tablesample system (pct)) ss 83 group by pct; 84 85-- errors 86SELECT id FROM test_tablesample TABLESAMPLE FOOBAR (1); 87 88SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (NULL); 89SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (NULL); 90 91SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (-1); 92SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (200); 93SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (-1); 94SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (200); 95 96SELECT id FROM test_tablesample_v1 TABLESAMPLE BERNOULLI (1); 97INSERT INTO test_tablesample_v1 VALUES(1); 98 99WITH query_select AS (SELECT * FROM test_tablesample) 100SELECT * FROM query_select TABLESAMPLE BERNOULLI (5.5) REPEATABLE (1); 101 102SELECT q.* FROM (SELECT * FROM test_tablesample) as q TABLESAMPLE BERNOULLI (5); 103 104-- check partitioned tables support tablesample 105create table parted_sample (a int) partition by list (a); 106create table parted_sample_1 partition of parted_sample for values in (1); 107create table parted_sample_2 partition of parted_sample for values in (2); 108explain (costs off) 109 select * from parted_sample tablesample bernoulli (100); 110drop table parted_sample, parted_sample_1, parted_sample_2; 111