2
-- Test Statistics Collector
4
-- Must be run after tenk2 has been created (by create_table),
5
-- populated (by create_misc) and indexed (by create_index).
7
-- conditio sine qua non
8
SHOW stats_start_collector; -- must be on
10
-----------------------
15
CREATE TEMP TABLE prevstats AS
16
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
17
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
18
(b.idx_blks_read + b.idx_blks_hit) AS idx_blks
19
FROM pg_catalog.pg_stat_user_tables AS t,
20
pg_catalog.pg_statio_user_tables AS b
21
WHERE t.relname='tenk2' AND b.relname='tenk2';
23
SET stats_block_level = on;
24
SET stats_row_level = on;
26
CREATE FUNCTION sleep(interval) RETURNS integer AS '
30
endtime := timeofday()::timestamp + $1;
31
WHILE timeofday()::timestamp < endtime LOOP
37
SELECT count(*) FROM tenk2;
43
SELECT count(*) FROM tenk2 WHERE unique1 = 1;
49
-- let stats collector catch up
50
SELECT sleep('0:0:2'::interval);
57
SELECT st.seq_scan >= pr.seq_scan + 1,
58
st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
59
st.idx_scan >= pr.idx_scan + 1,
60
st.idx_tup_fetch >= pr.idx_tup_fetch + 1
61
FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
62
WHERE st.relname='tenk2' AND cl.relname='tenk2';
63
?column? | ?column? | ?column? | ?column?
64
----------+----------+----------+----------
68
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
69
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
70
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
71
WHERE st.relname='tenk2' AND cl.relname='tenk2';
78
DROP FUNCTION sleep(interval);