From: Michal Taborsky - Internet Mall <michal.taborsky@mall.cz>
To: pgsql-performance@postgresql.org
Subject: Identifying bloated tables
Date: Mon, 28 Aug 2006 16:39:30 +0200
Message-ID: <44F30022.1000009@mall.cz> (raw)
I just put together a view, which helps us in indentifying which
database tables are suffering from space bloat, ie. they take up much
more space than they actually should. I though this might be useful for
some folk here, because the questions about bloat-related performance
degradation are quite common.
When using this view, you are interested in tables, which have the
"bloat" column higher that say 2.0 (in freshly dump/restored/analyzed
database they should all be around 1.0).
The bloat problem can be one-time fixed either by VACUUM FULL or
CLUSTER, but if the problem is coming back after while, you should
consider doing VACUUM more often or increasing you FSM settings in
postgresql.conf.
I hope I did the view right, it is more or less accurate, for our
purposes (for tables of just few pages the numbers may be off, but then
again, you are usually not much concerned about these tiny 5-page tables
performance-wise).
Hope this helps someone.
Here comes the view.
CREATE OR REPLACE VIEW "public"."relbloat" (
nspname,
relname,
reltuples,
relpages,
avgwidth,
expectedpages,
bloat,
wastedspace)
AS
SELECT pg_namespace.nspname, pg_class.relname, pg_class.reltuples,
pg_class.relpages, rowwidths.avgwidth, ceil(((pg_class.reltuples *
(rowwidths.avgwidth)::double precision) /
(current_setting('block_size'::text))::double precision)) AS
expectedpages,
((pg_class.relpages)::double precision / ceil(((pg_class.reltuples *
(rowwidths.avgwidth)::double precision) /
(current_setting('block_size'::text))::double precision))) AS bloat,
ceil(((((pg_class.relpages)::double precision *
(current_setting('block_size'::text))::double precision) -
ceil((pg_class.reltuples * (rowwidths.avgwidth)::double precision))) /
(1024)::double precision)) AS wastedspace
FROM (((
SELECT pg_statistic.starelid, sum(pg_statistic.stawidth) AS avgwidth
FROM pg_statistic
GROUP BY pg_statistic.starelid
) rowwidths JOIN pg_class ON ((rowwidths.starelid = pg_class.oid)))
JOIN
pg_namespace ON ((pg_namespace.oid = pg_class.relnamespace)))
WHERE (pg_class.relpages > 1);
Bye.
--
Michal Táborský
IT operations chief
Internet Mall, a.s.
<http://www.MALL.cz;
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: michal.taborsky@mall.cz
Subject: Re: Identifying bloated tables
In-Reply-To: <44F30022.1000009@mall.cz>
* 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