Advertisement
Nutria

Postgres btree bloat query

Mar 12th, 2018
183
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 4.25 KB | None | 0 0
  1. SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::BIGINT AS real_size,
  2.   bs*(relpages-est_pages)::BIGINT AS extra_size,
  3.   100 * (relpages-est_pages)::FLOAT / relpages AS extra_ratio,
  4.   fillfactor, bs*(relpages-est_pages_ff) AS bloat_size,
  5.   100 * (relpages-est_pages_ff)::FLOAT / relpages AS bloat_ratio,
  6.   is_na
  7.   -- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO)
  8. FROM (
  9.   SELECT COALESCE(1 +
  10.        CEIL(reltuples/FLOOR((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::FLOAT)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
  11.     ) AS est_pages,
  12.     COALESCE(1 +
  13.        CEIL(reltuples/FLOOR((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::FLOAT))), 0
  14.     ) AS est_pages_ff,
  15.     bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na
  16.     -- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
  17.   FROM (
  18.     SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor,
  19.       ( index_tuple_hdr_bm +
  20.           maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
  21.             WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
  22.             ELSE index_tuple_hdr_bm%maxalign
  23.           END
  24.         + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
  25.             WHEN nulldatawidth = 0 THEN 0
  26.             WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
  27.             ELSE nulldatawidth::integer%maxalign
  28.           END
  29.       )::NUMERIC AS nulldatahdrwidth, pagehdr, pageopqdata, is_na
  30.       -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
  31.     FROM (
  32.       SELECT
  33.         i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid,
  34.         current_setting('block_size')::NUMERIC AS bs, fillfactor,
  35.         CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
  36.           WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
  37.           ELSE 4
  38.         END AS maxalign,
  39.         /* per page header, fixed size: 20 for 7.X, 24 for others */
  40.         24 AS pagehdr,
  41.         /* per page btree opaque data */
  42.         16 AS pageopqdata,
  43.         /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
  44.         CASE WHEN MAX(COALESCE(s.null_frac,0)) = 0
  45.           THEN 2 -- IndexTupleData size
  46.           ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
  47.         END AS index_tuple_hdr_bm,
  48.         /* data len: we remove null values save space using it fractionnal part from stats */
  49.         SUM( (1-COALESCE(s.null_frac, 0)) * COALESCE(s.avg_width, 1024)) AS nulldatawidth,
  50.         MAX( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
  51.       FROM pg_attribute AS a
  52.         JOIN (
  53.           SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam,
  54.             indrelid, indexrelid, indkey::SMALLINT[] AS attnum,
  55.             COALESCE(SUBSTRING(
  56.               array_to_string(idx.reloptions, ' ')
  57.                FROM 'fillfactor=([0-9]+)')::SMALLINT, 90) AS fillfactor
  58.           FROM pg_index
  59.             JOIN pg_class idx ON idx.oid=pg_index.indexrelid
  60.             JOIN pg_class tbl ON tbl.oid=pg_index.indrelid
  61.             JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
  62.           WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0
  63.         ) AS i ON a.attrelid = i.indexrelid
  64.         JOIN pg_stats AS s ON s.schemaname = i.nspname
  65.           AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl
  66.           OR   (s.tablename = i.idxname AND s.attname = a.attname))-- stats from functionnal cols
  67.         JOIN pg_type AS t ON a.atttypid = t.oid
  68.       WHERE a.attnum > 0
  69.       GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
  70.     ) AS s1
  71.   ) AS s2
  72.     JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
  73. ) AS sub
  74. -- WHERE NOT is_na
  75. ORDER BY 2,3,4;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement