Postgres Pro
Downloads
Home   >   mailing lists

Thread: Unused indices

Unused indices

From
"Benjamin Krajmalnik"
Date:

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?

 

Re: Unused indices

From
Greg Smith
Date:
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

Re: Unused indices

From
Shaun Thomas
Date:
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

Re: Unused indices

From
Greg Smith
Date:
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

Re: Unused indices

From
Shaun Thomas
Date:
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