pgstatpack2.2的无索引、无toast段的表不统计的一个bug

文章也可以见我的blog:http://blog.osdba.net/?post=45
这几天使用了pgstatpack2.2，发现出来的报告不太对，有几个insert量很大的表，没有统计出来，检查函数pgstatspack_snap发现是其中的SQL有点问题：

INSERT INTO pgstatspack_tables

SELECT

  spid               as snapid,

  t.schemaname ||'.'|| t.relname          as table_name,

  t.seq_scan         as seq_scan,

  t.seq_tup_read     as seq_tup_read,

  t.idx_scan         as idx_scan,

  t.idx_tup_fetch    as idx_tup_fetch,

  t.n_tup_ins        as n_tup_ins,

  t.n_tup_upd        as n_tup_upd,

  t.n_tup_del        as n_tup_del,

  it.heap_blks_read  as heap_blks_read,

  it.heap_blks_hit   as heap_blks_hit,

  it.idx_blks_read   as idx_blks_read,

  it.idx_blks_hit    as idx_blks_hit,

  it.toast_blks_read as toast_blks_read,

  it.toast_blks_hit  as toast_blks_hit,

  it.tidx_blks_read  as tidx_blks_read,

  it.tidx_blks_hit   as tidx_blks_hit,

  pg_relation_size(t.relid)+pg_relation_size(s.relid) as tbl_size,

  sum(pg_relation_size(i.indexrelid)) as idx_size

FROM

  pg_statio_all_tables it,

  pg_stat_all_tables t

  JOIN pg_class c on t.relid=c.oid

  JOIN pg_stat_sys_tables s on c.reltoastrelid=s.relid

  JOIN pg_index i on i.indrelid=t.relid

WHERE

  (t.relid = it.relid)

GROUP BY

  t.schemaname,t.relname,t.seq_scan,t.seq_tup_read,t.idx_scan,t.idx_tup_fetch,t.n_tup_ins,t.n_tup_upd,t.n_tup_del,it.heap_blks_read,it.heap_blks_hit,it.i

dx_blks_read,it.idx_blks_hit,it.toast_blks_read,it.toast_blks_hit,it.tidx_blks_read,it.tidx_blks_hit,t.relid,s.relid

;

与pg_stat_sys_tables进行join的原因是为了统计表的toast段的大小，而与pg_index是为了计算表的索引大小，然后有些表是没有toast段的，有些表没有索引的，于是这样的表表就没有统计出来，

改成如下：
SELECT
  --spid               as snapid,
  t.schemaname ||'.'|| t.relname          as table_name,
  t.seq_scan         as seq_scan,
  t.seq_tup_read     as seq_tup_read,
  t.idx_scan         as idx_scan,
  t.idx_tup_fetch    as idx_tup_fetch,
  t.n_tup_ins        as n_tup_ins,
  t.n_tup_upd        as n_tup_upd,
  t.n_tup_del        as n_tup_del,
  it.heap_blks_read  as heap_blks_read,
  it.heap_blks_hit   as heap_blks_hit,
  it.idx_blks_read   as idx_blks_read,
  it.idx_blks_hit    as idx_blks_hit,
  it.toast_blks_read as toast_blks_read,
  it.toast_blks_hit  as toast_blks_hit,
  it.tidx_blks_read  as tidx_blks_read,
  it.tidx_blks_hit   as tidx_blks_hit,
  pg_relation_size(t.relid)+coalesce(pg_relation_size(s.relid),0) as tbl_size,
  sum(coalesce(pg_relation_size(i.indexrelid),0) ) as idx_size
FROM
  pg_statio_all_tables it,
  pg_stat_all_tables t
  JOIN pg_class c on t.relid=c.oid
  LEFT JOIN pg_stat_sys_tables s on c.reltoastrelid=s.relid
  LEFT JOIN pg_index i on i.indrelid=t.relid
WHERE
  t.relid = it.relid
  and t.relname='t'
GROUP BY  t.schemaname,t.relname,t.seq_scan,t.seq_tup_read,t.idx_scan,t.idx_tup_fetch,t.n_tup_ins,t.n_tup_upd,t.n_tup_del,it.heap_blks_read,it.heap_blks_hit,it.idx_blks_read,it.idx_blks_hit,it.toast_blks_read,it.toast_blks_hit,it.tidx_blks_read,it.tidx_blks_hit,t.relid,s.relid

这个我没有使用过。