• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    postgresql查看表和索引的情况,判断是否膨胀的操作

    索引膨胀的几个来源:

    1 大量删除发生后,导致索引页面稀疏,降低了索引使用效率。

    2 PostgresQL 9.0之前的版本,vacuum full 会同样导致索引页面稀疏。

    3 长时间运行的事务,禁止vacuum对表的清理工作,因而导致页面稀疏状态一直保持。

    查看重复索引

    SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
      (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
      (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
    FROM (
     SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
               COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
     FROM pg_index) sub
    GROUP BY KEY HAVING COUNT(*)>1
    ORDER BY SUM(pg_relation_size(idx)) DESC;

    表的大小和表中索引个数

    SELECT
     t.tablename,
     indexname,
     c.reltuples AS num_rows,
     pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
     pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
     CASE WHEN indisunique THEN 'Y'
      ELSE 'N'
     END AS UNIQUE,
     idx_scan AS number_of_scans,
     idx_tup_read AS tuples_read,
     idx_tup_fetch AS tuples_fetched
    FROM pg_tables t
    LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
    LEFT OUTER JOIN
     ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
       JOIN pg_class c ON c.oid = x.indrelid
       JOIN pg_class ipg ON ipg.oid = x.indexrelid
       JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
     AS foo
     ON t.tablename = foo.ctablename
    WHERE t.schemaname='public'
    ORDER BY 1,2;

    获取每个表的行数,索引和一些关于这些索引的信息(比较详细)

    SELECT
     pg_class.relname,
     pg_size_pretty(pg_class.reltuples::BIGINT) AS rows_in_bytes,
     pg_class.reltuples AS num_rows,
     COUNT(indexname) AS number_of_indexes,
     CASE WHEN x.is_unique = 1 THEN 'Y'
      ELSE 'N'
     END AS UNIQUE,
     SUM(CASE WHEN number_of_columns = 1 THEN 1
        ELSE 0
       END) AS single_column,
     SUM(CASE WHEN number_of_columns IS NULL THEN 0
        WHEN number_of_columns = 1 THEN 0
        ELSE 1
       END) AS multi_column
    FROM pg_namespace 
    LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
    LEFT OUTER JOIN
      (SELECT indrelid,
       MAX(CAST(indisunique AS INTEGER)) AS is_unique
      FROM pg_index
      GROUP BY indrelid) x
      ON pg_class.oid = x.indrelid
    LEFT OUTER JOIN
     ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
       JOIN pg_class c ON c.oid = x.indrelid
       JOIN pg_class ipg ON ipg.oid = x.indexrelid )
     AS foo
     ON pg_class.relname = foo.ctablename
    WHERE 
      pg_namespace.nspname='public'
    AND pg_class.relkind = 'r'
    GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
    ORDER BY 2;

    补充:postgresql查看表膨胀

    查看表膨胀(对所有表产进行膨胀率排序)

    SQL文如下:

    SELECT
     schemaname||'.'||relname as table_name,
     pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
     n_dead_tup,
     n_live_tup,
     round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
    FROM
     pg_stat_all_tables
    WHERE
     n_dead_tup >= 1000
    ORDER BY dead_tup_ratio DESC
    LIMIT 10;
    

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。如有错误或未考虑完全的地方,望不吝赐教。

    您可能感兴趣的文章:
    • PostgreSQL之INDEX 索引详解
    • PostgreSql 重建索引的操作
    • PostgreSQL模糊匹配走索引的操作
    • PostgreSQL的B-tree索引用法详解
    • postgresql通过索引优化查询速度操作
    • postgresql 索引之 hash的使用详解
    上一篇:PostgreSQL的外部数据封装器fdw用法
    下一篇:PostgreSQL表膨胀监控案例(精确计算)
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    postgresql查看表和索引的情况,判断是否膨胀的操作 postgresql,查,看表,和,索引,