> > > Generically speaking, if the total of dx_scan + idx_tup_read + > idx_tup_fetch are 0, then it is an _indication_ that those indexes should > be dropped. > You should also consider how long those indexes have existed and how often > queries are executed. > > A good practice would be to save the SQL to recreate the indexes before > you drop any. In that way, if you notice a degradation in performance, you > can just rebuild > You can use the following query to do that, but you might want to edit and > add the CONCURRENT option. > > SELECT pg_get_indexdef(idx.indexrelid) || ';' > FROM pg_stat_all_indexes i > JOIN pg_class c ON (c.oid = i.relid) > JOIN pg_namespace n ON (n.oid = c.relnamespace) > JOIN pg_index idx ON (idx.indexrelid = i.indexrelid ) > WHERE NOT idx.indisprimary > AND NOT idx.indisunique > AND i.relname NOT LIKE 'pg_%' > AND i.idx_scan + idx_tup_read + idx_tup_fetch = 0 > ORDER BY n.nspname, > i.relname; > > The following query generates the drop statements. > > SELECT 'DROP INDEX CONCURRENTLY IF EXISTS "' || quote_ident(n.nspname) || > '"' || '.' || '"' || quote_ident(i.indexrelname) || '"' ||';' > FROM pg_stat_all_indexes i > JOIN pg_class c ON (c.oid = i.relid) > JOIN pg_namespace n ON (n.oid = c.relnamespace) > JOIN pg_index idx ON (idx.indexrelid = i.indexrelid ) > WHERE NOT idx.indisprimary > AND i.relname NOT LIKE 'pg_%' > AND i.idx_scan + idx_tup_read + idx_tup_fetch = 0 > ORDER BY i.indexrelname; > > > I would not place any concern on the size of the index. That is just what > is needed to keep track of all associated rows. > Once you drop the indexes you determine are not needed, you will gain back > the space that they use up. > > Please stay in touch and let me know how it goes. >
I will. Thanks for the help/tips! Cheers Lucas
