PostgreSQL常用运维脚本

--查看索引膨胀

SELECT current_database(), nspname AS schemaname, tblname, idxname, pg_size_pretty(bs*(relpages)::bigint) AS real_size,

  pg_size_pretty(bs*(relpages-est_pages)::bigint) AS extra_size,

  100 * (relpages-est_pages)::float / relpages AS extra_ratio,

  fillfactor,

  pg_size_pretty(bs*(relpages-est_pages_ff)::bigint) AS bloat_size,

  100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio,

  is_na

  -- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO)

FROM (

  SELECT coalesce(1 +

       ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)

    ) AS est_pages,

    coalesce(1 +

       ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0

    ) AS est_pages_ff,

    bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na

    -- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)

  FROM (

    SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor,

      ( index_tuple_hdr_bm +

          maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN

            WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign

            ELSE index_tuple_hdr_bm%maxalign

          END

        + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN

            WHEN nulldatawidth = 0 THEN 0

            WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign

            ELSE nulldatawidth::integer%maxalign

          END

      )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na

      -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)

    FROM (

      SELECT

        i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid,

        current_setting('block_size')::numeric AS bs, fillfactor,

        CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)

          WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8

          ELSE 4

        END AS maxalign,

        /* per page header, fixed size: 20 for 7.X, 24 for others */

        24 AS pagehdr,

        /* per page btree opaque data */

        16 AS pageopqdata,

        /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */

        CASE WHEN max(coalesce(s.null_frac,0)) = 0

          THEN 2 -- IndexTupleData size

          ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)

        END AS index_tuple_hdr_bm,

        /* data len: we remove null values save space using it fractionnal part from stats */

        sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,

        max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na

      FROM pg_attribute AS a

        JOIN (

          SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam,

            indrelid, indexrelid, indkey::smallint[] AS attnum,

            coalesce(substring(

              array_to_string(idx.reloptions, ' ')

               from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor

          FROM pg_index

            JOIN pg_class idx ON idx.oid=pg_index.indexrelid

            JOIN pg_class tbl ON tbl.oid=pg_index.indrelid

            JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace

          WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0

        ) AS i ON a.attrelid = i.indexrelid

        JOIN pg_stats AS s ON s.schemaname = i.nspname

          AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl

          OR   (s.tablename = i.idxname AND s.attname = a.attname))-- stats from functionnal cols

        JOIN pg_type AS t ON a.atttypid = t.oid

      WHERE a.attnum > 0

      GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9

    ) AS s1

  ) AS s2

    JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'

) AS sub

-- WHERE NOT is_na

ORDER BY bloat_ratio desc;

 

--表年龄问题

 select a.oid::regclass,reltoastrelid,'vacuum freeze '|| b.nspname||'.'||a.relname||';',age(a.relfrozenxid),

a.relfilenode,a.reltoastrelid

from pg_class a

inner join pg_namespace b on a.relnamespace=b.oid

where a.relkind in ('r') and b.nspname not in ('pg_catalog','information_schema')

order by age(a.relfrozenxid) desc

limit 20;

 

--检查表膨胀

SELECT

  current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,

  ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,

  CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,

  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,

  CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::bigint || $$ bytes$$ END AS wastedsize,

  iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,

  ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,

  CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,

  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,

  CASE WHEN ipages < iotta THEN $$0 bytes$$ ELSE (bs*(ipages-iotta))::bigint || $$ bytes$$ END AS wastedisize,

  CASE WHEN relpages < otta THEN

    CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END

    ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)

      ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END

  END AS totalwastedbytes

FROM (

  SELECT

    nn.nspname AS schemaname,

    cc.relname AS tablename,

    COALESCE(cc.reltuples,0) AS reltuples,

    COALESCE(cc.relpages,0) AS relpages,

    COALESCE(bs,0) AS bs,

    COALESCE(CEIL((cc.reltuples*((datahdr+ma-

      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,

    COALESCE(c2.relname,$$?$$) AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,

    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols

  FROM

     pg_class cc

  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$

  LEFT JOIN

  (

    SELECT

      ma,bs,foo.nspname,foo.relname,

      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,

      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2

    FROM (

      SELECT

        ns.nspname, tbl.relname, hdr, ma, bs,

        SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,

        MAX(coalesce(null_frac,0)) AS maxfracsum,

        hdr+(

          SELECT 1+count(*)/8

          FROM pg_stats s2

          WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname

        ) AS nullhdr

      FROM pg_attribute att

      JOIN pg_class tbl ON att.attrelid = tbl.oid

      JOIN pg_namespace ns ON ns.oid = tbl.relnamespace

      LEFT JOIN pg_stats s ON s.schemaname=ns.nspname

      AND s.tablename = tbl.relname

      AND s.inherited=false

      AND s.attname=att.attname,

      (

        SELECT

          (SELECT current_setting($$block_size$$)::numeric) AS bs,

            CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#"[0-9]+.[0-9]+#"%$$ for $$#$$)

              IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,

          CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma

        FROM (SELECT version() AS v) AS foo

      ) AS constants

      WHERE att.attnum > 0 AND tbl.relkind=$$r$$

      GROUP BY 1,2,3,4,5

    ) AS foo

  ) AS rs

  ON cc.relname = rs.relname AND nn.nspname = rs.nspname

  LEFT JOIN pg_index i ON indrelid = cc.oid

  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid

) AS sml

order by wastedbytes desc limit 10;