Re: : Performance Improvement Strategy

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(dot)balaji(at)verse(dot)in>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(dot)kirkwood(at)catalyst(dot)net(dot)nz> 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(at)postgresql(dot)**
>> org <pgsql-performance(at)postgresql(dot)org>)
>> To make changes to your subscription:
>> http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-performance>
>>
>
>