How to use pg_stat_statements extension

Posted on In

pg_stat_statements 不能像 pageinspect 那样只靠 CREATE EXTENSION 就用，原因在于它们在 PostgreSQL 内核中的“级别”完全不同。

pg_stat_statements 和 pageinspect 的定位完全不同：它是一个 全局统计模块
它需要：

  1. 拦截每一条 SQL 的执行
    • 使用 executor hooks
    • 在 query 开始 / 结束时采样
  2. shared memory 中维护全局 hash 表
    • 跨 backend 共享
    • 保存长期累计统计
  3. 在 backend 启动阶段完成初始化
    • 分配 shared memory
    • 注册 hooks

这些事情 只能在 postmaster 启动时完成。

启动步骤

  1. 修改 postgresql.conf
    确认conf位置 
    1
    2
    3
    4
    5
    gujinfei=# show config_file;
                config_file
    ---------------------------------------
    /home/gujinfei/pgdata/postgresql.conf
    (1 row)
    1
    shared_preload_libraries = 'pg_stat_statements'
  2. reboot database 
    1
    pg_ctl restart
  3. 进入sql 
    1
    2
    SHOW shared_preload_libraries;
    CREATE EXTENSION pg_stat_statements;
  4. Test 
    1
    2
    3
    4
    5
    6
    7
    drop table if exists t;
    create table t(a text, b text, c int);
    SELECT pg_stat_statements_reset() IS NOT NULL AS t;
    explain(costs off, verbose) select count(*) from t group by a;
    explain(costs off, verbose) select count(*) from t group by b;
    explain(costs off, verbose) select count(*) from t group by c;
    SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
    result 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    gujinfei=# drop table if exists t;
    DROP TABLE
    gujinfei=# create table t(a text, b text, c int);
    CREATE TABLE
    gujinfei=# SELECT pg_stat_statements_reset() IS NOT NULL AS t;
    t
    ---
    t
    (1 row)

    gujinfei=# explain(costs off, verbose) select count(*) from t group by a;
                QUERY PLAN
    ---------------------------------------
    HashAggregate
    Output: count(*), a
    Group Key: t.a
    ->  Seq Scan on public.t
            Output: a, b, c
    Query Identifier: 8735661759096201363
    (6 rows)

    gujinfei=# explain(costs off, verbose) select count(*) from t group by b;
                QUERY PLAN
    ---------------------------------------
    HashAggregate
    Output: count(*), b
    Group Key: t.b
    ->  Seq Scan on public.t
            Output: a, b, c
    Query Identifier: 8735661759096201363
    (6 rows)

    gujinfei=# explain(costs off, verbose) select count(*) from t group by c;
                QUERY PLAN
    ---------------------------------------
    HashAggregate
    Output: count(*), c
    Group Key: t.c
    ->  Seq Scan on public.t
            Output: a, b, c
    Query Identifier: 9219356609107396553
    (6 rows)

    gujinfei=# SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
    calls | rows |                             query
    -------+------+---------------------------------------------------------------
        1 |    1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
        2 |    0 | explain(costs off, verbose) select count(*) from t group by a
        1 |    0 | explain(costs off, verbose) select count(*) from t group by c
    (3 rows)

    gujinfei=#