Threads query optimisations

[masvelio]

Description

GetBulkThreads is our one of worst performing routes. Recently, we added even more complexity to the query with this PR. We need to do:

• general optimisation in get_bulk_threads.ts and get_threads_by_id.ts
• adding index to ContestsAction (more context)

[kurtisassad]

adding index to ContestsAction

@timolegros I think we should also be careful here. Right now we only need to filter on the 'upvote' contest actions. The upvote contest actions will take the vast majority of contest actions (A thread has 'added' contest action when it is upvoted once, but gets an 'upvoted' action every time a different user upvotes it).

This means in the best case scenario it is 50/50 upvotes/actions, but it could skew high in the ratio of upvotes/actions. I think that the planner will just choose to use a full table scan even if the index exists because of these statistics.

[timolegros]

adding index to ContestsAction

@timolegros I think we should also be careful here. Right now we only need to filter on the 'upvote' contest actions. The upvote contest actions will take the vast majority of contest actions (A thread has 'added' contest action when it is upvoted once, but gets an 'upvoted' action every time a different user upvotes it).

This means in the best case scenario it is 50/50 upvotes/actions, but it could skew high in the ratio of upvotes/actions. I think that the planner will just choose to use a full table scan even if the index exists because of these statistics.

Then we should narrow the query so that we don't select all upvotes and only the upvotes we need. Either way we need to avoid that table scan.