Remove unneeded hivemind indexes

Savings would be:

| hive_posts_api_helper                              | 10.2 GB |
|----------------------------------------------------|---------|
| hive_posts_api_helper_author_s_permlink_idx UNUSED | 9.42 GB |

Polling apps devs, seems like no one uses this API call. We also don't see any traffic for it on api.hive.blog. Given the table for it consumes 22GB of space, I think we should drop this API call and the associated table.

Related to this issue: #189 (closed)

A more comple list of large "unused" hivemind indexes as reported by api.hive.blog (associated tables should also be examined, as we know that hive_posts_api_helper at 12.1GB now can be dropped, for instance).

hive_votes_voter_id_post_id_idx hivemind_app UNUSED	20.1 GB
hive_posts_api_helper_author_s_permlink_idx hivemind_app UNUSED	13.3 GB
-----------------------------------------------------------------	---------
hive_posts_cashout_time_id_idx hivemind_app UNUSED	6.93 GB
----------------------------------------------------	---------
hive_posts_payout_idx hivemind_app UNUSED	5.45 GB
-------------------------------------------	---------
idx_hivemind_app_hive_posts_row_id hivemind_app UNUSED	5 GB
--------------------------------------------------------	------
hive_posts_updated_at_idx hivemind_app UNUSED	3.42 GB
-----------------------------------------------	---------
idx_hivemind_app_hive_follows_row_id hivemind_app UNUSED	2.86 GB
----------------------------------------------------------	---------
dx_hivemind_app_hive_post_data_row_id hivemind_app UNUSED	2.47 GB
-----------------------------------------------------------	---------
idx_hivemind_app_hive_posts_api_helper_row_id hivemind_app UNUSED	2.46 GB
-------------------------------------------------------------------	---------
idx_hivemind_app_hive_permlink_data_row_id hivemind_app UNUSED	2.41 GB
----------------------------------------------------------------	---------
hive_follows_created_at_idx hivemind_app UNUSED	1.18 GB
-------------------------------------------------	---------
idx_hivemind_app_hive_feed_cache_row_id hivemind_app UNUSED	772 MB
-------------------------------------------------------------	--------
idx_hivemind_app_hive_reblogs_row_id hivemind_app UNUSED	301 MB
----------------------------------------------------------	--------
hive_reblogs_created_at_idx hivemind_app UNUSED	286 MB
-------------------------------------------------	--------
idx_hivemind_app_hive_reputation_data_row_id hivemind_app UNUSED	219 MB
------------------------------------------------------------------	--------
idx_hivemind_app_hive_notification_cache_row_id hivemind_app UNUSED	165 MB
---------------------------------------------------------------------	--------
idx_hivemind_app_hive_accounts_row_id hivemind_app UNUSED	93 MB
-----------------------------------------------------------	-------
idx_hivemind_app_hive_tag_data_row_id hivemind_app UNUSED	44.5 MB
-----------------------------------------------------------	---------
idx_hivemind_app_hive_mentions_row_id hivemind_app UNUSED	35.3 MB
-----------------------------------------------------------	---------
idx_hivemind_app_hive_subscriptions_row_id hivemind_app UNUSED	18.1 MB
----------------------------------------------------------------	---------
hive_mentions_account_id_idx hivemind_app UNUSED	15.4 MB
--------------------------------------------------	---------
idx_hivemind_app_hive_category_data_row_id hivemind_app UNUSED	9.93 MB
----------------------------------------------------------------	---------
hive_notifs_ix6 hivemind_app UNUSED	7.68 MB
-------------------------------------	---------
hive_notifs_ix3 hivemind_app UNUSED	6.1 MB
-------------------------------------	--------
idx_hivemind_app_hive_notifs_row_id hivemind_app UNUSED	5.16 MB
---------------------------------------------------------	---------
hive_notifs_ix1 hivemind_app UNUSED	4.58 MB
-------------------------------------	---------
hive_payments_post_id hivemind_app UNUSED	4.19 MB
-------------------------------------------	---------
idx_hivemind_app_hive_payments_row_id hivemind_app UNUSED	3.83 MB
-----------------------------------------------------------	---------
payout_stats_view_ix2 hivemind_app UNUSED	2.25 MB
-------------------------------------------	---------
hive_payments_to hivemind_app UNUSED	1.11 MB
--------------------------------------------------------	---------
idx_hivemind_app_hive_roles_row_id hivemind_app UNUSED	1000 KB
idx_hivemind_app_hive_communities_row_id hivemind_app UNUSED	208 KB
--------------------------------------------------------------	--------
idx_hivemind_app_hive_state_row_id hivemind_app UNUSED	16 KB
--------------------------------------------------------	-------
hive_notifs_ix5 hivemind_app UNUSED	8 KB
-------------------------------------	------

Like the original issue, there may be some associated unused query/api call, so we should also remove any associated queries.

I don't see any reason to keep created_at field and "hive_follows_created_at_idx" in hive_follows table. Even if there is some API that reports the data, I think we should just junk it.

The whole design of hive_follows table and functionality probably needs to be reviewed for a refactor.

changed milestone to %JulyHivemind

changed title from Investigate if anyone uses list_comments: if not we can remove api_helper table/index to Remove unneeded hivemind indexes

mentioned in merge request !830 (merged)

Another related issue: haf#272

The HAF-created indexes were removed in haf!487 (merged) (done a while back apparently), so between the 3 mentioned MRs, this issue has been completed.

closed