0

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.

  • @kiamlaluno ... I am not sure why this was down-voted and tags removed. I feel it is a very valid question. Deleting indexes can have detrimental performance impacts. So, I was hoping to learn more about the role these play since I am not seeing them being used. – MKK Feb 20 '15 at 17:33
0

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.