In 14cf8eacf1, we added the
`user_search_similar_results` site setting which when enabled will use
trigram matching for similarity search in `UserSearch`. However, we
noted that adding the `index_users_on_username_lower_trgm` index is
causing the PG planner to not use the `index_users_on_username_lower`
index when the `=` operator is used against the `username_lower` column.
Based on the PG mailing list discussion where support for the `=`
operator in gist_trgm_ops was being considered, it stated that "I also have checked that btree_gist is preferred over pg_trgm gist
index for equality search." This is however quite different from reality
on our own PG clusters where the btree index is not preferred leading to
significantly slower queries when the `=` operator is used.
Since the pg_trgm gist index is only used for queries when the `user_search_similar_results` site setting
is enabled, we decided to drop the feature instead as it is hidden and
disabled by default. As such, we can consider it experiemental and drop
it without deprecation.
PG mailing list discussiong: https://www.postgresql.org/message-id/CAPpHfducQ0U8noyb2L3VChsyBMsc5V2Ej2whmEuxmAgHa2jVXg%40mail.gmail.com
Alan Guo Xiang Tan