Can I safely delete auto-created Postgres Indexes?

Question

Running Drupal 7.X with a Postgres 9.3 database.

When adding new fields, 8 database indexes appear to be created for each new field. Running the query "select * from pg_stat_all_indexes;" shows that only 2-3 out of the 8 indexes are actually getting used ... primarily field_data_field_XXXXX_entity_id_idx and field_data_field_XXXXX_pkey. All the other indexes (e.g., ...bundle_idx, ... deleted_idx, ... language_idx, etc.) show 0 for idx_scan, idx_tup_read, and idx_tup_fetch.

Is it safe to delete those indexes that show 0? Once deleted, is there an easy way to recreate the indexes if needed?

The reason for wanting to delete those indexes is that hard drive space is limited. Deleting those indexes would recover significant amounts of hard drive space.

Answer 1

I used the following PSQL query to identify all the indexes not being used and to sort by biggest first:

SELECT nspname || '.' || C.relname AS "relation",
  pg_relation_size(C.oid) AS "size",
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
LEFT JOIN pg_stat_all_indexes stat ON C.relname = stat.indexrelname
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC

I deleted several dozen indexes. So far, no issues. It seems performance may be improved as I am noticing less CPU resources needed. Likely because there are fewer indexes to be updated.