As it turns out, the production database has quite a few indexes that apparently were created for testing purposes once upon a time, but never removed. This cruft causes unnecessary overhead for all updates on the edit tables and takes up HD space. It is also confusing for developers when these phantom indexes appear in execution plans. Finally, they are not consistent with what is listed in the admin/sql scripts and what slave servers have.
These indexes should be dropped:
- edit_close_time_idx (exact duplicate of existing edit_idx_close_time)
This index should be renamed:
- edit_open_time_idx, to edit_idx_open_time
For reference, the current state of the table is attached to this ticket.
(This change is not actually tied to a schema change, but will take a short downtime because DROP INDEX CONCURRENTLY is not available in Postgres 9.1. If we have a planned downtime before the next schema change, we could do it at that time.)