765705 - [performance][research] Optimize or replace or ? the questions list query in questions.views.questions()

Status: RESOLVED INCOMPLETE

(support.mozilla.org :: Questions, task)

Description

[Ricky Rosario [:rrosario, :r1cky]]

After adding some new timers [1] in bug 674304, it is clear that what makes the /questions view slow is the query to pull the list of questions. It looks like when filtering for unsolved it is at it's worst, but it is also pretty bad for no filter at all.

We need to figure out what options we have along with the pros and cons of each.

BTW, the SQL is like:

```
SELECT (SELECT COUNT(*) FROM questions_questionvote WHERE questions_questionvote.question_id = questions_question.id) AS `_num_votes`, `questions_question`.`id`, `questions_question`.`title`, `questions_question`.`creator_id`, `questions_question`.`content`, `questions_question`.`created`, `questions_question`.`updated`, `questions_question`.`updated_by_id`, `questions_question`.`last_answer_id`, `questions_question`.`num_answers`, `questions_question`.`solution_id`, `questions_question`.`is_locked`, `questions_question`.`num_votes_past_week`, `auth_user`.`id`, `auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`, `auth_user`.`is_active`, `auth_user`.`is_superuser`, `auth_user`.`last_login`, `auth_user`.`date_joined`, `questions_answer`.`id`, `questions_answer`.`question_id`, `questions_answer`.`creator_id`, `questions_answer`.`created`, `questions_answer`.`content`, `questions_answer`.`updated`, `questions_answer`.`updated_by_id`, `questions_answer`.`upvotes`, `questions_answer`.`page`, T4.`id`, T4.`username`, T4.`first_name`, T4.`last_name`, T4.`email`, T4.`password`, T4.`is_staff`, T4.`is_active`, T4.`is_superuser`, T4.`last_login`, T4.`date_joined` FROM `questions_question` INNER JOIN `auth_user` ON (`questions_question`.`creator_id` = `auth_user`.`id`) LEFT OUTER JOIN `questions_answer` ON (`questions_question`.`last_answer_id` = `questions_answer`.`id`) LEFT OUTER JOIN `auth_user` T4 ON (`questions_answer`.`creator_id` = T4.`id`) WHERE (`auth_user`.`is_active` = True AND NOT (`questions_question`.`created` < DATE(2012-03-16) AND `questions_question`.`num_answers` = 0 )) ORDER BY `questions_question`.`updated` DESC LIMIT 21 OFFSET 0;
```


[1] https://graphite-phx.mozilla.org/render/?target=stats.timers.sumo.questions.view.paginate.*.upper_90&target=stats.timers.sumo.view.questions.views.questions.GET.upper_90&title=questions+view+performance+stuff&id=0283f5f3-aa41-a749-27f4-754acea134a6&width=960&height=600&t=0.9334423278678167&hideLegend=false&from=-1hour

Comment 1

[Ricky Rosario [:rrosario, :r1cky]]

One of the options here is using ES but our ES cluster has issues so pushing this off to Q3 for now.

Comment 2

[Ricky Rosario [:rrosario, :r1cky]]

I landed https://github.com/mozilla/kitsune/commit/95f9c444f461c4b7be29621fe946e7dbde8ea0c6 which puts the `(SELECT COUNT(*) FROM questions_questionvote WHERE ...` part of the query behind a waffle switch. Now I can collect some timing data without that and see how much it helps if anything.

Comment 3

[Ricky Rosario [:rrosario, :r1cky]]

Wow, this was at least 3 LDAP password resets ago.

Comment 4

[Ricky Rosario [:rrosario, :r1cky]]

This got a whole lot better when we changed the question view recently (removed ability for changing the sort, etc).