Investigate database performance (esp. related to loading time for tutors)

[luoyanjie]

[allenwq]

@fonglh Helped to setup a testing db instance with the same configuration, and I tested a complex query in both machines:

The query time on our production db is constantly 8000 - 11000ms.
The very initial query on the new test machine is 8000ms, all subsequent queries are 1100 - 1500ms.

Testing query:

SELECT DISTINCT COUNT(DISTINCT "course_discussion_topics"."id") FROM "course_discussion_topics" INNER JOIN "course_discussion_posts" ON "course_discussion_posts"."topic_id" = "course_discussion_topics"."id" WHERE "course_discussion_topics"."course_id" = 15 AND "course_discussion_topics"."actable_type" IN ('Course::Assessment::Answer', 'Course::Assessment::Answer::ProgrammingFileAnnotation') AND "course_discussion_topics"."pending_staff_reply" = true AND (course_discussion_topics.id IN (SELECT "course_discussion_topics"."id" FROM "course_assessment_answers" INNER JOIN "course_assessment_submissions" ON "course_assessment_submissions"."id" = "course_assessment_answers"."submission_id" INNER JOIN "course_discussion_topics" ON "course_discussion_topics"."actable_id" = "course_assessment_answers"."id" AND "course_discussion_topics"."actable_type" = 'Course::Assessment::Answer' WHERE "course_assessment_submissions"."creator_id" IN (SELECT "course_users"."user_id" FROM "course_users" INNER JOIN "course_group_users" ON "course_group_users"."course_user_id" = "course_users"."id" INNER JOIN "course_groups" ON "course_groups"."id" = "course_group_users"."group_id" WHERE "course_group_users"."role" = 0 AND "course_groups"."id" IN (SELECT "course_group_users"."group_id" FROM "course_group_users" WHERE "course_group_users"."course_user_id" = 673 AND "course_group_users"."role" = 1))  ORDER BY "course_assessment_answers"."created_at" ASC) OR course_discussion_topics.id IN (SELECT "course_discussion_topics"."id" FROM "course_assessment_answer_programming_file_annotations" INNER JOIN "course_assessment_answer_programming_files" ON "course_assessment_answer_programming_files"."id" = "course_assessment_answer_programming_file_annotations"."file_id" INNER JOIN "course_assessment_answer_programming" ON "course_assessment_answer_programming"."id" = "course_assessment_answer_programming_files"."answer_id" INNER JOIN "course_assessment_answers" ON "course_assessment_answers"."actable_id" = "course_assessment_answer_programming"."id" AND "course_assessment_answers"."actable_type" = 'Course::Assessment::Answer::Programming' INNER JOIN "course_assessment_submissions" ON "course_assessment_submissions"."id" = "course_assessment_answers"."submission_id" INNER JOIN "course_discussion_topics" ON "course_discussion_topics"."actable_id" = "course_assessment_answer_programming_file_annotations"."id" AND "course_discussion_topics"."actable_type" = 'Course::Assessment::Answer::ProgrammingFileAnnotation' WHERE "course_assessment_submissions"."creator_id" IN (SELECT "course_users"."user_id" FROM "course_users" INNER JOIN "course_group_users" ON "course_group_users"."course_user_id" = "course_users"."id" INNER JOIN "course_groups" ON "course_groups"."id" = "course_group_users"."group_id" WHERE "course_group_users"."role" = 0 AND "course_groups"."id" IN (SELECT "course_group_users"."group_id" FROM "course_group_users" WHERE "course_group_users"."course_user_id" = 673 AND "course_group_users"."role" = 1))));

[fonglh]

On a machine with 2GB of RAM,

1st time: 5800 ms
subsequent: 1000 - 2000 ms