...
When running the query against the read replica, the expected index is being used. Removing `author_id_1` should not have affected this because the compound index `author_id_1_course_id_1` would be used instead. The median remains the same and the 95th percentile did not spike like the 99th. It seems like the proper indexes are in place, otherwise, there would be a regression for the median as well. One possible explanation could be the increase of index size explained in this wiki below.
UPDATE:
TLDR: Regression has been addressed. After The index "author_id_1_course_id_1" may have been corrupted. It was removed and then re-added. This endpoint now looks the same as it way before the index update.
Compose reported that there was a slow query where the suggested index was author_id_1, an index that had been removed. When running the example slow query with an explain plan, it was shown that the correct index "author_id_1_course_id_1" was being used as expected. After some back and forth with Compose, it was found in the logs that sometimes the username endpoint will have a long read lock time (in the order of 10s). One suggestion was to reapply "author_id_1_course_id_1" in the case it may have been a corrupted index. After reapplying this index, compose no longer reported this as a slow query and new relic seems to be reporting normal numbers.
Index size:
The indexes that we added takes up more RAM than the ones removed. To add to that, the `delete_spam` index that was removed had a size of 0 which does not really improve performance at all. Overall it seems like another 1GB of indexes was added.
...