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(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of John W Strange
> Sent: Tuesday, December 14, 2010 8:48 AM
> To: pgsql-performance(at)postgresql(dot)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 quick way 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(dot)w(dot)strange(at)jpmchase(dot)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