From: Greg Smith <greg@2ndquadrant.com>
To: sthomas@peak6.com
Cc: Benjamin Krajmalnik <kraj@servoyant.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Unused indices
Date: Thu, 24 Feb 2011 13:13:12 -0500
Message-ID: <4D669FB8.8010001@2ndquadrant.com> (raw)
In-Reply-To: <4D666A4F.1060205@peak6.com>
References: <F4E6A2751A2823418A21D4A160B689887B0F8E@fletch.stackdump.local>
<4D65795C.20404@2ndquadrant.com>
<4D666A4F.1060205@peak6.com>
Shaun Thomas wrote:
> I noticed with our database that without the indisprimary clause, we
> had another 4GB of unused indexes.
That's not quite the right filter. You want to screen out everything
that isn't a unique index, not just the primary key ones. You probably
can't drop any of those without impacting database integrity.
Also, as a picky point, you really should use functions like
pg_relation_size instead of doing math on relpages. Your example breaks
on PostgreSQL builds that change the page size, and if you try to
compute bytes that way it will overflow on large tables unless you start
casting things to int8.
Here's the simplest thing that does something useful here, showing all
of the indexes on the system starting with the ones that are unused:
SELECT
schemaname,
relname,
indexrelname,
idx_scan,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE
indisunique IS false
ORDER BY idx_scan,relname;
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
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: greg@2ndquadrant.com, sthomas@peak6.com, kraj@servoyant.com
Subject: Re: Unused indices
In-Reply-To: <4D669FB8.8010001@2ndquadrant.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