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 track_counts; -- must be on
14 -- ensure that both seqscan and indexscan plans are allowed
15 SET enable_seqscan TO on;
16 SET enable_indexscan TO on;
17 -- for the moment, we don't want index-only scans here
18 SET enable_indexonlyscan TO off;
20 CREATE TABLE prevstats AS
21 SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
22 (b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
23 (b.idx_blks_read + b.idx_blks_hit) AS idx_blks,
24 pg_stat_get_snapshot_timestamp() as snap_ts
25 FROM pg_catalog.pg_stat_user_tables AS t,
26 pg_catalog.pg_statio_user_tables AS b
27 WHERE t.relname='tenk2' AND b.relname='tenk2';
28 -- function to wait for counters to advance
29 create function wait_for_stats() returns void as $$
31 start_time timestamptz := clock_timestamp();
37 -- we don't want to wait forever; loop will exit after 30 seconds
38 for i in 1 .. 300 loop
40 -- With parallel query, the seqscan and indexscan on tenk2 might be done
41 -- in parallel worker processes, which will send their stats counters
42 -- asynchronously to what our own session does. So we must check for
43 -- those counts to be registered separately from the update counts.
45 -- check to see if seqscan has been sensed
46 SELECT (st.seq_scan >= pr.seq_scan + 1) INTO updated1
47 FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
48 WHERE st.relname='tenk2' AND cl.relname='tenk2';
50 -- check to see if indexscan has been sensed
51 SELECT (st.idx_scan >= pr.idx_scan + 1) INTO updated2
52 FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
53 WHERE st.relname='tenk2' AND cl.relname='tenk2';
55 -- check to see if all updates have been sensed
56 SELECT (n_tup_ins > 0) INTO updated3
57 FROM pg_stat_user_tables WHERE relname='trunc_stats_test4';
59 -- We must also check explicitly that pg_stat_get_snapshot_timestamp has
60 -- advanced, because that comes from the global stats file which might
61 -- be older than the per-DB stats file we got the other values from.
62 SELECT (pr.snap_ts < pg_stat_get_snapshot_timestamp()) INTO updated4
65 exit when updated1 and updated2 and updated3 and updated4;
68 perform pg_sleep_for('100 milliseconds');
70 -- reset stats snapshot so we can test again
71 perform pg_stat_clear_snapshot();
75 -- report time waited in postmaster log (where it won't change test output)
76 raise log 'wait_for_stats delayed % seconds',
77 extract(epoch from clock_timestamp() - start_time);
80 -- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
81 CREATE TABLE trunc_stats_test(id serial);
82 CREATE TABLE trunc_stats_test1(id serial);
83 CREATE TABLE trunc_stats_test2(id serial);
84 CREATE TABLE trunc_stats_test3(id serial);
85 CREATE TABLE trunc_stats_test4(id serial);
86 -- check that n_live_tup is reset to 0 after truncate
87 INSERT INTO trunc_stats_test DEFAULT VALUES;
88 INSERT INTO trunc_stats_test DEFAULT VALUES;
89 INSERT INTO trunc_stats_test DEFAULT VALUES;
90 TRUNCATE trunc_stats_test;
91 -- test involving a truncate in a transaction; 4 ins but only 1 live
92 INSERT INTO trunc_stats_test1 DEFAULT VALUES;
93 INSERT INTO trunc_stats_test1 DEFAULT VALUES;
94 INSERT INTO trunc_stats_test1 DEFAULT VALUES;
95 UPDATE trunc_stats_test1 SET id = id + 10 WHERE id IN (1, 2);
96 DELETE FROM trunc_stats_test1 WHERE id = 3;
98 UPDATE trunc_stats_test1 SET id = id + 100;
99 TRUNCATE trunc_stats_test1;
100 INSERT INTO trunc_stats_test1 DEFAULT VALUES;
102 -- use a savepoint: 1 insert, 1 live
104 INSERT INTO trunc_stats_test2 DEFAULT VALUES;
105 INSERT INTO trunc_stats_test2 DEFAULT VALUES;
107 INSERT INTO trunc_stats_test2 DEFAULT VALUES;
108 TRUNCATE trunc_stats_test2;
109 INSERT INTO trunc_stats_test2 DEFAULT VALUES;
110 RELEASE SAVEPOINT p1;
112 -- rollback a savepoint: this should count 4 inserts and have 2
113 -- live tuples after commit (and 2 dead ones due to aborted subxact)
115 INSERT INTO trunc_stats_test3 DEFAULT VALUES;
116 INSERT INTO trunc_stats_test3 DEFAULT VALUES;
118 INSERT INTO trunc_stats_test3 DEFAULT VALUES;
119 INSERT INTO trunc_stats_test3 DEFAULT VALUES;
120 TRUNCATE trunc_stats_test3;
121 INSERT INTO trunc_stats_test3 DEFAULT VALUES;
122 ROLLBACK TO SAVEPOINT p1;
124 -- rollback a truncate: this should count 2 inserts and produce 2 dead tuples
126 INSERT INTO trunc_stats_test4 DEFAULT VALUES;
127 INSERT INTO trunc_stats_test4 DEFAULT VALUES;
128 TRUNCATE trunc_stats_test4;
129 INSERT INTO trunc_stats_test4 DEFAULT VALUES;
132 SELECT count(*) FROM tenk2;
139 SELECT count(*) FROM tenk2 WHERE unique1 = 1;
145 -- We can't just call wait_for_stats() at this point, because we only
146 -- transmit stats when the session goes idle, and we probably didn't
147 -- transmit the last couple of counts yet thanks to the rate-limiting logic
148 -- in pgstat_report_stat(). But instead of waiting for the rate limiter's
149 -- timeout to elapse, let's just start a new session. The old one will
150 -- then send its stats before dying.
152 -- wait for stats collector to update
153 SELECT wait_for_stats();
160 SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
161 FROM pg_stat_user_tables
162 WHERE relname like 'trunc_stats_test%' order by relname;
163 relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
164 -------------------+-----------+-----------+-----------+------------+------------
165 trunc_stats_test | 3 | 0 | 0 | 0 | 0
166 trunc_stats_test1 | 4 | 2 | 1 | 1 | 0
167 trunc_stats_test2 | 1 | 0 | 0 | 1 | 0
168 trunc_stats_test3 | 4 | 0 | 0 | 2 | 2
169 trunc_stats_test4 | 2 | 0 | 0 | 0 | 2
172 SELECT st.seq_scan >= pr.seq_scan + 1,
173 st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
174 st.idx_scan >= pr.idx_scan + 1,
175 st.idx_tup_fetch >= pr.idx_tup_fetch + 1
176 FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
177 WHERE st.relname='tenk2' AND cl.relname='tenk2';
178 ?column? | ?column? | ?column? | ?column?
179 ----------+----------+----------+----------
183 SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
184 st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
185 FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
186 WHERE st.relname='tenk2' AND cl.relname='tenk2';
188 ----------+----------
192 SELECT pr.snap_ts < pg_stat_get_snapshot_timestamp() as snapshot_newer
193 FROM prevstats AS pr;
199 DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
200 DROP TABLE prevstats;