From: Mladen Gogala <mladen.gogala@vmsinfo.com>
To: Dave Crooke <dcrooke@gmail.com>
Cc: Plugge, Joe R. <JRPlugge@west.com>
Cc: John W Strange <john.w.strange@jpmchase.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Index Bloat - how to tell?
Date: Thu, 16 Dec 2010 14:27:08 -0500
Message-ID: <4D0A680C.7040105@vmsinfo.com> (raw)
In-Reply-To: <AANLkTim9cSXi9iswOUvKM4iifVUMPzURWxAaXt_OHwRW@mail.gmail.com>
References: <EF37296944B47C40ADDCCB7BFD6289FE046CDF1BCD@EMASC201VS01.exchad.jpmchase.net>
<BD69807DAE0CE44CA00A8338D0FDD08302E9548A03@oma00cexmbx03.corp.westworlds.com>
<AANLkTim9cSXi9iswOUvKM4iifVUMPzURWxAaXt_OHwRW@mail.gmail.com>
Dave Crooke wrote:
> There is a plugin called pgstattuple which can be quite informative
> .... however, it actually does a full scan of the table / index files,
> which may be a bit invasive depending on your environment and load.
>
> http://www.postgresql.org/docs/current/static/pgstattuple.html
>
> It's in the contrib (at least for 8.4), and so you have to import its
> functions into your schema using the script in the contrib directory.
>
> Cheers
> Dave
I tried it with one of my databases:
testtrack=# select * from pgstatindex('public.defects_pkey');
version | tree_level | index_size | root_block_no | internal_pages |
leaf_pages | empty_pages | deleted_pages | avg_leaf_density |
leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
-
2 | 1 | 827392 | 3 | 0
| 100 | 0 | 0 | 70.12
| 22
(1 row)
What is "leaf_fragmentation"? How is it defined? I wasn't able to find
out any definition of that number. How is it calculated. I verified that
running reindex makes it 0:
testtrack=# reindex table public.defects;
REINDEX
testtrack=# select * from pgstatindex('public.defects_pkey');
version | tree_level | index_size | root_block_no | internal_pages |
leaf_pages | empty_pages | deleted_pages | avg_leaf_density |
leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
-
2 | 1 | 647168 | 3 | 0
| 78 | 0 | 0 | 89.67
| 0
(1 row)
--
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mladen.gogala@vmsinfo.com, dcrooke@gmail.com, JRPlugge@west.com, john.w.strange@jpmchase.com
Subject: Re: Index Bloat - how to tell?
In-Reply-To: <4D0A680C.7040105@vmsinfo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox