0

I am trying to put together a query to find unused indexes in a postgres database:

SELECT indexrelname, pg_relation_size(quote_ident(indexrelname)), idx_scan 
FROM pg_stat_all_indexes 
WHERE idx_scan=0 
    AND indexrelname NOT LIKE 'pg_toast_%';

The above seems to work all right, but I want to concentrate on the big indexes, so I added an extra condition:

SELECT indexrelname, pg_relation_size(quote_ident(indexrelname)), idx_scan 
FROM pg_stat_all_indexes 
WHERE idx_scan=0 
    AND indexrelname NOT LIKE 'pg_toast_%' 
    AND pg_relation_size(quote_ident(indexrelname)) > 1000000 ;

Which resulted in

ERROR:  relation "routine_privileges" does not exist

Could someone tell me why the second query is wrong and how to fix it?

  • pg_relation_size(quote_ident(indexrelname)) makes no sense at all. pg_relation_size(indexrelname) is completely enough. quote_ident is intended when you create dynamic SQL. Remove it and see if that get's rid of the error. – a_horse_with_no_name Sep 12 '18 at 8:35
  • Unrelated, but: you should also exclude unique indexes from your query. They might be "unused" but they are still important – a_horse_with_no_name Sep 12 '18 at 8:36
  • @a_horse_with_no_name If I remove quote_ident (SELECT indexrelname, pg_relation_size(indexrelname) as size FROM pg_stat_all_indexes), then the error is: ERROR: function pg_relation_size(name) does not exist. SELECT indexrelname, pg_relation_size(indexrelname::text) as size FROM pg_stat_all_indexes; works, I will look into that – P.Péter Sep 12 '18 at 8:42
  • indexrelname::text is the better choice, yes. I am not sure, but it might be that pg_stat_all_indexes returns table names where your current user has no access and that's the reason for the error. Do you still get the error if you use pg_stat_user_indexes instead of all_indexes? – a_horse_with_no_name Sep 12 '18 at 9:20
  • I am logged in as postgres user, who is the most super admin of all. Is it possible, that it does not have access to something? Or could this be a database error? How to detect the latter? – P.Péter Sep 13 '18 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.