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

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;

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;