Re: : Performance Improvement Strategy

Venkat Balaji <venkat.balaji@xxxxxxxx> · Wed, 21 Sep 2011 11:29:23 +0530

Can you please help me understand what "blkno" column refers to ?

Thanks
Venkat

On Wed, Sep 21, 2011 at 11:08 AM, Venkat Balaji <venkat.balaji@xxxxxxxx> wrote:

Thank Everyone for your inputs !
Mark,

We are using 9.0, so, i should be able to make use of this "freespacemap" contrib module and would get back to you with the results.

I was using below query (which i got it by googling)..

But, was not sure, if its picking up the correct information. I want to avoid mis-prediction cost after whole production has been scheduled for downtime for maintenance.

SELECT 
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/ 
  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat, 
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes, 

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

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

  SELECT 
    schemaname, tablename, cc.reltuples, cc.relpages, bs, 
    CEIL((cc.reltuples*((datahdr+ma- 
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) 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 ( 
    SELECT 
      ma,bs,schemaname,tablename, 
      (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 
        schemaname, tablename, hdr, ma, bs, 
        SUM((1-null_frac)*avg_width) AS datawidth, 

        MAX(null_frac) AS maxfracsum, 
        hdr+( 
          SELECT 1+count(*)/8 
          FROM pg_stats s2 
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename 
        ) AS nullhdr 
      FROM pg_stats s, ( 
        SELECT 
          (SELECT current_setting('block_size')::numeric) AS bs, 
          CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr, 

          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma 
        FROM (SELECT version() AS v) AS foo 
      ) AS constants 
      GROUP BY 1,2,3,4,5 
    ) AS foo 
  ) AS rs 
  JOIN pg_class cc ON cc.relname = rs.tablename 
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema' 

  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 

Thanks
Venkat

On Wed, Sep 21, 2011 at 3:40 AM, Mark Kirkwood <mark.kirkwood@xxxxxxxxxxxxxxx> wrote:

On 21/09/11 10:05, Mark Kirkwood wrote:

...then using the freespacemap contrib module should give very accurate results:

Sorry, should have said - for 8.4 and later!

-- 

Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-performance