pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding bloated indexes?
3+ messages / 3 participants
[nested] [flat]
* Finding bloated indexes?
@ 2007-04-13 20:01 Dan Harris <fbsd@drivefaster.net>
2007-04-13 21:07 ` Re: Finding bloated indexes? Vivek Khera <vivek@khera.org>
2007-04-14 08:27 ` Re: Finding bloated indexes? Simon Riggs <simon@2ndquadrant.com>
0 siblings, 2 replies; 3+ messages in thread
From: Dan Harris @ 2007-04-13 20:01 UTC (permalink / raw)
To: pgsql-performance
Is there a pg_stat_* table or the like that will show how bloated an index is?
I am trying to squeeze some disk space and want to track down where the worst
offenders are before performing a global REINDEX on all tables, as the database
is rougly 400GB on disk and this takes a very long time to run.
I have been able to do this with tables, using a helpful view posted to this
list a few months back, but I'm not sure if I can get the same results on indexes.
Thanks
-Dan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Finding bloated indexes?
2007-04-13 20:01 Finding bloated indexes? Dan Harris <fbsd@drivefaster.net>
@ 2007-04-13 21:07 ` Vivek Khera <vivek@khera.org>
1 sibling, 0 replies; 3+ messages in thread
From: Vivek Khera @ 2007-04-13 21:07 UTC (permalink / raw)
To: pgsql-performance
On Apr 13, 2007, at 4:01 PM, Dan Harris wrote:
> Is there a pg_stat_* table or the like that will show how bloated
> an index is? I am trying to squeeze some disk space and want to
> track down where the worst offenders are before performing a global
> REINDEX on all tables, as the database is rougly 400GB on disk and
> this takes a very long time to run.
I find this as a helpful guide:
select relname,relkind,relpages from pg_class where relname like 'user
%';
for example (obviously change the LIKE clause to something useful to
you).
then with your knowledge of how big your rows are and how many
relpages the table itself takes, you can see if your index is too
big. It helps to watch these numbers over time.
Also, running "analyze verbose" on the table gives you a hint at how
sparse the pages are, which might imply something for table bloat.
I'm not sure.
More expensive is "vacuum verbose" which gives lots of info on how
many "unused pointers" there are in your indexes. This may be of
use. If this is a high number compared to the number of row
versions, then you probably have bloat there.
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Finding bloated indexes?
2007-04-13 20:01 Finding bloated indexes? Dan Harris <fbsd@drivefaster.net>
@ 2007-04-14 08:27 ` Simon Riggs <simon@2ndquadrant.com>
1 sibling, 0 replies; 3+ messages in thread
From: Simon Riggs @ 2007-04-14 08:27 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
On Fri, 2007-04-13 at 14:01 -0600, Dan Harris wrote:
> Is there a pg_stat_* table or the like that will show how bloated an index is?
> I am trying to squeeze some disk space and want to track down where the worst
> offenders are before performing a global REINDEX on all tables, as the database
> is rougly 400GB on disk and this takes a very long time to run.
>
> I have been able to do this with tables, using a helpful view posted to this
> list a few months back, but I'm not sure if I can get the same results on indexes.
Use pgstatindex in contrib/pgstattuple
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-04-14 08:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-13 20:01 Finding bloated indexes? Dan Harris <fbsd@drivefaster.net>
2007-04-13 21:07 ` Vivek Khera <vivek@khera.org>
2007-04-14 08:27 ` Simon Riggs <simon@2ndquadrant.com>
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