pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUnused indices
5+ messages / 3 participants
[nested] [flat]
* Unused indices
@ 2011-02-23 19:12 Benjamin Krajmalnik <kraj@servoyant.com>
2011-02-23 21:17 ` Re: Unused indices Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Benjamin Krajmalnik @ 2011-02-23 19:12 UTC (permalink / raw)
To: pgsql-performance
I am trying to clean up our schema by removing any indices which are not
being used frequently or at all.
Using pgadmin, looking at the statistics for an index, I see various
pieces of information:
Index Scans, Index Tuples Read, Index Tuples Fetched, Index Blocks Read,
and Index Blocks Hit.
I have on index with the following statistics:
Index Scans 0
Index Tuples Read 0
Index Tuples Fetched 0
Index Blocks Read 834389
Index Blocks Hit 247283300
Index Size 1752 kB
Since there are no index scans, would it be safe to remove this one?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unused indices
2011-02-23 19:12 Unused indices Benjamin Krajmalnik <kraj@servoyant.com>
@ 2011-02-23 21:17 ` Greg Smith <greg@2ndquadrant.com>
2011-02-24 14:25 ` Re: Unused indices Shaun Thomas <sthomas@peak6.com>
0 siblings, 1 reply; 5+ messages in thread
From: Greg Smith @ 2011-02-23 21:17 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; +Cc: pgsql-performance
Benjamin Krajmalnik wrote:
>
>
> Index Scans 0
>
> Index Tuples Read 0
>
> Index Tuples Fetched 0
>
> Index Blocks Read 834389
>
> Index Blocks Hit 247283300
>
> Index Size 1752 kB
>
>
>
>
>
> Since there are no index scans, would it be safe to remove this one?
>
Yes. The block usage you're seeing there reflects the activity from
maintaining the index. But since it isn't ever being used for queries,
with zero scans and zero rows it's delivered to clients, it's not doing
you any good. Might as well reclaim your 1.7MB of disk space and reduce
overhead by removing it.
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unused indices
2011-02-23 19:12 Unused indices Benjamin Krajmalnik <kraj@servoyant.com>
2011-02-23 21:17 ` Re: Unused indices Greg Smith <greg@2ndquadrant.com>
@ 2011-02-24 14:25 ` Shaun Thomas <sthomas@peak6.com>
2011-02-24 18:13 ` Re: Unused indices Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Shaun Thomas @ 2011-02-24 14:25 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Benjamin Krajmalnik <kraj@servoyant.com>; pgsql-performance
On 02/23/2011 03:17 PM, Greg Smith wrote:
> Yes. The block usage you're seeing there reflects the activity from
> maintaining the index. But since it isn't ever being used for
> queries, with zero scans and zero rows it's delivered to clients,
Nice to know. To that end, here's a query that will find every unused
index in your database:
SELECT i.schemaname, i.relname, i.indexrelname, c.relpages*8 indsize
FROM pg_stat_user_indexes i
JOIN pg_class c on (i.indexrelid=c.oid)
JOIN pg_index ix ON (i.indexrelid=ix.indexrelid)
WHERE i.idx_scan = 0
AND i.idx_tup_read = 0
AND i.schemaname NOT IN ('zzz', 'archive')
AND NOT ix.indisprimary
AND c.relpages > 0
ORDER BY indsize DESC;
I noticed with our database that without the indisprimary clause, we had
another 4GB of unused indexes. Clearly we need to look at those tables
in general, but this will find all the "safe" indexes for removal.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unused indices
2011-02-23 19:12 Unused indices Benjamin Krajmalnik <kraj@servoyant.com>
2011-02-23 21:17 ` Re: Unused indices Greg Smith <greg@2ndquadrant.com>
2011-02-24 14:25 ` Re: Unused indices Shaun Thomas <sthomas@peak6.com>
@ 2011-02-24 18:13 ` Greg Smith <greg@2ndquadrant.com>
2011-02-24 19:36 ` Re: Unused indices Shaun Thomas <sthomas@peak6.com>
0 siblings, 1 reply; 5+ messages in thread
From: Greg Smith @ 2011-02-24 18:13 UTC (permalink / raw)
To: sthomas@peak6.com; +Cc: Benjamin Krajmalnik <kraj@servoyant.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unused indices
2011-02-23 19:12 Unused indices Benjamin Krajmalnik <kraj@servoyant.com>
2011-02-23 21:17 ` Re: Unused indices Greg Smith <greg@2ndquadrant.com>
2011-02-24 14:25 ` Re: Unused indices Shaun Thomas <sthomas@peak6.com>
2011-02-24 18:13 ` Re: Unused indices Greg Smith <greg@2ndquadrant.com>
@ 2011-02-24 19:36 ` Shaun Thomas <sthomas@peak6.com>
0 siblings, 0 replies; 5+ messages in thread
From: Shaun Thomas @ 2011-02-24 19:36 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Benjamin Krajmalnik <kraj@servoyant.com>; pgsql-performance
On 02/24/2011 12:13 PM, Greg Smith wrote:
> 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.
Ah yes. I was considering adding the clause for unique indexes. Filthy
constraint violations.
> Also, as a picky point, you really should use functions like
> pg_relation_size instead of doing math on relpages.
You know, I always think about that, but I'm essentially lazy. :) I
personally haven't ever had the old *8 trick fail, but from your
perspective of working with so many variations, I could see how you'd
want to avoid it.
I'll be good from now on. ;)
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-02-24 19:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-23 19:12 Unused indices Benjamin Krajmalnik <kraj@servoyant.com>
2011-02-23 21:17 ` Greg Smith <greg@2ndquadrant.com>
2011-02-24 14:25 ` Shaun Thomas <sthomas@peak6.com>
2011-02-24 18:13 ` Greg Smith <greg@2ndquadrant.com>
2011-02-24 19:36 ` Shaun Thomas <sthomas@peak6.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