If you have set up the postgres instance to write stats, the tables 
pg_stat_user_indexes, pg_statio_all_indexes and so (use the \dS option at the psql 
prompt to see these system tables); also check the pg_stat_user_tables table and 
similar beasts for information on total access, etc. Between these you can get a good 
idea of what indexes are not being used, and from the sequentail scan info on tables 
perhaps some idea of what may need some indexes.


Greg Williamson
GlobeXplorer LLC 

-----Original Message-----
From:   Martin Foster [mailto:[EMAIL PROTECTED]
Sent:   Thu 9/23/2004 3:16 PM
Subject:        [PERFORM] Cleaning up indexes
My database was converted from MySQL a while back and has maintained all 
of the indexes which were previously used.   Tt the time however, there 
were limitations on the way PostgreSQL handled the indexes compared to 

Meaning that under MySQL, it would make use of a multi-column index even 
if the rows within did not match.    When the conversion was made more 
indexes were created overall to correct this and proceed with the 

Now the time has come to clean up the used indexes.   Essentially, I 
want to know if there is a way in which to determine which indexes are 
being used and which are not.   This will allow me to drop off the 
unneeded ones and reduce database load as a result.

And have things changed as to allow for mismatched multi-column indexes 
in version 7.4.x or even the upcoming 8.0.x?

        Martin Foster

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to