Poor Query Performance

[horahoradev]

Currently, several queries perform quite badly at the scale of 160k+ videos.

• basic home page searches (esp tag stuff)
• recommender query (this is a given)
• video info fetch
  We should profile the SQL here, and see if we need to e.g. add an index.

[horahoradev]

https://www.alibabacloud.com/blog/postgresql-responds-to-fuzzy-or-regular-expression-based-searches-within-seconds_595637

[horahoradev]

Made various improvements

• postgres doesn't add an index for foreign keys by default (lol), so I did that
• various other indexes, e.g. hash index on userid for videos table (makes profile video list queries fast)
• improved count/search SQL, removed a redundant join, improved recommender algorithm somewhat (still very slow)
• switched to equality for tag searches, prefix for username lookups
  Search is between 10-100x faster, profile lookups are much faster now.

There's future work to be done here, recommender is still too slow, and the fuzzy search feature was good, it'd be nice to keep it if the performance isn't prohibitive. See above article for some tips; could possibly switch to doing a prefix and suffix match instead of full fuzzy match, and add a forward and reverse b-tree on tags.