Postgresql - Finding unused indexes
Saturday, September 24, 2011 at 8:39PM Database indexes are very useful, but the down side is that updating or adding rows to tables becomes more intensive as all the indexes also need updating.
If you are using postgresql then you probably already have access to information on index usage as this information is usually collected by default - if not, you can find more information here in the postgresql docs on statistics collection.
This query (tested on PG9) gives some information on index usage and index size:
SELECT t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty ( pg_relation_size ( t.tablename::text ) ) AS table_size,
pg_size_pretty ( pg_relation_size ( indexrelname::text ) ) AS index_size,
CASE
WHEN x.is_unique = 1
THEN 'Y'
ELSE 'N'
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT
OUTER JOIN pg_class c
ON t.tablename = c.relname
LEFT
OUTER JOIN (
SELECT indrelid,
max ( CAST ( indisunique AS integer ) ) AS is_unique
FROM pg_index
GROUP BY indrelid ) x
ON c.oid = x.indrelid
LEFT
OUTER JOIN (
SELECT c.relname AS ctablename,
ipg.relname AS indexname,
x.indnatts AS number_of_columns,
idx_scan,
idx_tup_read,
idx_tup_fetch,
indexrelname
FROM pg_index x JOIN pg_class c
ON c.oid = x.indrelid JOIN pg_class ipg
ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai
ON x.indexrelid = psai.indexrelid ) AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname = 'public'
ORDER BY number_of_scans desc;
If you assume that all indexes that are needed have been used at least once, this next query tells you how big all the unused indexes are:
SELECT pg_size_pretty ( sum ( pg_relation_size ( indexrelname::text ) ) ::bigint ) AS index_size
FROM pg_tables t
LEFT
OUTER JOIN pg_class c
ON t.tablename = c.relname
LEFT
OUTER JOIN (
SELECT indrelid,
max ( CAST ( indisunique AS integer ) ) AS is_unique
FROM pg_index
GROUP BY indrelid ) x
ON c.oid = x.indrelid
LEFT
OUTER JOIN (
SELECT c.relname AS ctablename,
ipg.relname AS indexname,
x.indnatts AS number_of_columns,
idx_scan,
idx_tup_read,
idx_tup_fetch,
indexrelname
FROM pg_index x JOIN pg_class c
ON c.oid = x.indrelid JOIN pg_class ipg
ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai
ON x.indexrelid = psai.indexrelid ) AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname = 'public'
AND idx_scan =0;
You can inverse that last query to get the total size of all the in-use indexes by changing the idx_scan=0 to idx_scan>0. If you are heavily reliant on the database fitting into physical RAM (as I usually am) then you may want to keep an eye on the index size, along with the size of your tables.
If you are looking to reduce the size of the indexes you could look at some of your biggest indexes and think about the queries that actually run against the table - do you really need to index the *whole* table for your queries? If not, you could look at changing the index to a partial index which is basically an index which only covers part of a table based on a where clause.
There are a few other things that can help get down index size , ensure the auto vacuum setting are aggressive enough, dump-restore your database periodically, delete rows in the underlying table, etc, :)
Reader Comments (11)
Welcome to Moncler Jackets Shop! The biggest catch with winter wear is they turn out making you search like a teddy bear at the value of remaining warm. moncler outlet The Moncler jacket is a flattering jacket to possess inside your closet whether or not it really
is a thing you may not have the ability to put on for all time. moncler online shop The styles and colours that Moncler Jackets have are assorted but there need to be just one for almost any person. moncler outlet Moncler jackets will not be just warm but are effectively in form and trendy to boot. moncler outlet Moncler Outlet are here to offer ideal winter protection and present you with a debonair search. No need to compromise on how you look so that you can remain warm.
moncler jackets what are you waiting for, choose the Moncler Online from Here! Get the warm feeling with them now! If you want to add uggs clearance some celebrity ugg coupon style to your wardrobe ugg australia uk add a pair of Cheap Ugg UK boots to your footwear ugg boots clearance collection. Cheap uggs clearance uggs sale at UGG Australia Official Store to find out ugg boots outlet more information on the latest uggs for men and women.It is time for you to prepare a UGG Cardy Boots for fall or winter. UGG Outlet Store When somebody moncler outlet sale speaks about winter, moncler outlet online what comes first in your mind? You need shoes, moncler outlet clothing ,so uggs clearance that you can keep yourself warm. uggs on clearance UGG Cardy Boots is the best for you.You should keep your self moncler online shop warm and for this you need a comfortable and warm outfit, uggs clearance further more do not neglect the care and comfort of your moncler on sale feet and you will surely get all the qualities of good shoe in UGG Cardy Boots.
HYCJL18565V3 I like your article very much, thank you for sharing! Canada Goose Jacket of Canada Goose Parka not only has this function, but also a Coyote fur ruff surrounding a removable 2-way adjustable hood for great protection from the elements. And this is also the reason why so many women like it. You know, for women, short but fit parka can help show their good figure. And While Snow Jacket Sale:Yorkville Parka MidGrey of Canada Goose Parka is the one can satisfy your need. Everyone want to have it. http://www.2011canadagooseoutlet.com/
It is more useful and also may be all of them because in the post has discussed about the postgresqul coding so that can be used it.
Samsung Galaxy
Nice and more informative matter has been discussed in the post how to used it index so that can be know that and able to know that regarding post. Mostly are very like it.
Søgemaskineoptimering
Those numbers can be used reliably to identify unused indexes.
link building
we are using postgresql then we probably already have access to information share it and updating data .
porno
The grateful technology and more useful shared on the site so that can be know that regarding post. In the post define all the information how to used it in this technology.
Lån Penger
Most of the people are very like it in this technology and enjoy it. How to work and maintain index so that can be know that and able to understand regarding post. Mostly are used in this technology.
Hulmursisolering
Nice technology discussed on the site in which used to developed the software and used in this technology. Often are used and know that in this technology. It is very fantastic and grateful technology shared on the site.
it outsourcing
In the post has been discussed about the index coding and more informative we can be know that and used in this technology and creative also. The majority of the community is used and collects the information.
Energy saving windows
Coding through the database system is really an tough subject. This is the most useful subject for everyone in terms of coding. Indexing through database and making it through in a format of table is very new for everyone. The figures mentioned in your blog is very useful for everyone.