diff options
Diffstat (limited to 'contrib/pg_stat_statements/expected/entry_timestamp.out')
| -rw-r--r-- | contrib/pg_stat_statements/expected/entry_timestamp.out | 159 |
1 files changed, 159 insertions, 0 deletions
diff --git a/contrib/pg_stat_statements/expected/entry_timestamp.out b/contrib/pg_stat_statements/expected/entry_timestamp.out new file mode 100644 index 00000000000..a10c4be6bac --- /dev/null +++ b/contrib/pg_stat_statements/expected/entry_timestamp.out @@ -0,0 +1,159 @@ +-- +-- statement timestamps +-- +-- planning time is needed during tests +SET pg_stat_statements.track_planning = TRUE; +SELECT 1 AS "STMTTS1"; + STMTTS1 +--------- + 1 +(1 row) + +SELECT now() AS ref_ts \gset +SELECT 1,2 AS "STMTTS2"; + ?column? | STMTTS2 +----------+--------- + 1 | 2 +(1 row) + +SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements +WHERE query LIKE '%STMTTS%' +GROUP BY stats_since >= :'ref_ts' +ORDER BY stats_since >= :'ref_ts'; + ?column? | count +----------+------- + f | 1 + t | 1 +(2 rows) + +SELECT now() AS ref_ts \gset +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_plan_time + max_plan_time = 0 + ) as minmax_plan_zero, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_stats_since_after_ref, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref +-------+------------------+------------------+------------------------------+----------------------- + 2 | 0 | 0 | 0 | 0 +(1 row) + +-- Perform single min/max reset +SELECT pg_stat_statements_reset(0, 0, queryid, true) AS minmax_reset_ts +FROM pg_stat_statements +WHERE query LIKE '%STMTTS1%' \gset +-- check +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_plan_time + max_plan_time = 0 + ) as minmax_plan_zero, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_stats_since_after_ref, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref +-------+------------------+------------------+------------------------------+----------------------- + 2 | 1 | 1 | 1 | 0 +(1 row) + +-- check minmax reset timestamps +SELECT +query, minmax_stats_since = :'minmax_reset_ts' AS reset_ts_match +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%' +ORDER BY query COLLATE "C"; + query | reset_ts_match +---------------------------+---------------- + SELECT $1 AS "STMTTS1" | t + SELECT $1,$2 AS "STMTTS2" | f +(2 rows) + +-- check that minmax reset does not set stats_reset +SELECT +stats_reset = :'minmax_reset_ts' AS stats_reset_ts_match +FROM pg_stat_statements_info; + stats_reset_ts_match +---------------------- + f +(1 row) + +-- Perform common min/max reset +SELECT pg_stat_statements_reset(0, 0, 0, true) AS minmax_reset_ts \gset +-- check again +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_plan_time + max_plan_time = 0 + ) as minmax_plan_zero, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_ts_after_ref, + count(*) FILTER ( + WHERE minmax_stats_since = :'minmax_reset_ts' + ) as minmax_ts_match, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + total | minmax_plan_zero | minmax_exec_zero | minmax_ts_after_ref | minmax_ts_match | stats_since_after_ref +-------+------------------+------------------+---------------------+-----------------+----------------------- + 2 | 2 | 2 | 2 | 2 | 0 +(1 row) + +-- Execute first query once more to check stats update +SELECT 1 AS "STMTTS1"; + STMTTS1 +--------- + 1 +(1 row) + +-- check +-- we don't check planing times here to be independent of +-- plan caching approach +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_ts_after_ref, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + total | minmax_exec_zero | minmax_ts_after_ref | stats_since_after_ref +-------+------------------+---------------------+----------------------- + 2 | 1 | 2 | 0 +(1 row) + +-- Cleanup +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + |