Re: Index Bloat - how to tell?

Can you explain this query a bit? It isn't at all clear to me.


Plugge, Joe R. wrote:
> I have used this in the past ... run this against the database that you want to inspect.
>
>
> 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
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of John W
Strange
> Sent: Tuesday, December 14, 2010 8:48 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Index Bloat - how to tell?
>
> How can you tell when your indexes are starting to get bloated and when you need to rebuild them.  I haven't seen a
quickway to tell and not sure if it's being tracked. 
>
>
>
> _______________________________________________________________________________________________
>
> | John W. Strange | Investment Bank | Global Commodities Technology
>
> | J.P. Morgan | 700 Louisiana, 11th Floor | T: 713-236-4122 | C: 281-744-6476 | F: 713 236-3333
>
> | john.w.strange@jpmchase.com | jpmorgan.com
>
>
>
> This communication is for informational purposes only. It is not
> intended as an offer or solicitation for the purchase or sale of
> any financial instrument or as an official confirmation of any
> transaction. All market prices, data and other information are not
> warranted as to completeness or accuracy and are subject to change
> without notice. Any comments or statements made herein do not
> necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
> and affiliates.
>
>
>
> This transmission may contain information that is privileged,
> confidential, legally privileged, and/or exempt from disclosure
> under applicable law. If you are not the intended recipient, you
> are hereby notified that any disclosure, copying, distribution, or
> use of the information contained herein (including any reliance
> thereon) is STRICTLY PROHIBITED. Although this transmission and any
> attachments are believed to be free of any virus or other defect
> that might affect any computer system into which it is received and
> opened, it is the responsibility of the recipient to ensure that it
> is virus free and no responsibility is accepted by JPMorgan Chase &
> Co., its subsidiaries and affiliates, as applicable, for any loss
> or damage arising in any way from its use. If you received this
> transmission in error, please immediately contact the sender and
> destroy the material in its entirety, whether in electronic or hard
> copy format. Thank you.
>
>
>
> Please refer to http://www.jpmorgan.com/pages/disclosures for
> disclosures relating to European legal entities.
>
>


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions