Re: Index Bloat - how to tell? - Mailing list pgsql-performance
From | Mladen Gogala |
---|---|
Subject | Re: Index Bloat - how to tell? |
Date | |
Msg-id | 4D0799A1.7070700@vmsinfo.com Whole thread Raw |
In response to | Re: Index Bloat - how to tell? ("Plugge, Joe R." <JRPlugge@west.com>) |
List | pgsql-performance |
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
pgsql-performance by date: