84 lines
2.4 KiB
Plaintext
84 lines
2.4 KiB
Plaintext
|
CREATE EXTENSION tsm_system_rows;
|
||
|
|
CREATE TABLE test_tablesample (id int, name text);
|
||
|
|
INSERT INTO test_tablesample SELECT i, repeat(i::text, 1000)
|
||
|
|
FROM generate_series(0, 30) s(i);
|
||
|
|
ANALYZE test_tablesample;
|
||
|
|
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (0);
|
||
|
|
count
|
||
|
|
-------
|
||
|
|
0
|
||
|
|
(1 row)
|
||
|
|
|
||
|
|
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (1);
|
||
|
|
count
|
||
|
|
-------
|
||
|
|
1
|
||
|
|
(1 row)
|
||
|
|
|
||
|
|
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (10);
|
||
|
|
count
|
||
|
|
-------
|
||
|
|
10
|
||
|
|
(1 row)
|
||
|
|
|
||
|
|
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (100);
|
||
|
|
count
|
||
|
|
-------
|
||
|
|
31
|
||
|
|
(1 row)
|
||
|
|
|
||
|
|
-- bad parameters should get through planning, but not execution:
|
||
|
|
EXPLAIN (COSTS OFF)
|
||
|
|
SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
|
||
|
|
QUERY PLAN
|
||
|
|
----------------------------------------
|
||
|
|
Sample Scan on test_tablesample
|
||
|
|
Sampling: system_rows ('-1'::bigint)
|
||
|
|
(2 rows)
|
||
|
|
|
||
|
|
SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
|
||
|
|
ERROR: sample size must not be negative
|
||
|
|
-- fail, this method is not repeatable:
|
||
|
|
SELECT * FROM test_tablesample TABLESAMPLE system_rows (10) REPEATABLE (0);
|
||
|
|
ERROR: tablesample method system_rows does not support REPEATABLE
|
||
|
|
LINE 1: SELECT * FROM test_tablesample TABLESAMPLE system_rows (10) ...
|
||
|
|
^
|
||
|
|
-- but a join should be allowed:
|
||
|
|
EXPLAIN (COSTS OFF)
|
||
|
|
SELECT * FROM
|
||
|
|
(VALUES (0),(10),(100)) v(nrows),
|
||
|
|
LATERAL (SELECT count(*) FROM test_tablesample
|
||
|
|
TABLESAMPLE system_rows (nrows)) ss;
|
||
|
|
QUERY PLAN
|
||
|
|
----------------------------------------------------------
|
||
|
|
Nested Loop
|
||
|
|
-> Values Scan on "*VALUES*"
|
||
|
|
-> Aggregate
|
||
|
|
-> Sample Scan on test_tablesample
|
||
|
|
Sampling: system_rows ("*VALUES*".column1)
|
||
|
|
(5 rows)
|
||
|
|
|
||
|
|
SELECT * FROM
|
||
|
|
(VALUES (0),(10),(100)) v(nrows),
|
||
|
|
LATERAL (SELECT count(*) FROM test_tablesample
|
||
|
|
TABLESAMPLE system_rows (nrows)) ss;
|
||
|
|
nrows | count
|
||
|
|
-------+-------
|
||
|
|
0 | 0
|
||
|
|
10 | 10
|
||
|
|
100 | 31
|
||
|
|
(3 rows)
|
||
|
|
|
||
|
|
CREATE VIEW vv AS
|
||
|
|
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (20);
|
||
|
|
SELECT * FROM vv;
|
||
|
|
count
|
||
|
|
-------
|
||
|
|
20
|
||
|
|
(1 row)
|
||
|
|
|
||
|
|
DROP EXTENSION tsm_system_rows; -- fail, view depends on extension
|
||
|
|
ERROR: cannot drop extension tsm_system_rows because other objects depend on it
|
||
|
|
DETAIL: view vv depends on function system_rows(internal)
|
||
|
|
HINT: Use DROP ... CASCADE to drop the dependent objects too.
|