pg_stat_statements 在有效采集窗口内比较查询代价

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE TABLE pgss_demo (id integer);
INSERT INTO pgss_demo SELECT generate_series(1,1000);
CREATE TEMP TABLE pgss_key (
  userid oid NOT NULL, dbid oid NOT NULL,
  toplevel boolean NOT NULL, queryid bigint NOT NULL CHECK (queryid <> 0),
  PRIMARY KEY (userid,dbid,toplevel,queryid)
);
DO $$
DECLARE first_plan json; second_plan json; first_id bigint; second_id bigint;
BEGIN
  IF current_setting('compute_query_id') NOT IN ('auto','on')
     OR current_setting('pg_stat_statements.track') <> 'top' THEN
    RAISE EXCEPTION 'requires built-in query identifiers and top-level tracking';
  END IF;
  EXECUTE 'EXPLAIN (VERBOSE, FORMAT JSON) SELECT sum(id) FROM pgss_demo WHERE id < 100'
    INTO STRICT first_plan;
  EXECUTE 'EXPLAIN (VERBOSE, FORMAT JSON) SELECT sum(id) FROM pgss_demo WHERE id < 500'
    INTO STRICT second_plan;
  first_id := (first_plan->0->>'Query Identifier')::bigint;
  second_id := (second_plan->0->>'Query Identifier')::bigint;
  IF first_id IS NULL OR first_id = 0 OR second_id IS DISTINCT FROM first_id THEN
    RAISE EXCEPTION 'expected matching nonzero query identifiers';
  END IF;
  INSERT INTO pgss_key
  SELECT (SELECT oid FROM pg_roles WHERE rolname=current_user),
         (SELECT oid FROM pg_database WHERE datname=current_database()),
         true,first_id;
END $$;
CREATE TEMP VIEW pgss_target AS
SELECT s.* FROM pg_stat_statements AS s JOIN pgss_key AS k
USING (userid,dbid,toplevel,queryid);
CREATE TEMP TABLE pgss_window AS
SELECT dealloc,stats_reset FROM pg_stat_statements_info;
SELECT sum(id) FROM pgss_demo WHERE id < 100;
CREATE TEMP TABLE pgss_first AS
SELECT userid,dbid,toplevel,queryid,calls,stats_since FROM pgss_target;
SELECT sum(id) FROM pgss_demo WHERE id < 500;
SELECT userid,dbid,toplevel,queryid,query,calls,rows,
       round(total_exec_time::numeric,3) AS total_ms,
       round(mean_exec_time::numeric,3) AS mean_ms,stats_since
FROM pgss_target;
DO $$ BEGIN
  IF (SELECT count(*) FROM pgss_first) <> 1
     OR (SELECT count(*) FROM pgss_target) <> 1
     OR (SELECT count(*) FROM pgss_window) <> 1 THEN
    RAISE EXCEPTION 'expected one identity-bound entry and collection baseline';
  END IF;
  IF NOT EXISTS (
      SELECT 1 FROM pgss_first AS a JOIN pgss_target AS b
      USING (userid,dbid,toplevel,queryid)
      CROSS JOIN pgss_window AS w CROSS JOIN pg_stat_statements_info AS i
      WHERE a.calls=1 AND b.calls=2 AND b.calls-a.calls=1
        AND b.stats_since=a.stats_since
        AND i.dealloc=w.dealloc AND i.stats_reset=w.stats_reset) THEN
    RAISE EXCEPTION 'expected calls 1 then 2 in one unchanged collection window';
  END IF;
END $$;
SELECT userid,dbid,toplevel,queryid,calls,
       round(total_exec_time::numeric,3) AS total_ms,
       round(mean_exec_time::numeric,3) AS mean_ms
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
SELECT userid,dbid,toplevel,queryid,calls,
       round(mean_exec_time::numeric,3) AS mean_ms
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
SELECT dealloc,stats_reset FROM pg_stat_statements_info;
SELECT round((sum(shared_blks_read)::numeric *
              current_setting('block_size')::numeric)/1073741824,6) AS requested_read_gib
FROM pg_stat_statements;
DROP VIEW pgss_target;
DROP TABLE pgss_first,pgss_window,pgss_key;
DROP TABLE pgss_demo;
SELECT 'pgss fixture completed' AS completion;